How to execute an SSIS package from NET

Question

I have a SSIS package that eventually I would like to pass parameters too  these parameters will come from a  NET application  VB or C   so I was curious if anyone knows of how to do this  or better yet a website with helpful hints on how to do it    So basically I want to execute a SSIS package from  NET passing the SSIS package parameters that it can use within it     For instance  the SSIS package will use flat file importing into a SQL db however the Path and name of the file could be the parameter that is passed from the  Net application

User · Answer

Here is how to set variables in the 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

User · Answer

Here is how to set variables in the 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

User · Answer

To add to  Craig Schwarze answer   Here are some related MSDN links   Loading and Running a Local Package Programmatically   Loading and Running 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

User · Answer

Here s how do to it with the SSDB catalog 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 lt string gt  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 lt PackageInfo ExecutionValueParameterSet gt  executionParameter   new Collection lt PackageInfo ExecutionValueParameterSet gt             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 lt string gt         foreach  OperationMessage message in ssisServer Catalogs  SSISDB   Executions executionIdentifier  Messages                messages Add message MessageType          message Message              return messages      The code is a slight 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

User · Answer

You can use this Function if you have some variable in the SSIS       Package pkg       Microsoft SqlServer Dts Runtime Application app      DTSExecResult pkgResults      Variables vars       app   new Microsoft SqlServer Dts Runtime Application        pkg   app LoadPackage   Location of your SSIS package   null        vars   pkg Variables          your variables     vars  somevariable1   Value    yourvariable1       vars  somevariable2   Value    yourvariable2        pkgResults   pkg Execute null  vars  null  null  null        if  pkgResults    DTSExecResult Success                Console WriteLine  Package ran successfully              else                Console WriteLine  Package failed

User · Answer

So there is another way you can actually fire it from any language  The best way I think  you can just create a batch file which will call your  dtsx package   Next you call the batch file from any language  As in windows platform  you can run batch file from anywhere  I think this will be the most generic approach for your purpose  No code dependencies   Below is a blog for more details     https   www mssqltips com sqlservertutorial 218 command-line-tool-to-execute-ssis-packages   Happy coding       Thanks  Ayan

User · Answer

You can use this Function if you have some variable in the SSIS       Package pkg       Microsoft SqlServer Dts Runtime Application app      DTSExecResult pkgResults      Variables vars       app   new Microsoft SqlServer Dts Runtime Application        pkg   app LoadPackage   Location of your SSIS package   null        vars   pkg Variables          your variables     vars  somevariable1   Value    yourvariable1       vars  somevariable2   Value    yourvariable2        pkgResults   pkg Execute null  vars  null  null  null        if  pkgResults    DTSExecResult Success                Console WriteLine  Package ran successfully              else                Console WriteLine  Package failed

User · Answer

To add to  Craig Schwarze answer   Here are some related MSDN links   Loading and Running a Local Package Programmatically   Loading and Running 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

User · Answer

Here s how do to it with the SSDB catalog 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 lt string gt  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 lt PackageInfo ExecutionValueParameterSet gt  executionParameter   new Collection lt PackageInfo ExecutionValueParameterSet gt             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 lt string gt         foreach  OperationMessage message in ssisServer Catalogs  SSISDB   Executions executionIdentifier  Messages                messages Add message MessageType          message Message              return messages      The code is a slight 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

User · Answer

So there is another way you can actually fire it from any language  The best way I think  you can just create a batch file which will call your  dtsx package   Next you call the batch file from any language  As in windows platform  you can run batch file from anywhere  I think this will be the most generic approach for your purpose  No code dependencies   Below is a blog for more details     https   www mssqltips com sqlservertutorial 218 command-line-tool-to-execute-ssis-packages   Happy coding       Thanks  Ayan

[c#] How to execute an SSIS package from .NET?

Examples related to c#

Examples related to .net

Examples related to sql-server

Examples related to vb.net

Examples related to ssis