SSIS - [How To Use Precedence Constraint]

Scenario:

We are loading data from source file to our SQL Server table.  After the load we want to send email if number of records loaded are more than 5.

Solution:

We will be using Precedence Constraint with expressions to handle this scenario. Here is step by step approach.

Step 1:
Create a variable RowCnt with scope= Package level as shown below. We will be using this variable in expressions to make decision to send email or not.


Step 2:
Bring data flow task on Control Flow pane and then build as shown below. It should be extracting from Source File, Row Count Transformation will be using RowCnt variable to capture  rows moved between source and destination. Use OLE DB Destination to insert data into SQL Table.



 Step 3:
Choose Control Flow and connect Data flow task to Send Mail Task. After connecting them, double click on green connection ( Precedence constrain) and write expression as shown below.



 We are considering two things here, Once Success of Previous Task and Expression should be true. If both will be true then Send Mail Task will be execute else It will stop at Data Flow Task.

6 comments:

  1. I think SSIS is actually a very important and useful component to solve extreme complex IT problems.

    SSIS PostgreSql Write

    ReplyDelete
  2. This Example is not Precedence constraint , it is Data flow path,
    Data flow paths deal with moving data but precedence constrain deal with work flow tasks handling in control flow.

    ReplyDelete
  3. Precedence constraints are the green, red, and grey/blue connectors in the Control Flow that link the tasks together and can be used to manage the workflow of a package and handle error conditions. Data Flow paths deal with moving data and precedence constraints deal with workflow handling

    ReplyDelete