sqlsql-serverssissql-server-2008-r2dts

Combining CSV import with User Variable in SSIS package

I am importing a number of CSV files into a database. The aim is to take the data from the files but also write the filename of the associated CSV file to each of the database rows.

I am using a Script Component inside the Data Flow to capture the 'Current File' variable that is written to by the ForEach loop, but I am struggling on how to integrate this into the Data Flow Destination (OLE DB).

Data Flow

If I use a UNION ALL, the Script Component row has its own row with NULL values.

Data Reader

Both Merge Transformation behave largely the same.

How should I be combining the two data sources in order to get the filename repeated alongside all of the records from each file in the destination table?

Solution

  • Remove your Script Component and the UNION ALL as they are not needed.

    Instead, add a Derived Column component called something like DER Add file name Within the Derived Column, use @[User::filename] and give it a good column name, like FileNameColumnName

    How you use it in an destination, is up to you. If you don't already have a column, add one ALTER TABLE dbo.MyTable ADD FileNameColumnNamevarchar(250) NULL;

    Finally, map that column to the destination.

    As an alternative, you can go into the Advanced properties of the Flat File Source and add the FileNameColumnName into the data flow and skip having a derived column to inject the file name.

    Using the Derived Column pattern works for any file source (Excel, etc) but the FileNameColumnName is built into the Flat File Source so pro/con either way.

    I wrote about it under "What is the name of the current file"?