Execute SSIS package from SSIS Script/ C#

Execute SSIS package from C# / SSIS script with configuration file and/or preset variable value and get all types of messages/events from that package like information, error, warning etc.

For this execution you have to add below references and use below packages…

  Ø  References (Assembly location “C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\”)

ü  Microsoft.SqlServer.ScriptTask

ü  Microsoft.SqlServer.ManagedDTS

  Ø  Packages

ü  Microsoft.SqlServer.Dts.Runtime

ü  Microsoft.SqlServer.Dts.Tasks.ScriptTask

First of all you have to load a package by using Application object and then set configuration file and/or variables value by using Package. Then add a listener to get messages/events from loaded package.

Example declaration and calling … Download code only (CallingCode.c#)

run.Package pkg;

run.Application app;

pkg = app.LoadPackage(pkgPath + @"\TestCall.dtsx", null);

run.Variables pkgVars = pkg.Variables;

pkgResults = pkg.Execute(null, pkgVars, eventListener, null, null);

Download ExecuteSSISPkgC#.zip for example. This zip contains following three (3) files...

File Name	Purpose	Comment
CallingPkg.dtsx	This package will call the target TestCall.dtsx package	Add congif file (XML), set variables, retrieve event messages to/from TestCall.dtsx
TestCall.dtsx	This package will be executed by the package CallingPkg.dtsx	Will be executed
TestConfig.dtsConfig	Use to set variable “ValueOfConfig”	Used by “TestCall.dtsx”. set by “CallingPkg.dtsx”

see also -- SQL script to check duplicate record existence of a table/SQL statement of SQL Server