Sunday, January 24, 2016

Using execute package task in SSIS

Execute package task is used to break down the complex package into smaller one. This task allows you to run another package, executable file of workflow.
Salient features of execute package task:-
  1. Break the complex package into multiple packages and execute by calling them from this task. This will facilitates better maintenance and easier access to each individual task.
  2. Re-using the same tasks can be easy. For example you can use any package in any other package which require same functionality (or) same data.
  3. If you are loading star schema you require different packages to fill each dimension. In step by step loading each finishing task will contain one execute process task which call other package to start loading data into dimension.
  4. Controlling package security is major task. By breaking huge package into small package and giving permission to a specific part to the user this is a great security future granting access to the particular part
  5. A package that runs other package is generally referred as parent task, Execute process task is very use full in parent child configuration.
  6. Stored procedure execution, database maintenance activity can be planed through execute package task
Let’s consider this scenario for better understanding. Parent task will load data from HRdepartment data to destination in another database. Where the child task will updates the department id’s.
Create parent task which loads data from source table to destination
Create child task which contain department id updating task. Drag execute sql task into control flow and create connection to the destination table.
Paste the following code in sql command to update department id.
update [dbo].[HRD] SET DepartmentID= (case when GroupName='Research and Development' Then 1when GroupName='Sales and Marketing' then 2  when GroupName='Inventory Management' then 3  when GroupName='Manufacturing' then 4 when GroupName='Inventory Management' then 5 end )
Now go to parent task and add execute package task in control flow and start configuring it.
Go to package option and there you will find below options
Reference type:-
  1. Project Reference: - Package which already in the same folder (or) same project.
  2. External Reference: - Package which presents in another project.
Here I selected project reference where my child task exist in the same project.
PackageNameFromProjectReferece:- select the child task which you want to execute
User name and password not require. If the package is protected with user name and password then you need to enter username password and execute the package done.
Hrd table in another database.