SSIS Update Table Values from Another Table

Question

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.

Is there a better way to do this in SSIS?

Current information below:

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

The better way would be an Execute SQL task.

Answer 2

Let's start by cleaning that up a little:

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

Okay, now that I can read it I can start looking at what might be broken. Check back in a few minutes for edits.

---

Okay, now let's look at that query. I'm missing some data type information, so I'm going to make some assumptions. Please correct any that are wrong:

• b.Account_Key is some number type, probably int. Otherwise you wouldn't need to convert to varchar
• The Lic_Exp_Date columns really are of type datetime

If those are correct, I think this will do what you want, but do it a lot faster:

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))

Here's what changed:

• Turn the correlated subquery into a join. A join will be much faster, however the query optimizer might have been doing this for you already
• Eliminate the need to call a number of per-row functions - should also help you match up with indexes better.
• Use an integer comparison rather than string for your main join
• Use date functions rather than convert to strings to remove the time portion, which should be much faster and in turn allow me to:
• Use date comparisons rather than string comparisons in your join