SQL Server Integration Services: Calling SSIS Package in C#

Introduction

During this series about SQL Server Integration Services, the basics of importing, exporting, manipulating data, typical database maintenance and deployment of SSIS packages have been covered. In this last article of this introductory series on SSIS, the technique of calling a SSIS package from C# will be covered.

We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

Scenario

As seen in the last article, a package was successfully deployed and ran in SQL Server Management Studio. But there may be times when a package needs to be run by a user initiated action. This could be from another application or web service that has access to the where the dtsx package is physically located. For this example, a simple console application will be used to show how to call a SSIS package from some C# code.

Implementation

Open up Visual Studio and create a C# Console Project. Once the project is loaded, the first thing to take place is to add the reference to the code that uses SSIS packages. In solution explorer, add a reference to the Micorsoft.SQLServer.Managed.DTS.dll. Then in the blank class file add a using statement to reference the dll. There are four variable that are needed to be able to run a dtsx package. One being a string variable where the physical location of the package located, hence pkgLocation. Next is the actual package object, ssisPackage, followed by the application running the package, ssisApp. Finally it would be nice to display the results of the package once it is executed so ssisPackageResults will be used.

After the variables are created all that is needed is to put it all in motion. First, set the physical file path of the package. Then create a new application object to handle the pacakge execution. From there, give the package object the application along with the location of the package Finally, capture the result of the executed package and display it to the screen.

Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.

Run the application and see it all work.

What have we learned?

How to add a reference to the Micosoft.SQLServer.Managed.DTS.dll
How to call a SSIS package from C# code
How to verify capture the results of a package being executed and display it to the screen

Attachments