Ways to execute SSIS package

Hi All,

There are lots of methods documented and available on internet. But if you think about the execution of anything you may find more options.

Particularly for SSIS packages, there is a golden rule to be bear in mind when planning for executing ssis packages, i.e. you will need SSIS RunTime.

Following is the excerpt from my previous post “

DTEXEC utility is a client tool for executing SSIS package But SSIS execution is carried by “Integration Services Runtime” and not by “Integration Services Service”. If you track-back to basics of SSIS, you will see that “Integration Services Runtime provides core functionality for executing the package including logging, configuration, debugging etc.

So in short, if you wanted to execute SSIS package using DTEXEC or DTEXECUI then you have to install the server components for SSIS Service on your machine where you want to run the package. Remember, SSIS package you can create even in Notepad (provided you are well-versed with XML/XSL/XSLT etc.) but execution will be done only by “Integration Services Runtime” (if outside the BIDS) or using BIDS (because BIDS has “Integration Services Object Model” which interacts with “SSIS Runtime” for executing packages).

Execution of any SSIS package is categorized in 2 modes: Interactive & non-interactive. An interactive mode is the method of execution where SSIS package needs inputs from user or user may want to change inputs for SSIS package like configuration values etc. Non-interactive mode does not wait for user to provide inputs as it’s pre-configured with default values. You can choose any method you may wish which suits your requirements.

Note: This categorization is NOT part of any official Microsoft documentation. This is my own views & analysis.

Following are quick ways you can use and implement according to your needs:
•    BIDS (Business Intelligence Development Studio)
•    SSMS (SQL Server Management Studio)
•    Xp_CmdShell (Command-line Utility)
•    DTEXEC.EXE (Command-line Utility)
•    DTEXECUI.EXE (Command-line Utility with user interface)
•    SQL Agent Job
•    SSIS APIs in .NET Application

Note: For executing SSIS package which is on Remote Machine, please refer to trusted link for more details.

Important Tip: Always remember, no matter what options you choose from above list, but underneath DTEXEC.EXE command-line utility will be called by SQL Server Integration Services RunTime in order to execute SSIS package. The default path of DTEXEC.EXE is “C:Program FilesMicrosoft SQL Server110DTSBinn“. This path may be different for you if you are using different version of SQL Server as well as different method of installation.

DTEXEC.EXE (32 Bit & 64 Bit):
If you are running SSIS on 64 bit machines, then you will have 2 versions of DTEXEC.EXE utility. And by default 64 Bit versioned DTEXEC.EXE is called. So when you specifically need to execute using 32 bit versioned DTEXEC.EXE then you have to specify in command-line.

I am not going cover each of them these options in much details in this article but I would rather post separate tips on them later.

BIDS (Business Intelligence Development Studio):
It’s always considered a best practice to create a solution workflow by creation Master-Package and multiple Child-Packages for any ETL solution. This way maintenance and modification would be easier to incorporate as well as whole solution would be easy to understand.

To execute a selected package in a project, right-click on package and mark it as “Set as Startup Object”.

Make sure to change a debug property for running the package in 64 bit or 32 bit.

SSMS (SQL Server Management Studio):
Once you deploy your SSIS packages to integration services database. You can connect to Integration services. Right click and run the package. In this option, actual execution is carried by DTEXECUI.EXE and integration services database is merely a storage place for packages.

You can also use T-SQL on SQL Server and execute SSIS packages. Like following. I am going to use Xp_CmdShell to execute package.

Note: Make sure if Xp_CmdShell is enabled for your SQL Server.

Enable Xp_CmdShell
EXEC Sp_configure 'show advanced options', 1
EXEC Sp_configure 'Xp_CmdShell’, 1

Simply use following code to execute the package:

DTEXEC.EXE (Command-line Utility):
Start command prompt with CMD. (Mind well with what credentials you are launching CMD because that user will be executing SSIS package.)

You can use any of the following command-lines which suit your needs. (Be sure to modify appropriately)

Executing package from your local file system
DTEXEC.EXE /F "C:PowerBiPackage.dtsx" /Config "C:PackagesPowerBiConfiguration.dtsconfig"

Executing package stored on SQL Server with SQL authentication
DTEXEC.EXE /SQL "PowerBiPackage" /SERVER PowerBiServer /USER PowerBiAdministrator /PASSWORD PowerBiPassword

Executing package from your local file system with parameter value
DTEXEC.EXE /F "C:PowerBiPackage.dtsx" /SET "Package.Variables[User::FirstValue].Properties[Value]";"5000"

Executing package from your local file system with multiple parameter values
DTEXEC.EXE /F "C:PowerBiPackage.dtsx"
/SET "Package.Variables[User::FirstValue].Properties[Value]";"5000"
/SET "Package.Variables[User::SecondValue].Properties[Value]";"7000"

Executing an encrypted package from your local file system
DTEXEC.EXE /F "C:PowerBiPackage.dtsx" /DECRYPT PowerBiPassword

Execute package from your local file system with Logging
DTEXEC.EXE /F "C:PowerBiPackage.dtsx" /LOG "DTS.LogProviderTextFile;C:PowerBiPackage_Executionlog.txt"

More options can be found at MSDN.

DTEXECUI.EXE (Command-line Utility with user interface):
This option provides user interface for executing SSIS package but uses DTEXEC.EXE for executing package.

Select your package & execute it.

SQL Agent Job:
You can create a new job as shown below and explore the dialog box. I will put this in separate post so I can explain the full configuration.

SSIS APIs in .NET Application:
The people who wanted to execute SSIS packages in their applications or during the execution of their applications; can use SSIS APIs and call SSIS packages for execution.

You will need following namespaces to include in your program:

Create a new application thread:
Microsoft.SqlServer.Dts.Runtime.Package myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();

Create a new package & load package from file system:
Microsoft.SqlServer.Dts.Runtime.Package myPackage = myApplication.LoadPackage("C:PackagesPowerBi_MasterPackage.dtsx", null);

/*create a new package & load package from SQL Server:
Microsoft.SqlServer.Dts.Runtime.Package myPackage =
myApplication.LoadFromSqlServer(\PowerBi_MasterPackage, "(local)", "PowerBiAdministrator", "PowerBiPassword", Nothing);

Instantiate package execution results:
Microsoft.SqlServer.Dts.Runtime.DTSExecResult myPackageResults = Default(Microsoft.SqlServer.Dts.Runtime.DTSExecResult);

Instantiate package variables (if needed):
Microsoft.SqlServer.Dts.Runtime.Variables myPackageVariables = myPackage.Variables;

Set values for required variables:
myPackageVariables("FirstValue").Value  = "5000";
myPackageVariables("SecondValue").Value = "7000";

Execute a package:
myPackageResults = myPackage.Execute(NULL, myPackageVariables, NULL, NULL, NULL);

Check package execution results:
If (myPackageResults == DTSExecResult.Success)
{MessageBox.Show("Package execution: Success");}
{MessageBox.Show("Package execution: Failed");}

I hope this may help you somewhere.
Thanks, Khilit

bigatorMarch 11, 2012 · bigatorKhilitchandra Prajapati · bigator14 Comments
bigatorTags: , , ,  · Posted in: DTEXEC, Interview QA, SSIS, Tutorial, XP_CmdShell

14 Responses

  1. Big bob - March 11, 2012

    Very nice post and indispensable,
    Thanks, bob

  2. raf - April 1, 2012

    Excellent work

  3. Nootropics - April 15, 2012

    Greeting from over the ocean. excellent post I shall return for more.

  4. gumboots - April 27, 2012

    You have observed very interesting points! ps nice site.

  5. Annette Sington - June 16, 2012

    Definitely, what a splendid site and instructive posts, I will bookmark your blog.All the Best!

  6. Patricia Dumoulin - June 17, 2012

    I am always invstigating online for posts that can aid me. Thank you!

  7. Sunshine Mackney - June 17, 2012

    I have been examinating out many of your stories and i can state pretty nice stuff. I will make sure to bookmark your blog.

  8. Veerman - June 20, 2012

    Nice work. Keep it up. Thank you. Veerz

  9. Kotresha A M S - October 20, 2012

    I tried to refer configfile using belwo syntax but it did not work.
    DTEXEC.EXE /F “C:PowerBiPackage.dtsx” /Config “C:PackagesPowerBiConfiguration.dtsconfig”

    After lot of research, i came across work around to refer to SSIS configuration file by following below steps.

    1. open your package file(.dtsx) with notepad.
    2. Naviage to section.
    3. Set physical path of your Config file in below shown property.

  10. Kotresha A M S - October 20, 2012

    Sorry,While I posted above comments, it’s stripping XML content. 🙂

  11. Kotresha A M S - October 20, 2012

    I tried to refer configfile using belwo syntax but it did not work.
    DTEXEC.EXE /F “C:PowerBiPackage.dtsx” /Config “C:PackagesPowerBiConfiguration.dtsconfig”

    After lot of research, i came across work around to refer to SSIS configuration file by following below steps.

    1. open your package file(.dtsx) with notepad.
    2. Naviage to <DTS:Configuration> section.
    3. Set physical path of your Config file in below shown property.
    <DTS:Property DTS:Name=”ConfigurationString”&gtD:\SSISTest\myconfig.dtsConfig</DTS:Property>

  12. Kotresha A M S - October 20, 2012

    1. open your package file(.dtsx) with notepad.
    2. Naviage to section.
    3. Set physical path of your Config file in below shown property.
    <DTS:Property DTS:Name=”ConfigurationString”>D:\SSISTest\myconfig.dtsConfig

  13. Kerl Eichelkaser - October 29, 2012

    I am lucky that I discovered this blog , just the right info that I was searching for! .

  14. Joann Pendleton - February 4, 2014

    Do you have instructions on how to set up a remote computer on a network to execute an SSIS package. Do I have to install SSIS on the remote computer?

Leave a Reply

%d bloggers like this: