How to Query Multiple Databases in SSIS Based on Retrieved Data?

Querying Multiple Databases Based on Retrieved Data

In SSIS, it is often necessary to retrieve data from one database and use that data to query another database. This can be achieved through several methods:

Method 1: Lookup Transformation

The Lookup Transformation allows you to retrieve data from a second database and add it to the data stream. However, it does not inherently filter rows based on the retrieved data.

To filter rows using the Lookup Transformation, handle errors during the lookup process. You can set Error Handling to Ignore Row and use a Conditional Split to remove rows with null values in the added columns. Alternatively, you can set Error Handling to Redirect Row to route all rows to the error output line, which can then be filtered.

Method 2: Script Task

For more selective data retrieval, consider using a Script Task:

' Get the list of IDs from the first database
Dim lst As New Collections.Generic.List(Of String)
Dim myADONETConnection As SqlClient.SqlConnection = _
    DirectCast(Dts.Connections("TestAdo").AcquireConnection(Dts.Transaction), _
    SqlClient.SqlConnection)
myADONETConnection.Open()

Dim myADONETCommand As New SqlClient.SqlCommand("Select [ID] FROM dbo.MyTable", myADONETConnection)
Dim dr As SqlClient.SqlDataReader
dr = myADONETCommand.ExecuteReader

While dr.Read
    lst.Add(dr(0).ToString)
End While

' Construct the query for the second database
Dts.Variables.Item("User::MyVariableList").Value = "SELECT ... FROM ... WHERE ID IN(" & String.Join(",", lst) & ")"

Assign the "User::MyVariableList" variable as the source for the subsequent data retrieval.

Method 3: Execute SQL Task

Another approach is the Execute SQL Task:

• In the Execute SQL Task, set ResultSet to Single and Result Set to User::MyVariableList.
• Use the following SQL command:

DECLARE @str AS VARCHAR(4000)
SET @str = ''
SELECT @str = @str + CAST([ID] AS VARCHAR(255)) + ','
FROM dbo.MyTable
SET @str = 'SELECT * FROM MySecondDB WHERE ID IN (' + SUBSTRING(@str,1,LEN(@str) - 1) + ')'
SELECT @str

This generates an SQL query that includes the retrieved IDs in an IN clause. Assign the output to the User::MyVariableList variable and use it as the OLEDB Source for data retrieval from the second database.

The above is the detailed content of How to Query Multiple Databases in SSIS Based on Retrieved Data?. For more information, please follow other related articles on the PHP Chinese website!