根据来自另一个数据库的查询结果查询数据库 [英] Query a database based on result of query from another database

问题描述

我在VS 2013中使用SSIS。
我需要从1个数据库中获取ID列表，并希望使用该ID列表查询另一个数据库，即 SELECT。 .. from MySecondDB WHERE ID IN（{来自MyFirstDB的ID列表}）。

I am using SSIS in VS 2013. I need to get a list of IDs from 1 database, and with that list of IDs, I want to query another database, ie SELECT ... from MySecondDB WHERE ID IN ({list of IDs from MyFirstDB}).

推荐答案

有有3种方法可以实现：

There is 3 Methods to achieve this:

首先，您必须添加查找转换像@TheEsisia一样，但是有更多要求：

First you have to add a Lookup Transformation like @TheEsisia answered but there are more requirements:

• 在查找，您必须编写包含ID列表（例如：从MyFirstDB WHERE ... 中选择SELECT的ID）

• In the Lookup you Have to write the query that contains the ID list (ex: SELECT ID From MyFirstDB WHERE ...)

至少您必须从查找表中选择一列

At least you have to select one column from the lookup table

要过滤行 WHERE ID IN（{来自MyFirstDB的ID列表）} ，您必须在查找错误输出<$中进行一些工作c $ c>错误案例有两种方法：

To filter rows WHERE ID IN ({list of IDs from MyFirstDB}) you have to do some work in the look up error output Error case there are 2 ways:

1. 将错误处理设置为忽略行，因此（从查找中）添加的列值将为null，因此您必须添加有条件拆分来过滤值等于NULL的行。

1. set Error handling to Ignore Row so the added columns (from lookup) values will be null , so you have to add a Conditional split that filter rows having values equal NULL.

假设您已选择 col1 作为查找列，则必须使用相似的表达式

Assuming that you have chosen col1 as lookup column so you have to use a similar expression

ISNULL([col1]) == False

1. 或者您可以将错误处理设置为重定向行，因此所有行都将发送到错误输出行（可能不会使用），因此数据将被过滤

1. Or you can set Error handling to Redirect Row, so all rows will be sent to the error output row, which may not be used, so data will be filtered

方法是在执行期间加载和过滤所有数据。

The disadvantage of this method is that all data is loaded and filtered during execution.

同样，如果在加载所有数据后在本地计算机（服务器上的第二种方法）上完成了网络过滤工作，那么内存就是这样。

Also if working on network filtering is done on local machine (2nd method on server) after all data is loaded is memory.

为避免加载所有数据，可以采取一种解决方法，可以使用脚本任务来实现： （在VB.NET中写的答案）

To avoid loading all data, you can do a workaround, You can achieve this using a Script Task: (answer writen in VB.NET)

假定连接管理器名称为 TestAdo 和从dbo.MyTable中选择[ID] 是获取ID列表的查询，而 User :: MyVariableList 是您要存储ID列表的变量

Assuming that the connection manager name is TestAdo and "Select [ID] FROM dbo.MyTable" is the query to get the list of id's , and User::MyVariableList is the variable you want to store the list of id's

注意：此代码将从连接管理器读取连接

    Public Sub Main()

        Dim lst As New Collections.Generic.List(Of String)


        Dim myADONETConnection As SqlClient.SqlConnection  
    myADONETConnection = _  
        DirectCast(Dts.Connections("TestAdo").AcquireConnection(Dts.Transaction), _  
        SqlClient.SqlConnection)

        If myADONETConnection.State = ConnectionState.Closed Then
        myADONETConnection.Open()
        End If

        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


        Dts.Variables.Item("User::MyVariableList").Value = "SELECT ... FROM ... WHERE ID IN(" &  String.Join(",", lst) & ")"

        Dts.TaskResult = ScriptResults.Success
    End Sub

并且 User :: MyVariableList 应该用作源（变量中的Sql命令）

类似于第二种方法，但这将使用 Execute SQL来构建IN子句任务，然后将整个查询用作 OLEDB源，

Similar to the second method but this will build the IN clause using an Execute SQL Task then using the whole query as OLEDB Source,

1. 只需在DataFlow Task之前添加Execute SQL任务


2. 将 ResultSet 属性设置为 single


3. 选择 User :: MyVariableList 作为结果集



4. 使用以下SQL命令

1. Just add an Execute SQL Task before the DataFlow Task
2. Set ResultSet property to single
3. Select User::MyVariableList as Result Set

4. 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

< a href = https://i.stack.imgur.com/9cPZE.png rel = nofollow noreferrer>

如果列具有字符串数据类型，则应在值之前和之后添加引号，如下所示：

SELECT @str = @str + '''' + CAST([ID] AS VARCHAR(255)) + ''','
    FROM dbo.MyTable

确保已设置 DataFlow任务 延迟验证属性为 True

Make sure that you have set the DataFlow Task Delay Validation property to True

这篇关于根据来自另一个数据库的查询结果查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！