2

I have an SSIS package with two Execute SQL Tasks, the first one has a simple select statement to select a single column from a table, the result set is an ID column which I have to use as input to the second Execute SQL Task to get records from another table which matching IDs. Basically I have to implement following SQL statement in SSIS:

SELECT * FROM TableB WHERE ID IN
(
   SELECT ID FROM TableA
)

How can I configure the second Execute SQL task to use the result set of first Execute SQL task as input to execute an SQL statement like mentioned above?

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56
seadrag0n
  • 848
  • 1
  • 16
  • 40
  • so why don't make single task then – Bhuban Shrestha May 22 '17 at 08:29
  • It sounds like you try do to what is usually done using a `Data Flow` and you should be able to combine your 2 tasks into 1 just by creating a query joining the two tables. – Filburt May 22 '17 at 08:31
  • @Filburt I did not mention this in the question..`TableA` is present on an `MSSQL server` and `TableB` is present on an `Oracle Database server`thats why I am trying to do this with two Execute SQL tasks, is that possible? – seadrag0n May 22 '17 at 09:24
  • You could solve this by creating a Linked Server connection to your Oracle db. This would allow you to query across both databases. – Filburt May 22 '17 at 09:30
  • @Filburt any other way to do this without creating a linked server? I want to able to manage this in the SSIS package itself.. – seadrag0n May 22 '17 at 09:39
  • 1
    A possible solution w/o a Linked Server would be pulling your `Table A` using a `DataFlow` and joining your `Table B` using a [Lookup Transformation](https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/lookup-transformation) or pulling both tables and use a [Merge Join Transformation](https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/lookup-transformation). However a Linked Server query likely has far better performance. Overall the solution depends on the number of records you expect. – Filburt May 22 '17 at 10:35
  • Possible duplicate of [Query a database based on result of query from another database](https://stackoverflow.com/questions/43746258/query-a-database-based-on-result-of-query-from-another-database) – Hadi May 22 '17 at 21:21

1 Answers1

0

One solution would be to populate an object variable with the result of the first Execute SQL Task.

Then have a Script Task which loops through the object variable and dynamically builds the SQL String for the second Execute SQL Task, and puts that into a string variable.

Then finally, the second Execute SQL Task uses the string variable for its SQL query.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52