SSIS package - How to produce multiple files with a foreach loop container?

Question

How can several files be produced in a single SSIS package? I have created one that produces a single file, but have no idea how to produce several ones.

The package I produced uses variables to know which data to retrieve, and an expression in the flat file connection manager to assign the correct name to the file (which is based on variables).

The single package I created retrieves the city for which I want the sales data (New York) and the month (September 2020) as variables/parameters, and uses them to extract the appropriate data. Example of SQL statement executed:

select * from table1 where City = ? and Period = ??

It then uses those to build the name for the file to be exported and sends it to a folder. But how do you do that to produce several files within the same package? How can I make the same SSIS package produce another file for Chicago - July 2020, another for Denver - June 2020, and another for San Diego - March 2020?

I plan to have a table that indicates what needs to be produced.

• ExampleRow1: Chicago, Sep 2020, Produce=Yes.
• ExampleRow2: Miami, Jan 2020, Produce=Yes.

So the SSIS package would need to use that info to produce a file, and then do it again, and again, until there is nothing more to produce. Is this even possible? I know a foreach loop container can help, but not sure if it can handle so many variables changing. This is pretty much the first package I create, that's why I am this ignorant. Thanks in advance!

Answer 1

Right now, you have it working correctly for the value of your two SSIS variables (City and Period) and you have it parameterized so I wouldn't discount that as your first SSIS package. People struggle with far easier tasks

What you need to do is connect the orchestrator/driver table into your package. Here's how we're going to do that.

1. Create an SSIS variable called rsObject of type Object. This is going to hold a recordset object aka the results of our query.

Execute SQL Task

Add an Execute SQL Task to the Control Flow. Call it "SQL Get Driver Data" You'd use a query like

SELECT T.City, T.Period FROM dbo.ExtractSetup AS T WHERE T.Produce = 1;

Change the default of No Result Set to Full Result Set. That tells SSIS to expect a table shaped return object but something needs to catch that incoming data.

In the Results tab, you now need to map the results into an SSIS variable. Assuming an OLE DB type connection manager, you'll select User::rsObject in the Variable list and then 0 as the recordset name (doing this from memory so specifics might be a slightly off)

Save and run that task. Assuming no errors, when the package runs we have a, potentially empty, set of data in our recordset object. Let's do something with that.

Shredding the data

The name I generally see applied to getting data out of enumerable objects in SSIS is called "shredding the data". The implementation of that is an Foreach Enumerator - one of the most powerful tools in your toolkit.

Drag a ForEach Loop Container onto the canvas. Drag the connector line (precedent constraint) from the "SQL Get Driver Data" to our new ForEach Loop Container. I'd name it "FELC Shred Results" to indicate my intent.

Double click the Task and change the default enumerator type from File System to "Ado.net recordset" This has no bearing on whether you used an OLE, ODBC or ADO.NET connection manager to populate the table-like object. If it's a table, use ADO.NET Recordset.

Specify our variable [User::rsObject] as the source of the Recordset object.

The last thing we need to do is configure what we should do with the current row in the enumerator. That's in the Mapping tab. Here you'll add two parameters and this will be a zero based ordinal system. Choose [User::City] (or whatever you've named your City variable) for your first entry and map that to column name 0. Add a row and use User::Period and map that to column 1

The final step is to take the existing logic (Data Flow Task and whatever else is variable dependent) and move it into the FELC. That's literally drawing a box around it with the mouse to highlight everything and hold the left mouse button and drag it into the FELC.

Hit F5 and you should have 2 files generated.