CODEWITHSUNDEEP
sql-serverssisvisual-studio-2017
fireshark519
fireshark519

Reputation: 175

SSIS Foreach loop container suggestions

I have not used a foreach loop container before and need some help with this.

I have a SSIS package that at the moment does some stuff (import from excel into database, some merges, some joins).

Basically at the end I finish with a table that contains the following fields (modified):

Invoice number
Container number
Item number
Item price
item duty
(...)

I need it to do some specific SQL (joins, insert formulas ...) for each invoice and export that to an Excel file with file name = invoice number.

I thought of getting a select distinct ROW_NUmber () (...),[Invoice_number] from [invoicetable] and getting to iterate over the row_number basically and do what I needed it to do.

I've thought about maybe getting this on a SQL loop to create #temp1, #temp2, ... and then export all of the #temp individually and then dropping them.

I am looking for ideas of the most efficient way of getting this done.

I must say I have tried T-SQL to get the files to be exported using cmd but I keep getting errors trying to create the files.

Thanks in advance!

Upvotes: 2

Views: 339

Answers (2)

fireshark519
fireshark519

Reputation: 175

I ended up giving up on the foreach loop container. As the data input to my tables was an excel file I had so many problems with data type conversions in the middle that nothing wanted to work in the loop. I couldn't get it to iterate over the invoice "number/name".

I ended up looking at C# as an option and managed to loop it but could not get it to loop inside the loop initially, after some trial an error I managed to get the outcome I needed and this process is now running what prior BA created with 4 macros, in SSIS package reducing work time from 1800 minutes to 3.

The C# code is here:

C# Variable Excel file output loop

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

The way I would do this is use an Execute SQL Task to populate an object variable with all the invoice numbers in your table.

Then have a foreach loop that iterates over that object variable and begins with a script task that uses the value of the invoice number to set the name of your destination excel file, and also to generate the SQL Command of your data flow's source component to SELECT from the table WHERE InvoiceNumber={the current invoice number in the loop}.

Each iteration of the loop will select one invoice and send it to one appropriately-named Excel file. Then move on to the next invoice number.

Upvotes: 1

Related Questions