Can DTS update columns in a table when importing a text file

[nakkii]

I have a DTS package that updates the rows in a table every hour, the import is coming from a comma delimited text file.

What I need to do is if there is a new field added to the text file, I want DTS to automatically add the column to the SQL (2000) table and then update all the rows accordingly.

I think I have the update process figured out, but don't see how to get DTS to add new columns.

Any help or direction would be greatly appreciated.

 

[malexanian]

Short answer: DTS can't. Long answer: You can write a procedure, either in SQL Server or outside it, that examines the file and determines if a column has been added. If it has, trigger a stored procedure that ALTERs your TABLE to add the column.

 

[nakkii]

Great! Thanks, saves me some time trying to do something DTS can't.

Any pointers on how to get SQL to check the text file in which the first row contains the fields names and compare it to a table that exists?

-Rob