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 http://www.box.net/shared/lx4j79a39z
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