How to create dynamic number of output files with SSIS?

Question

I will be creating flatfiles and based on the data in the batch, it might be necessary to split the data into an undetermined number of files.

I can make the connection string dynamic with an expression, but that is only evaluated when the package starts. I'd like to change that expression to include a '-a' or '-b' in the filename.

Alternately, if I have to create new connection manager objects at run time on demand, how do I go about that?

Answer 1

1) create grobal variable(a variable is created within the scope of a package) and assign it to the file name property.

2) change the variable during the looping.

EDITED see for more details...

Answer 2

You can access the data set in a script (in the script component) and write out to a set of files based on your criteria.

Answer 3

First determine your naming scheme for the output files and come up with an expression formula in your head

Put the Data Flow Task in a loop.

Within this Data Flow Task, define the source and destination. Destination being the Flat File Destination. Read the source and add some derived column that sets a value to another variable that you'll later use in the Filename expression.

Connect the Flat File Destination to a Connection Manager. First define some path but then add an Expression to define a Connection String based on your File Name scheme (Path + Filename + extension). Now this Filename is tricky. You'll have to put IIF statements based on the values you've got from Source