Pull Data from Text File Before Appending it to Table in SQL-Server 2005 DTS

Question

I have to work in a DTS environment in 2005 (too complicated to explain) and I have a comma delimited text file that is to be appended to the main table. I'd like to pull the last column in the text file out for the first record and use it as select criteria for a delete command. But how can I do this in the older DTS environment?

Here's the line of foobar data

9,36,7890432174,2007-12-17 00:00:00.000,21,15.22,99,11,49,28,2009-07-12 00:00:00

what I want to do is create a sql statement that will delete all the records where a certain column is equal to "2009-07-12 00:00:00"

Thanks.

Answer 1

There are at least two ways to implement this in DTS.

The first is to

1. load the text file into a staging table
2. select the date value from the temporary table and assign it to a package variable
3. carry out the delete using the package variable as an input parameter
4. insert from the staging table into the main table
5. clear down the staging table

This assumes that there is some way to identify the order of the rows in the text file from the data. If not, you could add an identity column to the staging table definition.

The second is to

1. extract the value from the input file using a script task and assign it to a package variable
2. carry out the delete using the package variable as an input parameter
3. insert from the text file into the main table

EDIT

I believe it's also possible to use the generic text file ODBC driver to access the text file like a database table.