SSIS foreach loop to group all unique customers in a table and write them to their own file

Question

I have a table which stores all of my customers and their invoices (less than 5k total), I want to to use a foreach loop container to write each one of these (customers) to their own file listing their own invoices.

I have used a foreach loop container to read/load/write files before so I understand that part but how do I apply the foreach loop on the AccountNumber as the enumerator?

For each file, I only want that customers info.

My table:

AccountNumber   InvoiceNumber   OriginalCharge
A255            2017-11         225.00
A255            2017-12         13.50
A255            2018-01         25.00
D870            2017-09         7.25
D870            2017-10         10.00
R400            2016-12         100.00
R400            2017-03         5.00
R400            2017-04         7.00
R400            2017-09         82.00

So this would produce 3 files and would include the invoices/original charge for the given customers.

File 1 = Customer A255

File 2 = Customer D870

File 3 = Customer R400

Or should I approach this differently?

Environment: SQL Server 2014 SSIS-2012

Thanks!

Answer 1

You'll need to apply a few different recipes to make this work.

• Dynamic file name
• Source query parameterization
• Shredding record set

Assumptions

You have three SSIS Variables:

• CurrentAccountNumber String (initial value of A255)
• rsAccountNumbers Object
• FileNameOutput String EvaluateAsExpression = True "C:\\ssisdata\output\\" + @[User::CurrentAccountNumber] + ".txt"

The package would look something like

[Execute SQL Task] -> [Foreach (Ado.net) Enumerator] -> [Data Flow Task]

Execute SQL Task

Set the resultset type to Full

Your source query would be SELECT DISTINCT AccountNumber FROM dbo.Invoices;

In the Results tab, assuming OLE DB Connection Manager, click add result button and use a "name" of 0 and the variable becomes User::rsAccountNumbers

Foreach (Ado.net) Enumerator

Set your enumerator type as Ado.NET and single table. Use the variable User::rsAccountNumbers and assign the zeroeth element to our variable CurrentAccountNumber

Run the package as is to verify the Execute SQL Task is returning a resultset that the Foreach can shred. Observe that each loop in the enumerator results in the value of our Variable FileNameOutput changing (C:\ssisdata\output\A255.txt, C:\ssisdata\output\D870.txt, etc)

Data flow task

This a simple flow

[OLE DB Source] -> [Flat File Destination]

Configure your OLE DB Source to be a Query SELECT * FROM dbo.Invoices WHERE D.AccountNumber = ?;

Click the Parameter button. Configure the name 0 to be @[User::CurrentAccountNumber]

Flat File Destination - Connect the Source to the destination, create a new Flat File Connection Manager and connect the columns.

Dynamic file name

The final piece will be to edit the Flat File Connection manager created above to use the variable FileNameOutput instead of the hard coded value you indicated. Right click on the Flat File Connection manager and select Properties. In the resulting properties window, find the Expressions property and click the ellipses (...) In the lefthand window, find ConnectionString and in the righthand window, use @[User::FileNameOutput]

F5 and the package should fire up and generate an output file per account number.