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 multiple rows in single result using SQL Server 2008
  • SQL Server Profiler - How to filter trace to only display TSQL containing a DELETE statement?
  • How to check if email is already in use in asp.net and making sure email is available before allowing the user to register?
  • Copy table rows using OUTPUT INTO in SQL Server 2005
  • Find T-SQL to Transpose A Table By Chunks
  • SQL Parameter Syntax: Declare @Parameter .
  • 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.

  • User permission error when accessing “user instance” database from ASP.NET
  • Converting seconds into HH:MM:SS format
  • Using Entity Framework to migrate database structure
  • Temporary Table within Dynamic SQL (SQL-Server)
  • Select column where another related column's total is 0
  • Getting control back to a procedure in SQL Server
  • 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.
    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.