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.
One Solution collect form web for “Using results from a query/data source as a parameter in another query with SSIS”
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.
2: In second
Execute SQL Task, I have used
? to pass parameter in sql query connected to second source.
3 : Parameter mapping is set as below so that
? is mapped to ResultSet
variable during run time.