CODEWITHSUNDEEP
sql-serverssis
Shannon Rogers
Shannon Rogers

Reputation: 45

Using results from a query/data source as a parameter in another query with SSIS

I'm looking for the best way to use the results from one query into another query, which uses a different server and database. I have to OLE DB sources set up. The first source will give me a list of 12 digit long numbers that I will need to use in my second query as a filter. Example

1st OLE DB Source:

SELECT DISTINCT
digits
from foo

2nd DB Source

SELECT DISTINCT
numbers
from abc
where numbers IN
(select digits from 1st DB Source)

I do not have any DBA access to sp's, so they are out of the question. What is the best way to approach this? I'm not sure if there is a way to output the results from the 1st source into a variable so that my second query could use it.

Upvotes: 2

Views: 3359

Answers (1)

Prabhat G
Prabhat G

Reputation: 3029

I'm not sure if there is a way to output the results from the 1st source into a variable so that my second query could use it.

YES ! You can. Here is a step wise demo.

1: I have created 2 sources(source2012, source2014) and a variable (varResult) which stores value returned by my Execute SQL Task connected to source2012. As depicted below, set Result set tab to store value returned by SQL query. img1

2: In second Execute SQL Task, I have used ? to pass parameter in sql query connected to second source.

img2

3 : Parameter mapping is set as below so that ? is mapped to ResultSet variable during run time. img3

Upvotes: 3

Related Questions