Clever Geek Handbook

Running SSIS package after checking records in database table

Background

I would like to load data for 8 tables from Source (This gets data from elsewhere) to Target. I have a checklist in my source that maintains records every time the table is loaded. So before I start my SSIS package, I need to check if the control table in the source has records for all 8 tables.

SSIS package structure

  • Two variables RecordCount

    (default 0) and SQLQueryForRecordCount

    (SQL Query to get the number of records from the control table)
  • Executing an SQL task. Here I am executing a select query to get the record count and pass the result to a variableRecordCount

  • When connecting the data stream using, execute sql script

    I also chose the Expression as Evaluation Operation

    expression as @[User::RecordCount] == 8

    , so that the data stream is executed when RecordCount = 8

    .

Everything works fine so far.

The problem is that we are not sure when the records will be inserted into the control table in the source (this can be done at any time in the window in 3 hours). So I would like to loop through the 2nd step (execute the sql script) until the value of the variable RecordCount

reaches 8 and then start executing the next data flow tasks.

How do you achieve this? Please help me.

+3


source to share


2 answers


I find it easier to do this in a stored procedure and also prefer the ability to customize it without editing / deploying / promoting my packages.

In SSIS, you need a simple SQL execution task that calls a stored procedure. Then for the stored procedure, you need something like:



DECLARE @CountProcessed int

SELECT @CountProcessed = SUM(ControlField)
FROM ControlTable

WHILE @CountProcessed <> 8
   BEGIN
     WAITFOR DELAY 00:01:00 --Set to whatever time increment you want to wait
     SELECT @CountProcessed = SUM(ControlField)
     FROM ControlTable
   END

Then you can drop your SSIS variables and just start with that proc. No restrictions are required.

+3


source


Script Workaround

You can do a workaround by using Script Task

instead Execute SQL Task

:

  • Inside the Script task use SQLCommand

    to extractRecordCount

  • Add a while loop that will repeat execution SQLCommand

    untilRecordCount = 8

  • Your code should look like this:

    Dim recordcount As Integer = 0
Dim sqlQuery As String = ""
Public Sub Main()

    sqlQuery = Dts.Variables.Item("User::SQLQueryForRecordCount ").Value.ToString

    Using sqlcon As New SqlClient.SqlConnection("Server=myServerName\myInstanceName;Database=myDataBase;Integrated Security=SSPI;")

        sqlcon.Open()



        While recordcount < 8

            Using sqlcmd As New SqlClient.SqlCommand(sqlQuery, sqlcon)


                recordcount = CInt(sqlcmd.ExecuteScalar())
                'Theading.Thread.Sleep(5000) wait for 5 seconds

            End Using



        End While


    End Using

    Dts.Variables.Item("User::RecordCount").Value = recordcount

    Dts.TaskResult = ScriptResults.Success
End Sub

  • Finally, assign the Count value to the variable RecordCount



Note: you must add User::RecordCount

both ReadWriteVariable and User::SQLQueryForRecordCount

ReadOnly Variable in Script editor

Side note: you can add a command Theading.Thread.Sleep(5000)

inside While loop

to give more time for each command to execute

+2


source






More articles:


All Articles