Problem

You have a SSIS package and a .NET application allows your end-users whenever they determine to transfer data from server A to server B in your system.

Solution

One of approaches, Iwill use .NET framework to develop our application to call a SSIS package system file(.dtsx) . In this article, I’m developing .NET application in Visual Studio 2015 and call a SSIS package which is developed in SQL Server Data Tool 2013 (SSDT).

The simple example is to load a text file into database. The format of text file likes below table. Column Delimiter is Tab charactre (\t), Row Delimiter is CRLF and CodePage is Unicode UTF-8

TransactionID ProductCode ProductName Quantity Price TransactionDate
000000001 P00001 Bicycle 1 1000 2015-01-01

Step 1 : Create a SSIS package to transfer data from a txt file to a table in SQL Server 2014.

1.1 Create a staging table stgSales

create table stgSales
(
TransactionID nvarchar(50),
ProductCode nvarchar(50),
ProductName nvarchar(100),
Quantity integer, 
Price numeric,
TransactionDate date,
CreatedDate datetime default getdate()
)

1.2 Create SSISDemo.dtsx package

Control Flow of the package
Control Flow of the package

 

Data Flow of the package
Data Flow of the package

Step 2: develop a Window Form application. This application allows an end-user to click on button and then it calls the package system file(.dtsx)

2.1 Create a Window Form project in Visual Studio 2015

2.2 Drag and drop Button and double click on it. I will develop the Click event of this button to call .dtsx file

CallSSISPackage project
CallSSISPackage project

 

2.3 Right click on References , then click Add Reference and add a reference to  Microsoft.SqlServer.ManagedDTS.dll. Click OK. This dll helps us create and execute a SSIS package (.dtsx file). It was installed when you installed Visual Studio, SQL Server or SQL Server Data Tool by default. If you can not see it, browse to the folder you installed SQL Server at C:\Program Files\Microsoft SQL Server\120\DTS\Binn\Microsoft DTS Runtime dll

However, depends on which SQL Server version was installed. The location of this dll is different on your side.

2.4 Develop the C# code to call package SSISDemo.dtsx, it is located at D:\Projects\SSIS_Projects\SSISDemo\SSISDemo

using System;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
namespace CallSSISPackage
{
 public partial class CallSSISPackage : Form
 {
 private string pkSSISDemo = "D:\\Projects\\SSIS_Projects\\SSISDemo\\SSISDemo\\SSISDemo.dtsx";
 public CallSSISPackage()
 {
 InitializeComponent();
 }
private void btCallSSISPackage_Click(object sender, EventArgs e)
 {
 string error = "";
 lbResultSSISExecute.Text = "The package is executing...";
 Package pkg=null;
 Microsoft.SqlServer.Dts.Runtime.Application app;
 DTSExecResult result;
 try
 {
 app = new Microsoft.SqlServer.Dts.Runtime.Application();
 pkg = app.LoadPackage(pkSSISDemo, null);
 result = pkg.Execute();
if(result==Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
 {
 foreach(Microsoft.SqlServer.Dts.Runtime.DtsError dt_error in pkg.Errors)
 {
 error += dt_error.Description.ToString();
 }
 lbResultSSISExecute.Text = error;
 }
 if (result == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
 {
 lbResultSSISExecute.Text = "The package executed successfully";
 }
 }
 catch(Exception ex)
 {
 lbResultSSISExecute.Text = ex.Message;
 }
}
 }
}

2.5 Open App.config file and add more configurations

<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy=”true”>
<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.5.2″ />
</startup>
</configuration>

I must add this because I’m developing the application in .NETFramework 4.5.2. It needs this configuration to execute the .dtsx file which is developed in SSDT 2013 (under 4.5.2 .NETFramework)

2.6 Run the application

RunApplication

2.7 Query data in the database

FinalResult

Advertisements

3 thoughts on “How to call a SSIS package from .NET application

  1. Something is broken in this code. It is giving an error in compilation:
    Error 1 ‘CallSSISPackage’: member names cannot be the same as their enclosing type
    Error 2: lbResultSSISExecute.Text doesn’t exist in this context.

    Would u post a working downloadable version of the code to follow.

    Thanks,
    Brian

    1. Hi Brian,
      Sorry for responding late. Could you send me your email that I can send the source code back to you?

      Many thanks for reading my blog and I appreciate your feedback.

      Best regards,
      Dung Dinh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s