How to create separate transformed file instead of overwrite ssis output flat file?

Question

I have a dataflow that is used to do transformation of multiple flat files from given folder using for each loop container. I have a flat file again as output file. The problem is that every time I execute the the job only the last file that got transformed will be stored in destination file. Is there a way in SSIS I can create individual transformed output file instead on overwriting on same one over and over again?

For. eg. I have 5 flat files ,test_1.txt,test_2.txt,test_3.txt ,test4_.txt and test_5.txt in a folder. After the job ran I can only see the data from last file test_5.txt being transformed in my destination file.

Answer 1

There are ways to do what you are asking in SSIS with variables and expressions but there is an easier way to accomplish it using command line.

Since you are just consolidating a text files into 1 you can use a command prompt to better handle your issue:

copy *.txt output.txt

Answer 2

Here's steps on a working example I tested.

Variables

I have 3 variables defined:

• FileName - To be used in the foreach loop
• DestinationDir - where are the files going
• SourceDir - where are the files I want to process

Foreach Loop Setup

I have a foreach loop configured as:

• Expression for "Directory" set to @[User::SourceDir]
• Retrieve file name set to "Name and extension"

Then under the "Variable Mappings":

That means as the foreach loop is iterating over the files in the directory it will be setting the "Name and extension" of the file its on to the variable @[User:FileName]

Data Flow Task

The I add a Data Flow Task inside the foreach loop:

Then inside the DFT I have a simple Flat File Source to Flat File Destination. We'll just pass the contents of each file to new files:

During initial development I'll manually pick one file to walk through setting each of the source and destinations. Then come back and change the connection managers and set an expression on the ConnectionString.

Connection Manager Expressions

SourceFile Connection Manager:

• ConnectionString gets an expression as: @[User::SourceDir] + @[User::FileName]

DestinationFile Connection Manager:

• ConnectionString gets an expression as: @[User::DestinationDir] + @[User::FileName]

Testing

I have 2 test files in my source directory and no files in my destination:

After I execute my package I get success and also get new files in my destination: