CODEWITHSUNDEEP
sql-serverperformancessisforeach-loop-container
Mark Griggs
Mark Griggs

Reputation: 337

SSIS Package with Foreach Loop Container Looping Through Object Variable

The Context: In a SQL server (2014) I have a table with a unique list of hundreds of store numbers. I have a DB2 LUW server that I need to pull data from based on those store numbers and dump this data into a different SQL server (2014). Due to limitations, I have to chunk the store numbers into groups of 25 and run the query several times.

To do this in SSIS, I use an Execute SQL Task to pull 25 store numbers at a time and STUFF them into a single-quote justified, comma-delimited string ('12345','03884',etc.) that I store into an object variable. I then use a Foreach Loop Container to pull the string out of the object variable and store it into a string variable. Next, I have a string variable that contains the DB2 query and it takes the store list string variable as a parameter.

"select x, y, z from DB2SERVERTABLE where store_number in " + @[User::Store_List]

Next, I use another Execute SQL Task to execute that query in the variable. The Full result set is stored in another object variable. To get the results out of the object variable and insert them into my other SQL server, I use another FELC to iterate through the result set storing each field in a string variable using the variable mappings screen. Inside the FELC I have a final Execute SQL Task that is a simple insert statement that inserts the variables into the SQL server.

insert into table values (?,?,?);

All of this, somehow, actually works.

The Hard(Soft)ware:

  1. VMWare Windows 2012 R2 server 64Bit
  2. Intel Xeon E5-4650 CPU 4-cores @ 2.69GHz
  3. 32GB RAM (SQL Server commits 27GB so really only 5GB...)
  4. Visual Studio 2015
  5. SSDT 2015
  6. DB2 LUW (not sure what version)
  7. SQL Server 2014

The Problem: The final FELC and insert run incredibly slow. In my last test, I pulled data for 1 store number out of DB2 (~95K rows). After 20 minutes, only 40K rows had been inserted into my SQL server and while watching the row count in the Progress tab, it got slower and slower and slower until it just froze up and I had to kill the process. Visual Studio was using ~3GB RAM and 50% CPU on avg at this point. I've been googling up a storm and I can't find anyone with a similar situation to mine. I'm thinking I might be better off doing a lot of this work in C# via a script task. I'm hoping someone out there might recognize the issue though before I start getting back into the code. Thanks!

Upvotes: 1

Views: 1756

Answers (1)

tember
tember

Reputation: 1496

For this step:

"The Full result set is stored in another object variable. To get the results out of the object variable and insert them into my other SQL server, I use another FELC to iterate through the result set storing each field in a string variable using the variable mappings screen. Inside the FELC I have a final Execute SQL Task that is a simple insert statement that inserts the variables into the SQL server."

Try inserting the data into a local SQL table and instead using a final FELC - use a Lookup to filter your data.

Upvotes: 0

Related Questions