Running SSIS packages from C#

re: Running SSIS packages from C#

ashish — Tue, 10 Apr 2012 05:03:43 GMT

Do we have option to run it asynchronously?..I mean do we have something like BeginExecute()?

re: Running SSIS packages from C#

Piotr Rodak — Tue, 10 Apr 2012 07:17:14 GMT

Sure you can. Since the service runs as singleton, you can have whole asynchronous execution framework implemented. You just enqueue execution of the package into some sort of a queue and return.

re: Running SSIS packages from C#

Sid — Wed, 12 Sep 2012 12:55:50 GMT

This looks fantastic and I'm trying your sample but I've hit a problem and I don't readily see the solution. I have the SSIS package working and it runs and creates a file if I run it through in Visual Studio but I can't get it to work from the service. I'm getting this erorr from the console:

Calling package ExportEmployees.dtsx with parameter 12.

Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load

due to error 0xC0011008 "Error loading from XML. No further detailed error infor

mation can be specified for this problem because no Events object was passed whe

re detailed error information can be stored.". This occurs when CPackage::LoadFr

omXML fails.

---> System.Runtime.InteropServices.COMException: The package failed to load du

e to error 0xC0011008 "Error loading from XML. No further detailed error informa

tion can be specified for this problem because no Events object was passed where

detailed error information can be stored.". This occurs when CPackage::LoadFrom

XML fails.

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadPackage(Strin

g FileName, Boolean loadNeutral, IDTSEvents90 pEvents)

at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, I

DTSEvents events, Boolean loadNeutral)

--- End of inner exception stack trace ---

at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, I

DTSEvents events, Boolean loadNeutral)

at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, I

DTSEvents events)

at SSISRunner.PackRunner.RunPackage2(String name, Int32 rows) in L:\ZipStuff\

ServiceETLDemo\SSISRunner\SSISRunner\Program.cs:line 118

http://localhost:8011/runpackwithparams?package=%22ExportEmployees.dtsx%22&rows=

I'm running everything locally on my workstation on which I'm an admin.

I've probably missed something relatively simple but could you please help.

Any help is greatly appreciated.

re: Running SSIS packages from C#

Sid — Wed, 12 Sep 2012 15:06:18 GMT

Ok -- I got it -- the console app references a 2005 library:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

and I'm using a 2008 SSIS package so just switch the reference to the same dll in the \100 subdirectory and it works as expected.

Sorry for the confusion,

This article clarified it for me:

http://sqlaj.wordpress.com/2010/01/12/t-sql-tuesday-002-a-puzzling-situation-the-package-failed-to-load-due-to-error-0xc0011008/

thanks again for this great article!!!

My head is already spinning with ideas on how to apply this technology.