来自另一个表的SSIS更新表值

Question

我想每晚更新来自TableB的值的TableA。现在，我正在尝试使用脚本任务和SSIS 2005来执行此操作。每次尝试以这种方式进行更新时，我都会遇到超时错误。

SSIS中有更好的方法吗？

低于当前信息：

Public Sub Main() 

    Const Component_Name As String = "Updating TableA Data" 
    Const Conn_String As String = "Data Source=DB_A;Initial Catalog=TableA;Integrated Security=SSPI;" 

    Const sql_Emp As String = "Update TableA Set Contract = c.License_No, SEIN = convert(varchar, c.Lic_Exp_Date, 101) " _ 
     & "From Server.DB_B.dbo.TableB c Inner Join TableA b on " _ 
     & "rtrim(ltrim(c.business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14)))) " _ 
     & "Where c.Lic_Exp_Date = (select Max(Lic_Exp_Date) From Server.DB_B.dbo.TableB " _ 
     & "Where rtrim(ltrim(business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14))))) " _ 
     & "and convert(varchar, c.Lic_Exp_Date, 101) <> convert(varchar, b.SEIN, 101)" 

    Dim con As SqlConnection = New SqlConnection(Conn_String) 

    Try 
     Dts.Log("Opening DB Connection: " & con.ConnectionString, 0, Nothing) 

     con.Open() 

     Dim duh As New SqlCommand(sql_Emp, con) 
     duh.ExecuteNonQuery() 

     con.Close() 

     Dts.Log(String.Format(Component_Name), 0, Nothing) 
     Dts.Events.FireInformation(0, Component_Name, String.Format("TableA Data Updating"), "", 0, True) 

     Dts.TaskResult = Dts.Results.Success 

    Catch ex As Exception 
     Dts.Events.FireError(0, Component_Name, ex.Message, "", 0) 
     Dts.Log("Exception detected: " & ex.ToString, 0, Nothing) 
     Dts.TaskResult = Results.Failure 

    End Try 

End Sub 

Answer 1

让我们开始通过清理，截至一点点：

Public Sub Main() 

    Const Component_Name As String = "Updating TableA Data" 
    Const Conn_String As String = "Data Source=DB_A;Initial Catalog=TableA;Integrated Security=SSPI;" 

    Const sql_Emp As String = _ 
      "UPDATE TableA" _ 
      + " SET Contract = c.License_No, SEIN = convert(varchar, c.Lic_Exp_Date, 101)" _ 
     + " FROM Server.DB_B.dbo.TableB c" _ 
     + " INNER JOIN TableA b" _ 
      + " ON rtrim(ltrim(c.business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14))))" _ 
     + " WHERE c.Lic_Exp_Date= (" _ 
      + " SELECT MAX(Lic_Exp_Date)" _ 
      + " FROM Server.DB_B.dbo.TableB" _ 
      + " WHERE rtrim(ltrim(business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14))))" _ 
      + ") AND convert(varchar, c.Lic_Exp_Date, 101) <> convert(varchar, b.SEIN, 101)" 

    Try 
     Using con As New SqlConnection(Conn_String), _ 
       cmds New SqlCommand(sql_Emp, con) 

      Dts.Log("Opening DB Connection: " & con.ConnectionString, 0, Nothing) 

      con.Open() 
      cmd.ExecuteNonQuery() 

      Dts.Log(String.Format(Component_Name), 0, Nothing) 
      Dts.Events.FireInformation(0, Component_Name, String.Format("TableA Data Updating"), "", 0, True) 
      Dts.TaskResult = Dts.Results.Success 
     End Using 

    Catch ex As Exception 
     Dts.Events.FireError(0, Component_Name, ex.Message, "", 0) 
     Dts.Log("Exception detected: " & ex.ToString, 0, Nothing) 
     Dts.TaskResult = Results.Failure 

    End Try 

End Sub 

好了，现在我可以读它，我可以开始寻找可能被打破。几分钟后回来查看编辑。

---

好吧，现在我们来看看该查询。我错过了一些数据类型信息，所以我会做一些假设。请纠正任何错误：

• b.Account_Key是某种数字类型，可能是int。否则，你就不需要转换为varchar
• 的Lic_Exp_Date列真的是DateTime类型

的如果这些都是正确的，我认为这会做你想做的，但做它很多快：

UPDATE TableA 
    SET Contract = c1.License_No, SEIN = DATEADD(dd,0, DATEDIFF(dd,0, c1.Lic_Exp_Date)) 
FROM TableA b 
INNER JOIN Server.DB_B.dbo.TableB c1 
    ON ISNUMERIC(c1.busines_lic) = 1 AND cast(c1.business_lic AS int) = b.Account_Key 
INNER JOIN 
    (
     SELECT business_lic, MAX(Lic_Exp_Date) AS Lic_Exp_Date 
     FROM Server.DB_B.dbo.TableB 
     GROUP BY business_lic, License_No 
    ) c2 ON c2.business_lic = c1.business_lic AND c1.Lic_Exp_Date=c2.Lic_Exp_Date 
WHERE DATEADD(dd,0, DATEDIFF(dd,0, c1.Lic_Exp_Date)) <> DATEADD(dd,0, DATEDIFF(dd,0, b.SEIN)) 

这里是什么改变了：

• 打开相关子查询转换为连接。连接速度会快得多，但查询优化器可能已经为您执行此操作了。
• 消除调用多个每行函数的需要 - 还应该帮助您更好地与索引进行匹配。
• 使用整数的比较，而不是字符串为您的主加盟
• 使用日期函数，而不是转换为字符串删除时间部分，这应该是更快，反过来让我：
• 使用日期比较而比您的连接中的字符串比较

Answer 2

更好的方法将是一个执行SQL任务。