Thursday, May 26, 2011

Column Name into variable to rename file

Today I came across a question in EE:

The person gets a unique file everyday, file name in the format 1234_Test.txt and the first four digits changes everyday.  Also need to place the output file and rename it based on a column value.
For ex: If a column named code is 2, then my output file should be 2_1234_TEST.txt

So it could be done by using a "File Watcher Task" to check for the file when it's available and put the file name into a variable.

Or using SSIS native task, using a "Foreah Loop" task to get the file name into variable, use a "script task" into the data flow to get the column value into another variable.

The a 3rd variable to join both of them and the destination folder.

The package could be downloaded from
steps are:

3 variables -  type (STRING)

 Foreach Loop config

 File system task config
 Expression on the variable

 Clearer View

Data Flow Task
Script component config

VB script