how to assign multiple rows data in a variablet in SSIS?

Question

Just wandering, is there any way that I can assign multiple rows data in a variable in SSIS?

example: I have the following table (tableA) with the following data created by the data flow task:

DataRow
Jay,10,11 Happy St\n\n
David,12,13 Angel St\n\n
Tom,30,23 Betman St\n\n

How can I able to assign those records into a variable in SSIS as below:

Jay,10,11 Happy St\n\nDavid,12,13 Angel St\n\nTom,30,23 Betman St\n\n

Then pass it into the Web service task. At the moment I'm running the web service task into the loop, but I would like to compile all the data row and pass over to the web service task outside the loop enter image description here

Any way that I can do it? Any example are link could share?

Using a ForEach loop is the method l would suggest. In the loop container you could use a script task to take the value from each row and append it to a variable. Each pass would just append the value to the end of value in the variable. Once it has looped over the rows then the next step would be to execute the Web Service Task passing it the variable containing the output from the rows. This page has some code technet.microsoft.com/en-us/library/ms135941.aspx — SQLBobScot
– SQLBobScot, Commented Apr 23, 2014 at 7:25

Playing With BI · Accepted Answer · 2014-04-23 07:51:50Z

2

If you can, take the source data using an "Execute SQL Task". If that is not possible you can create a temporary table and then populate this table in your data flow task. The key is to store your result set in a varible of type "Object" and then you can iterate it into a loop task and send every row to the Web Service task as you want.

Here you could find a detailed tutorial about this: Loop through ADO recordset in SSIS

If you need help with the temporary table just let me know.

Hope that helps,

Paul

answered Apr 23, 2014 at 7:51

Playing With BI

4211 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jin Yong Over a year ago

@Paul: I have more then 20000+ records to load into the web service. I have tried to using the loop to load 300+ records and it's take up to 6 mins to completed the task. If I using loop to load 20000+ records. It might take up to few hours to finished the task. Any better and efficiency way to load the large data from SSIS to wen service?

Playing With BI Over a year ago

@JinYong: Unfortunately the loads using a web service are slower than using batch inserts or similar, what you can do is analyse the processes that are trigger when you call the Web Service and try to deactivate some of them if it is suitable.

Collectives™ on Stack Overflow

how to assign multiple rows data in a variablet in SSIS?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related