Eight ways of executing SSIS package and passing parameters (T-SQL, VB.net, C#, SQLAgent, DTExec)

This article will explain differnt ways of executing SSIS package in interactive mode and non-interactive mode from your T-SQL/VB/C# code

1. Executing SSIS package from SQL Agent Job ( Non-interactive mode )
2. Executing SSIS package using commandline - DTExec
3. Executing SSIS package using DTSExecUI.exe  ( Interactive mode )
4. Executing SSIS package from T-SQL Code ( using xp_CmdShell )
5. Executing SSIS package from .Net Application using SSIS API ( VB.net or C# ) 
6. Executing SSIS package from BIDS (Run in 32bit mode or 64Bit Mode)
7. Executing SSIS package from SQL Server Management Studio
8. Executing SSIS package on Remote Machine (Various Techniques)


There are many ways you can execute SSIS package. No matter what option you pick behind the scene SSIS Runtime will use DTExec commandline application to execute your package.

Note: For 64Bit Machine

If you have 64-Bit OS then you will have two version of DTExec installed. 32Bit and 64Bit

On 64bit Machine By default 64Bit version of DTExec will be executed. If you want to use 32bit version of DTExec then you will have to specify the different path for DTExec as below
C:\Program files (x86)\Microsoft Sql Server\90\dts\binn\dtexec.exe

Some times if you using 32-Bit Driver (e.g. MS Jet/Excel Driver, any ODBC Driver) in SSIS Package then you will have to call 32bit DTExec when calling it from commandline.

Executing SSIS package from SQL Agent Job (Non-interactive mode)

If you want to schedule SSIS package via SQL Agent Job then perform the following steps.


Create Job

1. Open SSMS (SQL Server management Studio)
2. Expand SQL Agent Node and go to Jobs Folder (Note: If SQL Agent Service is not running you will be prompted to start it)
3. Right click on the Jobs folder and enter Job name (For this example name it "TestSSISPackage")

Create Step

1. To create new step on the Job Dialogbox click on the "Steps" tab (Left side) and enter step name (For this example name it "Step-1")
2. Change step type to "SQL Server Integration Services Package"
3. Select Proxy Account. Proxy Account is very important to make sure your Job runs without any error. By default SQLJob Runs using credentials of SQL Agent  Service Account (Most of the times its Networks Services or Local System Account). If you are accessing file systrem or using windows authentication then you will need an Proxy account which has all necessary permissions to connect to database and read/write file system if needed. how to configure "Proxy Account" URL: http://msdn.microsoft.com/en-us/library/ms189064.aspx
4. When you change Step-Type to "SQL Server Integration Services Package" you will see many new tabs. On the general tab ... change the following items
5. Change Package Source to Location where your packages are stored (For this example Package is stored on File system) 
6. If you select SQL Server then you will have to enter Server/UserID/Password  and If you select SSIS package Store then only Server Name is needed becuase SSIS Package Store is only allowed for Authnticated Windows Account
7. And specify package path.
8. You can also pass variable values as below




Scedule Job
http://msdn.microsoft.com/en-us/library/ms191439.aspx

Executing SSIS package using commandline - DTSExec

You can execute SSIS Package using command line by executing DTExec command.  DTExec is commandline application used to execute SSIS package. DTExec is found under the following path

C:\Program files\Microsoft Sql Server\90\dts\binn\dtexec.exe



Examples:

-- Running package stored on File System
dtexec /F "C:\Package1.dtsx"

-- Running package stored on SQL Server (Mixed mode)
dtexec /SQL "\Package1" /SERVER PROD001 /USER sa /PASSWORD Pass01

-- Execute filesystem package and pass single variable value
dtexec /F "C:\Package1.dtsx" /SET "\Package.Variables[User::fileName].Properties[Value]";"CUST001.txt"

-- Execute filesystem package and pass multiple variable values
dtexec /F "C:\Package1.dtsx" /SET "\Package.Variables[User::fileName].Properties[Value]";"CUST001.txt" /SET "\Package.Variables[User::employeeID].Properties[Value]";9999

-- Running encrypted package (passing password to commandline)
dtexec /F "C:\Package1.dtsx" /DECRYPT mypass123

-- Set entire connection string property
dtexec /F "C:\Package1.dtsx" /CONNECTION MyConnection "\"Data Source=(local);Initial Catalog=Northwind;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""

-- Set any property with double quotes (Example -  Varibale value  is  [Babies"R"Us]  )... If you have doublequotes in string then Replace it with \"
dtexec /F "C:\Package1.dtsx" /SET "\Package.Variables[User::companyname].Properties[Value]";"Babies\"R\"Us"

-- Execute file system package with logging , save log file to c:\log.txt
dtexec /F "C:\Package1.dtsx" /LOG "DTS.LogProviderTextFile;c:\log.txt"


Please visit the following URL to check all the options for DTExec
http://msdn.microsoft.com/en-us/library/ms162810.aspx

Executing SSIS package using DTSExecUI.exe  (interactive mode)

You can execute SSIS Package in interactive mode by right clicking on SSIS package and  launching Execute Package Utility. This untility allows you to pass parameters and set connection properties using GUI.

DTExecUI is usually located under the following path

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DtExecUI.exe

Execute Package from Windows Explorer



Set Variable Values

Executing SSIS package from T-SQL Code using xp_CmdShell

You can use xp_CmdShell stored procedure to call DTExec commandline application to execute SSIS package and pass commandline parameters as below.  For security reason by default xp_CmdShell is disabled when you install SQL Server but you can enable it using Surface Area Configuration tool or simply execute the following command in Query window. For more info visit http://msdn.microsoft.com/en-us/library/ms190693.aspx

Enable xp_Cmdshell

-- To allow advanced options to be changed.
EXEC Sp_configure   
  'show advanced options' ,1
GO
-- To update the currently configured value for advanced options.   
RECONFIGURE
GO
-- To enable the feature. Pass 1 to enable and 0 to disable xp_CmdShell.   
EXEC Sp_configure    'xp_cmdshell' ,1
GO
-- To update the currently configured value for this feature.   
RECONFIGURE
GO

Example: Executing SSIS Package using T-SQL Code, Pass multiple variables values.

DECLARE  @cmd VARCHAR   (1000)
DECLARE  @ssispath VARCHAR   (1000)
DECLARE  @fileName VARCHAR   (1000)
DECLARE  @maxrows VARCHAR (10)

SET @ssispath = 'C:\Package1.dtsx'
SET @fileName = '\\SRV1\CUST001.txt'
SET @maxrows = '5000'
 
SELECT @cmd = 'dtexec /F "' + @ssispath + '"'
SELECT @cmd = @cmd + ' /SET \Package.Variables[User::fileName].Properties[Value];"' + @fileName + '"'
SELECT @cmd = @cmd + ' /SET \Package.Variables[User::maxrows].Properties[Value];"' + @maxrows + '"'
EXEC MASTER..xp_cmdshell    @cmd

// Sample Command Line will be as below ....

dtexec /F "C:\Package1.dtsx" /SET \Package.Variables[User::fileName].Properties[Value];"\\SRV1\CUST001.txt"  /SET \Package.Variables[User::maxrows].Properties[Value];"5000"

Executing SSIS package from .Net Application using SSIS API (VB.net or C#  (Non-interactive mode))

Example: VB.net - Execute package and pass parameters

Dim pkgLocation As String = "c:\package1.dtsx" 

Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package 
Dim app As Microsoft.SqlServer.Dts.Runtime.Application 
Dim pkgResults As Microsoft.SqlServer.Dts.Runtime.DTSExecResult
Dim vars As Microsoft.SqlServer.Dts.Runtime.Variables 

app = New Microsoft.SqlServer.Dts.Runtime.Application() 
pkg = app.LoadPackage(pkgLocation, Nothing) 



-- // OR //--
--Load Package from SQL Server
--pkg = app.LoadFromSqlServer(\package1, "(local)", "sa", "mypwd01", Nothing)

vars = pkg.Variables 
vars("fileName").Value = "\\SRV1\CUST001.txt"
vars("maxrows").Value = 5000 

pkgResults = pkg.Execute(Nothing, vars, Nothing, Nothing, Nothing) 

If pkgResults = DTSExecResult.Success Then 
    Console.WriteLine("Package ran successfully") 
Else 
    Console.WriteLine("Package failed") 
End If 

 Example: C# - Execute package and pass parameters

string pkgLocation = "c:\\package1.dtsx"; 

Microsoft.SqlServer.Dts.Runtime.Package pkg = default(Microsoft.SqlServer.Dts.Runtime.Package); 
Microsoft.SqlServer.Dts.Runtime.Application app = default(Microsoft.SqlServer.Dts.Runtime.Application); 
Microsoft.SqlServer.Dts.Runtime.DTSExecResult pkgResults = default(Microsoft.SqlServer.Dts.Runtime.DTSExecResult); 
Microsoft.SqlServer.Dts.Runtime.Variables vars = default(Microsoft.SqlServer.Dts.Runtime.Variables); 

app = new Microsoft.SqlServer.Dts.Runtime.Application(); 
pkg = app.LoadPackage(pkgLocation, null); 

-- // OR //--
--Load Package from SQL Server
--pkg = app.LoadFromSqlServer(\\package1, "(local)", "sa", "mypwd01", Nothing);

vars = pkg.Variables; 
vars("fileName").Value = "\\\\SRV1\\CUST001.txt"; 
vars("maxrows").Value = 5000; 

pkgResults = pkg.Execute(null, vars, null, null, null); 

if (pkgResults == DTSExecResult.Success) { 
    Console.WriteLine("Package ran successfully"); 
} 
else { 
    Console.WriteLine("Package failed"); 
}

Executing SSIS package from BIDS (32Bit or 64Bit Mode)

This is the most common method of executing SSIS package during development phase. You can execute package in BIDS couple of ways

Executing Entire Package

1. Select Package in Solution Explorer and Right click and "Execute"
2. Hit F5 to execute package

Executing only single task
1. Right click on any Task and click "Execute"

Running package in 32Bit Mode (Only applicable for 64Bit OS)
When you testing your packages on 64Bit machine inside BIDS then by default package is running using 64Bit version of DTExec commandline application. But if you wish to run package using 32 Bit version of DTExec then change the "Run64BitRuntime" to False as below. This setting is found under SSIS Project Property Dialogbox.

Executing SSIS package from SQL Server Management Studio

In the management studio click on connect and select Integration Services.  Enter server name and connect. Expand "Stored Packages Folder" and you can right click and run any selected package.

Executing SSIS package on Remote Machine

IWhen you execute SSIS package without SQL Agent Job then package is loaded into local machine memory and all local resources are used to execute package... including Memory, Credentials to connect to datasources, Drivers etc. If you want to launch your SSIS package on remote machine then you can impement one of the following approaches.

1. Call Package via SQL Agent Job Programatically.
2. Call Package via Web Services Programatically.

Please read the following MSDN Article for more information

http://msdn.microsoft.com/en-us/library/ms403355.aspx




I hope you like this article. Plese feel free to leave your feedback at the bottom comment section.

Cheers!!!!