Passing value of SQL task to another

Question

Passing value of SQL task to another

kazim.raza

SSCrazy

Points: 2390
More actions
February 25, 2010 at 4:09 am

#156715

Hi:
I have a scenario where I have to execute a SQL query based upon the result of another sql task. This is the flow:
1) I have an Execute SQL task that executes a sql and gets result.
2) the result set is then passed to a foreach loop
Now I have to execute another sql query and have to pass the value from foreach loop into this execute sql. How can I do that?
Regards,
Kazim Raza

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Skull Killer SSCrazy Points: 2504 More actions · Answer 1

Hi.

Inside the For Each configuration you need to map each recordset column you need into a variable. Then you need to assign those variables to parameters of the sql task inside the for each.

The msdn link talking about sql task parameters: http://msdn.microsoft.com/en-us/library/cc280502.aspx

kazim.raza SSCrazy Points: 2390 More actions · Answer 2

Can you suggest some other resource? I have tried to look up on Google but haven't come across anything concrete.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

kazim.raza

1) I have an Execute SQL task that executes a sql and gets result.

From the extremely limited amount of information you have posted, have you thought of using your first T-SQL to insert the data returned into either a table variable or a temp table. With that you might have a high probability of using a set based solution instead of using a ForEach loop.

If you post a definition of your source table, along with some sample data, and the required results some one will be able to assist you further.

(Refer to the first link in my signature block on how to post the information to get a proven / tested answer)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Skull Killer SSCrazy Points: 2504 More actions · Answer 4

some links:

Running Parameterized SQL Commands Using the Execute SQL Task – I[/url]

Running Parameterized SQL Commands using the Execute SQL Task – II[/url]

How to use OUTPUT parameters with SSIS Execute SQL Task

SSIS: ForEach Looping through a recordset

Using the Foreach ADO enumerator in SSIS

I think this will help you with the basics. If there is another problem that arrise, please specify the details about the problem.

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 5

The easiest way to this would be to use a data flow task with Source and Record Set Destination that will hold your values in a variable...Then in the Control flow use a FOR each Loop with ADO and check the first option then use another variable to iterate.... and then place an execute sql task and use an expression instead of passing variables to the task... (Write you SQL statement in variable)

If i wanted to insert values coming in from my recordset destination ... instead of using a sql statement in Execute sql task like

INSERT INTO TABLE VALUES(@var) I will use this as an exp in variable like "INSERT INTO TABLE VALUES("+@var+")"

Expressions always make your life easy 🙂

stricknyn SSCertifiable Points: 6560 More actions · Answer 6

You can also use a data flow task with a source and a SQL Command data flow task. The source gets your data and the SQL Command task will execute a SQL Statment for every row the your source pulled.

Strick

kazim.raza SSCrazy Points: 2390 More actions · Answer 7

Thank you all.

I have to focus on another task as a result of shuffling priorities but I will certainly get back to it in a few days, with the schema, of course! 🙂

Regards,

Kazim Raza