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:

  • Copy data from one SQL Server database table to the other
  • declare variable for query string
  • Insert error, Incorrect syntax near '2'
  • SQL Server Select Case Statement with two distinct Value in two Columns
  • .NET SqlDependency with many notifications vs. high rate polling?
  • Storing DateTime (UTC) vs. storing DateTimeOffset
  • 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.

  • Update a SQL table with values from another nested query
  • Entity framework performance tuning
  • Getting sourcecontrol on stored procedures
  • Add custom header with sql server database mail
  • T-SQL CASE statement relies on another CASE statement in same SELECT query
  • DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK
  • 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.