Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am looking for a way to execute sub queries from within a main query that I execute in my SSIS package.

So When I execute the package, I want to be able to execute the sub query after everyline that is produced by the main query and pass the data from the row in main query to the sub query.

Is this possible?

share|improve this question
up vote 0 down vote accepted

Yes, you create a variable of type object. Execute your query and place the results into that SSIS variable. Make the next step a ForEach loop and use the iterator of ADO recordset type. You'll pick your object variable to use for that. Hopefully that gets you going.
You need to define variables to use from the recordset and they will be available to whatever steps you put inside that loop container.

If you're just using SQL tables, I'd recommend using a stored procedure, SSIS can just complicate things.

This is from memory, so I may be forgetting a setting or step.

share|improve this answer
Thanks for the help sam. I used two object variables in the end, one was assigned to an array list and the other way to take the next item from the object and to be passed into the loop. I agree that SSIS just complicates everything, Somthing as simple as a dynamic variable was quite a faf to get working. – hadEnoughOfKeyboardGangstars Aug 6 '10 at 10:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.