SQL Server 2005 SSIS - How to get special information from the first line of a file

Question

Say I have a text file that looks like this:

date 1/1/2010
a,b,c
a,b,d
...

I want to import it into a table so it looks like this:

1/1/2010,a,b,c
1/1/2010,a,b,d
...

What is an elegant way to do that?

My best idea so far is to use a data flow package, and use a flat file source to read in the file (ignoring the first line) and load it into the table. When that is complete, have a script task open the file again, read out the date, then pass that date to a sql task to update the table with the date.

But surely there is a less convoluted way?

Answer 1

I would extract the date into a package datetime variable. Then, use the data flow to extract your data as you mentioned. After that within the same dataflow, I would then use a derived column tranformation to add the date variable to your buffer which would then be loaded into your table. It's similar to what you had in mind but, requires one less open and close db connection which would be created and disposed using your sql task.

Answer 2

I've used this component --> http://www.sqlis.com/post/Row-Number-Transformation.aspx

The component creates a new variable with a row number. You read the file like you describe in the question then apply the component. After that you can use a conditional split to take all rows with the id > 1.

This avoids going to the database twice but involves installing a third party component.

Pick you poison.