Performing Control Flow task in SSIS Package

Steps to create Control Flow in SSIS package


Below is an example design for control flow

Get a SQL task and name it as 'SQL Insert Audit' to insert audit values and update the script in 'SQL Statement' field as given below:

exec spu_insert_audit ?,?, ?,?,?,? OUT

and go to the Parameter Mapping option and add the variables as shown below:

next, get a SQL task and name it as 'SQL Drop All FK' and update the script in 'SQL Statement' field as given below:

exec [dbo].[sp_ExecDropAndRebuildSql]  '','','drop','r' 

now get an 'Execute Package Task' and name it as 'EPT package_name'. In that, go to Package option and select the Location field as 'File System'. In Connection field, select the package name. See below:

now go to the variable option and create variables as shown below:

here the value path for the variable name 'sPkgFolder' is the place where the packages saved.

next we can see the package_name.dtsx created in the Connection Managers area. Select the package name and press F4 to go to the property window.

In Property window, go to the Expression field and select the property as 'ConnectionString' and expression as '@[User::sPkgFolder]+ @[User::sPkgFileNameStaging]'.  Drag the variables from the available list and form the script. Click the Evaluate Expression' button to confirm the expression. See below screen:

do the same step to connect all the packages in control flow as showed in the example design of control flow package in top.

Finally need to get a SQL task to rebuild the Foreign Key. Get a SQL task and name it as 'SQL Rebuild All FK' and update the script in 'SQL Statement' field as given below:

exec [dbo].[sp_ExecDropAndRebuildSql]  '','','create','F'