我在使用VS 2013中的SSIS。
我需要从一个数据库获取一个ID列表,并使用该ID列表查询另一个数据库,即 SELECT ... from MySecondDB WHERE ID IN ({来自MyFirstDB的ID列表})
。
我在使用VS 2013中的SSIS。
我需要从一个数据库获取一个ID列表,并使用该ID列表查询另一个数据库,即 SELECT ... from MySecondDB WHERE ID IN ({来自MyFirstDB的ID列表})
。
有三种方法可以实现这个目标:
首先,您必须像@TheEsisia所回答的那样添加一个Lookup变换
,但还需要满足以下要求:
在Lookup中,您必须编写包含ID列表的查询 (例如:SELECT ID From MyFirstDB WHERE ...
)
至少您必须从lookup表中选择一个列
为了过滤行WHERE ID IN ({MyFirstDB中的ID列表})
,您必须在查找错误输出Error case
中进行一些工作,有两种方法:
忽略行
,以便添加的列(来自查找)值为null,因此您必须添加一个条件分割
,该分割过滤具有等于NULL值的行。假设您已经选择将col1
作为查找列,因此您必须使用类似的表达式
ISNULL([col1]) == False
重定向行
,这样所有行都将被发送到错误输出行,该行可能不会被使用,因此数据将被过滤。这种方法的缺点是在执行期间加载和过滤所有数据。
另外,如果在本地机器上(第二种服务器方法)进行网络过滤,则在加载所有数据后进行内存过滤。
为了避免加载所有数据,您可以使用一个解决方法。您可以使用脚本任务来实现:(以VB.NET书写的答案)
假设连接管理器名称为TestAdo
,"Select [ID] FROM dbo.MyTable"
是获取id列表的查询语句,并且User::MyVariableList
是要存储id列表的变量。
注意:此代码将从连接管理器读取连接。
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 Task
构建IN子句,然后将整个查询作为OLEDB Source
使用。
ResultSet
属性设置为single
User::MyVariableList
作为结果集使用以下SQL命令
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
如果列的数据类型为字符串,则应在值前后添加引号,如下所示:
SELECT @str = @str + '''' + CAST([ID] AS VARCHAR(255)) + ''','
FROM dbo.MyTable
确保您已将DataFlow Task
的Delay Validation
属性设置为True
Connection
选项卡中,使用 OLE DB 连接管理器
连接到您的第二个服务器。然后,您可以直接选择带有 ID
值的数据集,或者(如下图所示)使用 SQL 代码从过滤数据集中选择 ID。连接:
请转到“列”选项卡,并从两个数据集中选择“ID”列。对于来自第一个数据集的每条记录,它将检查其“ID”是否在“可用查找列”中。如果是,则进入“匹配”输出,否则进入“无匹配项”输出。确定
关闭 LookUp
。然后需要选择 LookUp Match Output
。匹配输出:
1,2,3
),在另一个变量中构建 SQL 命令作为表达式 "SELECT * FROM .... WHERE ID IN(" + @[user::variable] + ")"
,并将此变量用作 OLEDB SOURCE 的源。 - YahfoufiSELECT
'SELECT * from MySecondDB WHERE ID IN ( '
+ STUFF ( (
SELECT TOP 5
' , ''' + [name] + ''''
FROM dbo.spt_values
FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(4000)'
) , 1 , 3, '' )
+ ' ) '
AS SQL_Select
然后您可以在下游任务中使用变量SQL_Select,例如针对数据库2的OLE DB源。OLE DB源和OLE DB命令任务都允许您指定变量作为SQL语句来源。
EXEC sp_addlinkedserver @server='SRV' --or any name you want
EXEC sp_addlinkedsrvlogin 'SRV', 'false', null, 'username', 'password'
SELECT * FROM SRV.CatalogNameInSecondDB.dbo.SecondDBTableName s
INNER JOIN FirstDBTableName f on s.ID = f.ID
WHERE f.ID IN (list of values)
EXEC sp_dropserver 'SRV', 'droplogins'