Execution Modes of SSIS package in Detail

I wrote a blog on various modes of SSIS package execution few days back. But I did not explain any one of them. So, I thought of giving a detailed explaination of how to execute an SSIS package using various execution modes. To check and verify the result of each execution mode, create a table as:


Now create a simple SSIS package using BIDS. The package will contain a variable named Mode of type string (in the scope of package)and an Execute SQL Task. Use following insert statement as SQL Statement in execute sql task:
INSERT INTO SSISExecutionMode (ExecutionMode)SELECT ?
"?" is parameter which will be mapped to variable (Mode) using Parameter Mapping tab inside the Execute SQL Task Editor. Now the stage is set to start executing the package using various modes and checking the result in table.
Following are the various modes:
Using BIDS
Open the package and set the value for the variable "Mode" as BIDS as shown in the below figure and then execute the package.


Verify the package execution result in the table by issuing a Select statement against the table as: SELECT * FROM SSISExecutionMode
The result will show 1 row in the table with Execution Mode value as BIDS
Using DtExecUI (Execute Package Utility)
Go to Run and enter DtexecUI to open the execute package utility. Select the package source as File System (if the package is deployed on SQL Server, select the SQL Server as Package Source) and click on ellipsis (…) to locate the actual package as shown below:
The path shown in above figure is the actual location of the package. Package can be executed by clicking on execute button but before that set the value for variable "Mode" as "DtexecUI". To set the value of a package variable use "Set Values" tab in the execute package utility as shown below:
Now click the Execute button and check the result in table. This time there will be a new entry in the table with Execution Mode as DtexecUI.

Next