How can I call SSIS programmatically from .NET?

Question

I have an application where whenever a file is loaded into a directory, I have to call SSIS to parse the XML file.

Is it possible to call SSIS directly from the .NET Windows service?

Answer 1

SSIS software package .

I prefer the second method:

Run the DTEXEC.EXE process. DTEXEC is a command line utility for executing SSIS packages. See here command line options: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

Advantages: running the package from reliable process performance. It can be used from any programming language (including .NET 1.1 :)). Easily pass parameters by setting values ​​for variables.

Disadvantages: also only local. It’s harder to get package progress information (but registering SSIS can give you most of the functionality). Some overhead when starting a new process (probably minimal compared to the runtime for large packages).

ASP.NET specification: Win32 CreateProcess function ignores thread impersonation. Therefore, if you want DTEXEC to run under an account other than the ASP.NET process account, you must either enter a username / password, either pass it to Process.Start, or use the method described in the following KB to start child process under the impersonated account http://support.microsoft.com/kb/889251 .

Answer 2

you can run your SSIS package programmatically, as shown below:

using System; using Microsoft.SqlServer.Dts.Runtime.Wrapper; namespace ConsoleApplicationSSIS { class Program { static void Main(string[] args) { Console.WriteLine("Loading SSIS Service..."); //Application object allows load your SSIS package Application app = new Application(); //In order to retrieve the status (success or failure) after running SSIS Package DTSExecResult result ; //Specify the location of SSIS package - dtsx file string SSISPackagePath = @"C:\Microsofts\BI\SSIS\ConsoleApplicationSSIS\IntegrationServiceScriptTask\Package.dtsx"; //Load your package Package pckg = (Package)app.LoadPackage(SSISPackagePath,true,null); //Execute the package and retrieve result result = pckg.Execute(); //Print the status success or failure of your package Console.WriteLine("{0}", result.ToString()); Console.ReadLine(); } } } 

if you want a full sample, go to: http://hassanboutougha.wordpress.com/2012/10/13/run-your-ssis-package-progammatically/

I will explain how to create a simple SSIS package even after it is called programmatically from a console application. Do not forget to make this assembly: C: \ Program Files (x86) \ Microsoft SQL Server \ 100 \ SDK \ Assemblies \ Microsoft.SQLServer.DTSRuntimeWrap.dll for reference to the sys runtime namespace

you can also programmatically pass your variables and also change the source and target connections of your ssis package

Answer 3

You can program SSIS programmatically, execute the package, and change the configuration from the .NET code using the DTS runtime. Here is the complete code of how you can do this .

Answer 4

You can call the SSIS package from a Windows service. But Microsoft.SqlServer.Dts must be installed on the system where Windows services will run. If you installed the DTS installed on this computer, call the SSIS package directly. If it is not installed, you must do the following.

• Create SSIS Package
• Create a job that runs the SSIS package
• In your ADO.NET [located in the code of the Windows services] Call Call procedure that performs the task [configured to run the SSIS package]. Below is an example from your .NET code.

EXEC msdb.dbo.sp_start_job N'YourJobName '

Hope this helps!

Answer 5

Updating this rather old question:

In SQL Server 2012, you can do this by simply creating a stored procedure that will call create_execution and set_execution_parameter

A step-by-step guide can be found here: https://blogs.msdn.microsoft.com/biblog/2013/05/07/step-by-step-of-executing-ssis-2012-package-through-stored-procedure/