Different ways for executing SQL Server Integration Package

With Business Intelligence Development Studio (BIDs) we get a template for creating SQL Server Integration Services (SSIS) Project, which in turn creates individual packages. Each package has an extension .dtsx (Data Transformation Services with XML) which is a unit of deployment and execution. There are several ways in which the package can be executed. Here are some of those

1. Run the package from BIDs itself.

• With this option, we have the advantage of working with debug mode for control flow tasks and Data Viewer for Data Flow Tasks.
• The disadvantage is that we will not have Visual Studio on deployment server.

2. Run the package with Command line utility DTEXECUI

• This means Data Transfer EXecution User Interface
• User Interface is provided with the help of which certain properties like setting command line parameters, specifying configuration files, setting values for variables at run time etc

3. Run the package wit Command line utility DTEXEC

• As the name suggests, this option does not have an user interface but certain switches that can be used with the command
• Depending upon where the package is stored, we can use different switches for file deployment or SQL server deployment
• Users can specify properties values with /Set

4. Run the package after deploying it on the server either in a File System or on a SQL server database (MSDB)

• This option will provide a similar interface like DTEXECUI

5. Run the package as a SQL Server Agent job

• Create a step for SQL server agent job to execute a package and depending upon the schedule, the package will be executed automatically

6. Write a code with Visual Studio with C# or VB.NET which will execute package from the application. In this option, we will need to first set references to System.SQLServer.ManagedDTS so as to load existing package in the application by using Application.LoadPackage() method and then execute it by using the Package.Execute() method.

C#

using Microsoft.SqlServer.Dts.Runtime;

Package pkg = new Package();
Application app = new Application();
//load and execute package
pkg = app.LoadPackage(@"<path>/<PackageName>.dtsx”, null);
pkg.Execute();

VB.NET

Imports Microsoft.SqlServer.Dts.Runtime

Package pkg= new Packagr()
Application app= new Application()
'load and package
pkg = app.LoadPackage(“<path>/<packagename>.dtsx”,null)
pkg.Execute()