Loading flat files /adding filename to table column

Question

I am trying to load a ton of flat files into a sql table and adding a filename column to the table.

in control flow I have 2 foreech loops grabbing the filenames and passing them to 2 variables.

this is where im having trouble in my data flow, I first have my flat file source pointing to derived column then pointing to OLE DB Destination.. in the OLE DB destination it is recognizing my filename derived column, but when I run the package it executes fine it just creates the table, it doesn't insert the flat file data or the filename from the 2 variables

Answer 1

The flat file source has a special property for the purpose of giving you the file name:

1. Right click on the flat file connection manager and go to advanced properties.
2. In the component properties tab, go to Custom Properties> FileNameColumnName and enter a name, i.e. FileName
3. Once you hit ok to save the changes, the FileName column will be added to the output buffer and this can be mapped in the data flow.

Answer 2

Variable value changes cannot be read within the same DataFlow Task. You have to write an expression that get the Destination Table name in a Script Task or Expression Task inside the ForEach Loop container, then connect this task to the DataFlow Task. Note that you have to set the Delay Validation of the DataFlow Taskto True.