• <legend id='tjG9y'><style id='tjG9y'><dir id='tjG9y'><q id='tjG9y'></q></dir></style></legend>
    • <bdo id='tjG9y'></bdo><ul id='tjG9y'></ul>
    <i id='tjG9y'><tr id='tjG9y'><dt id='tjG9y'><q id='tjG9y'><span id='tjG9y'><b id='tjG9y'><form id='tjG9y'><ins id='tjG9y'></ins><ul id='tjG9y'></ul><sub id='tjG9y'></sub></form><legend id='tjG9y'></legend><bdo id='tjG9y'><pre id='tjG9y'><center id='tjG9y'></center></pre></bdo></b><th id='tjG9y'></th></span></q></dt></tr></i><div id='tjG9y'><tfoot id='tjG9y'></tfoot><dl id='tjG9y'><fieldset id='tjG9y'></fieldset></dl></div>
  • <tfoot id='tjG9y'></tfoot>

    <small id='tjG9y'></small><noframes id='tjG9y'>

      1. 根据来自另一个数据库的查询结果查询一个数据库

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

          <tbody id='2hXF5'></tbody>
        <i id='2hXF5'><tr id='2hXF5'><dt id='2hXF5'><q id='2hXF5'><span id='2hXF5'><b id='2hXF5'><form id='2hXF5'><ins id='2hXF5'></ins><ul id='2hXF5'></ul><sub id='2hXF5'></sub></form><legend id='2hXF5'></legend><bdo id='2hXF5'><pre id='2hXF5'><center id='2hXF5'></center></pre></bdo></b><th id='2hXF5'></th></span></q></dt></tr></i><div id='2hXF5'><tfoot id='2hXF5'></tfoot><dl id='2hXF5'><fieldset id='2hXF5'></fieldset></dl></div>
          <bdo id='2hXF5'></bdo><ul id='2hXF5'></ul>

            <small id='2hXF5'></small><noframes id='2hXF5'>

            <legend id='2hXF5'><style id='2hXF5'><dir id='2hXF5'><q id='2hXF5'></q></dir></style></legend><tfoot id='2hXF5'></tfoot>
                1. 本文介绍了根据来自另一个数据库的查询结果查询一个数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我在 VS 2013 中使用 SSIS.我需要从 1 个数据库中获取 ID 列表,并使用该 ID 列表查询另一个数据库,即 SELECT ... from MySecondDB WHERE ID IN ({list of IDs from MyFirstDB}).

                  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:

                  首先你必须添加一个 Lookup Transformation 就像@TheEsisia 回答的那样,但还有更多的要求:

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

                  • 在查找中,您必须编写包含 ID 列表的查询(例如:SELECT ID From MyFirstDB WHERE ...)

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

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

                  要过滤行 WHERE ID IN ({list of IDs from MyFirstDB}) 你必须在查找错误输出中做一些工作 Error case 有两种方法:

                  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. 将错误处理设置为 Ignore Row 以便添加的列(来自查找)值将为 null ,因此您必须添加一个 Conditional split 来过滤具有相等值的行空值.
                  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. 或者你可以将Error处理设置为Redirect Row,这样所有的行都会被发送到错误输出行,这个行可能不被使用,所以数据会被过滤
                  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 并且 "Select [ID] FROM dbo.MyTable" 是获取 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 Task 构建 IN 子句,然后使用整个查询作为 OLEDB Source,

                  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 任务之前添加一个执行 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
                  

                  如果列是字符串数据类型,你应该在值前后添加引号,如下所示:

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

                  确保您已将 DataFlow Task Delay Validation 属性设置为 True

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

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

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  SQL query to group by day(按天分组的 SQL 查询)
                  What does SQL clause quot;GROUP BY 1quot; mean?(SQL 子句“GROUP BY 1是什么意思?意思是?)
                  MySQL groupwise MAX() returns unexpected results(MySQL groupwise MAX() 返回意外结果)
                  MySQL SELECT most frequent by group(MySQL SELECT 按组最频繁)
                  Include missing months in Group By query(在 Group By 查询中包含缺失的月份)
                  Why Mysql#39;s Group By and Oracle#39;s Group by behaviours are different(为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同)

                  • <tfoot id='MxS3G'></tfoot>

                    1. <small id='MxS3G'></small><noframes id='MxS3G'>

                      1. <i id='MxS3G'><tr id='MxS3G'><dt id='MxS3G'><q id='MxS3G'><span id='MxS3G'><b id='MxS3G'><form id='MxS3G'><ins id='MxS3G'></ins><ul id='MxS3G'></ul><sub id='MxS3G'></sub></form><legend id='MxS3G'></legend><bdo id='MxS3G'><pre id='MxS3G'><center id='MxS3G'></center></pre></bdo></b><th id='MxS3G'></th></span></q></dt></tr></i><div id='MxS3G'><tfoot id='MxS3G'></tfoot><dl id='MxS3G'><fieldset id='MxS3G'></fieldset></dl></div>
                          <tbody id='MxS3G'></tbody>

                        <legend id='MxS3G'><style id='MxS3G'><dir id='MxS3G'><q id='MxS3G'></q></dir></style></legend>
                            <bdo id='MxS3G'></bdo><ul id='MxS3G'></ul>