2

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?

hadEnoughOfKeyboardGangstars
  • 133
  • 5

1 Answers1

0

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.

Sam
  • 1,990
  • 1
  • 14
  • 21
  • 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 06 '10 at 10:16