Run SSIS Package after checking entries in database table

Viewed 1229

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.

2 Answers
Related