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 “http://bigator.com/2012/02/17/the-task-scr-get-task-start-time-cannot-run-on-this-edition-of-integration-services-it-requires-a-higher-level-edition-ssis-2005-dtexec-execution-result/”

“Tip:
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. http://msdn.microsoft.com/en-us/library/ms403355.aspx

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.

Xp_CmdShell:
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
GO
RECONFIGURE
GO
EXEC Sp_configure 'Xp_CmdShell’, 1
GO
RECONFIGURE
GO

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:
Microsoft.SqlServer.Dts.Runtime.Package
Microsoft.SqlServer.Dts.Runtime.Application
Microsoft.SqlServer.Dts.Runtime.DTSExecResult
Microsoft.SqlServer.Dts.Runtime.Variables

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");}
else
{MessageBox.Show("Package execution: Failed");}

I hope this may help you somewhere.
Thanks, Khilit