title: SSIS Update Table Values from Another Table
body: <p>I want to update TableA with values from TableB on a nightly basis.  Right now, I'm trying to do this with SSIS 2005 using a Script Task with the SQL in it.  Each time I try to do the update in this manner, I get a time-out error.</p>
  
  <p>Is there a better way to do this in SSIS?</p>
  
  <p>Current information below:</p>
  
  <pre><code>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) " _
          &amp; "From Server.DB_B.dbo.TableB c Inner Join TableA b on " _
          &amp; "rtrim(ltrim(c.business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14)))) " _
          &amp; "Where c.Lic_Exp_Date = (select Max(Lic_Exp_Date) From Server.DB_B.dbo.TableB " _
          &amp; "Where rtrim(ltrim(business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14))))) " _
          &amp; "and convert(varchar, c.Lic_Exp_Date, 101) &lt;&gt; convert(varchar, b.SEIN, 101)"
  
      Dim con As SqlConnection = New SqlConnection(Conn_String)
  
      Try
          Dts.Log("Opening DB Connection: " &amp; 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: " &amp; ex.ToString, 0, Nothing)
          Dts.TaskResult = Results.Failure
  
      End Try
  
  End Sub
  </code></pre>