How to execute an SSIS package from .NET?

Question

I have an SSIS package that I would ultimately also want to pass parameters to, these parameters will be obtained from a .NET application (VB or C #), so I was curious if anyone knows how to do this, or, even better, a site with helpful tips on how to do this.

So basically, I want to execute the SSIS package from .NET, passing in the parameters of the SSIS package that it can use in it.

For example, SSIS will use the import of flat files into an SQL database, but the path and file name may be a parameter that is passed from the .Net application.

Answer 1

Here's how to set variables in a package from code -

using Microsoft.SqlServer.Dts.Runtime; private void Execute_Package() { string pkgLocation = @"c:\test.dtsx"; Package pkg; Application app; DTSExecResult pkgResults; Variables vars; app = new Application(); pkg = app.LoadPackage(pkgLocation, null); vars = pkg.Variables; vars["A_Variable"].Value = "Some value"; pkgResults = pkg.Execute(null, vars, null, null, null); if (pkgResults == DTSExecResult.Success) Console.WriteLine("Package ran successfully"); else Console.WriteLine("Package failed"); } 

Answer 2

Here's how to do it with the SSDB directory that was introduced with SQL Server 2012 ...

 using System.Collections.Generic; using System.Collections.ObjectModel; using System.Data.SqlClient; using Microsoft.SqlServer.Management.IntegrationServices; public List<string> ExecutePackage(string folder, string project, string package) { // Connection to the database server where the packages are located SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;"); // SSIS server object with connection IntegrationServices ssisServer = new IntegrationServices(ssisConnection); // The reference to the package which you want to execute PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders[folder].Projects[project].Packages[package]; // Add a parameter collection for 'system' parameters (ObjectType = 50), package parameters (ObjectType = 30) and project parameters (ObjectType = 20) Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>(); // Add execution parameter (value) to override the default asynchronized execution. If you leave this out the package is executed asynchronized executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 }); // Add execution parameter (value) to override the default logging level (0=None, 1=Basic, 2=Performance, 3=Verbose) executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 3 }); // Add a project parameter (value) to fill a project parameter executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 20, ParameterName = "MyProjectParameter", ParameterValue = "some value" }); // Add a project package (value) to fill a package parameter executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "MyPackageParameter", ParameterValue = "some value" }); // Get the identifier of the execution to get the log long executionIdentifier = ssisPackage.Execute(false, null, executionParameter); // Loop through the log and do something with it like adding to a list var messages = new List<string>(); foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages) { messages.Add(message.MessageType + ": " + message.Message); } return messages; } 

The code is a small adaptation of http://social.technet.microsoft.com/wiki/contents/articles/21978.execute-ssis-2012-package-with-parameters-via-net.aspx?CommentPosted=true#commentmessage

There is also a similar article at http://domwritescode.com/2014/05/15/project-deployment-model-changes/

Answer 3

To add to @Craig Schwarze's answer,

Here are some related MSDN links:

Download and run the local package programmatically:

Download and run a remote package programmatically

Capturing events from a running package:

 using System; using Microsoft.SqlServer.Dts.Runtime; namespace RunFromClientAppWithEventsCS { class MyEventListener : DefaultEvents { public override bool OnError(DtsObject source, int errorCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError) { // Add application-specific diagnostics here. Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description); return false; } } class Program { static void Main(string[] args) { string pkgLocation; Package pkg; Application app; DTSExecResult pkgResults; MyEventListener eventListener = new MyEventListener(); pkgLocation = @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" + @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"; app = new Application(); pkg = app.LoadPackage(pkgLocation, eventListener); pkgResults = pkg.Execute(null, null, eventListener, null, null); Console.WriteLine(pkgResults.ToString()); Console.ReadKey(); } } } 

Answer 4

So there is another way that you can run it from any language. I think it is best to create a batch file that will call your .dtsx package.

Next, you call the batch file from any language. As on the Windows platform, you can run the batch file from anywhere, I think this will be the most general approach for your purpose. No code dependency.

Below is a blog for more details ..

https://www.mssqltips.com/sqlservertutorial/218/command-line-tool-to-execute-ssis-packages/

Good coding .. :)

Thanks ayan