Run SSIS Package after checking entries in database table

Question

Background

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

SSIS Package Structure

1. Two variables RecordCount (default 0) & SQLQueryForRecordCount (SQL Query to get record count from control table)
2. Execute SQL Task - Here I am executing the select query to get record count and passing the result to variable RecordCount
3. While connecting data flow with execute sql script, I have selected Expression as Evaluation Operation and expression as @[User::RecordCount] == 8 so that Data Flow will be executed when the RecordCount = 8.

Everything works fine till now.

The Problem is we are not sure about when the entries will be inserted into control table in source (it can be done anytime in a 3 hours window). So I would like to loop the 2nd step (execute sql script) until the RecordCount variable value reaches 8 and then kick off next data flow tasks.

How to achieve this? Please help me.

Answer 1

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

In SSIS, you will want a simple execute SQL task that calls the stored procedure. Then for the stored procedure, you would want 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

You can then discard your SSIS variables, and simply start with this proc. No constraints needed.

Answer 2

Script Task workaround

You can do a workaround using a Script Task instead of Execute SQL Task:

1. Inside the Script Task use a SQLCommand to retrieve the RecordCount
2. Add a While Loop that will repeat the SqlCommand execution until the RecordCount = 8

3. Your code should look like:

   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
   

4. At the end assign the Count Value to the recordCount Variable

Note: that you must add User::RecordCount as ReadWriteVariable and User::SQLQueryForRecordCount as ReadOnly Variable in the Script editor

Side Note: you can add a Theading.Thread.Sleep(5000) command inside the While loop to give more time for each command execution