CODEWITHSUNDEEP
c#.netsql-serverssis
Anonymous Cow
Anonymous Cow

Reputation:

How can I call SSIS programmatically from .NET?

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

Can I call a SSIS directly from a .NET Windows service?

Upvotes: 4

Views: 10196

Answers (5)

Yasskier
Yasskier

Reputation: 811

Updating this pretty old question:

On SQL Server 2012 you can do this simply by creating stored procedure that will call to create_execution and set_execution_parameter

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/

enter image description here

Upvotes: 0

Gowdhaman008
Gowdhaman008

Reputation: 1323

You can call the SSIS package from your windows service. But Microsoft.SqlServer.Dts should be installed into the system where windows services are going to run. If you have installed DTS installed in that machine, directly call the SSIS package. If it is not installed then you should do the following.

  1. Create the SSIS package
  2. Create the job which runs the SSIS package
  3. In your ADO.NET[resides in windows services code], Call stored procedure which runs job[configured to run the SSIS package]. Following is an example should be called from your .NET code.

EXEC msdb.dbo.sp_start_job N'YourJobName'

Hope this helps!

Upvotes: 0

Dost MK
Dost MK

Reputation: 11

You can call SSIS programtically, execute the package and change the configuration from a .NET code using DTS runtime. Here is complete code of how you can do it.

Upvotes: 0

Hassan Boutougha
Hassan Boutougha

Reputation: 3929

you can run your SSIS package programmatically, as follow:

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 complete sample, go to :http://hassanboutougha.wordpress.com/2012/10/13/run-your-ssis-package-progammatically/

I explain how create a simple SSIS package and after how to call it programmatically from a console application. Don't forget to have this assembly :C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll to reference runtime ssis namespace

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

Upvotes: 3

Gulzar Nazim
Gulzar Nazim

Reputation: 52178

Running SSIS package programmatically.

I prefer the second method:

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

Benefits: running package out of process gains reliability. Can be used from any programming language (including .NET 1.1 :)). Easy to pass parameters by setting variables values.

Drawbacks: Also local only. Harder to get information about package progress (but SSIS logging can give you most functionality). Some overhead on starting new process (likely minimal compared to execution time for big packages).

ASP.NET specific: Win32 CreateProcess function ignores the thread impersonation. So if you want DTEXEC to run under account different from ASP.NET process account, you should either make user enter name/password and pass it to Process.Start, or use method described in the following KB to run child process under impersonated account http://support.microsoft.com/kb/889251.

Upvotes: 10

Related Questions