SSIS Package Integration with C# .NET

Introduction

This article describes the process of creating SSIS packages with SQL Server 2005. SSIS stands for SQL Server 2005 Integration Services.

Significance of SSIS

It is a platform for building high performance data integration solutions, including extraction, transformation, and loading (ETL) packages for data warehousing. It ships with the features, tools, and functionality to build Enterprise-Class ETL-based applications. Similar to DTS (Data Transmission Services), it provides functions to move/copy data from one place to another and modify the data at runtime. SSIS fully supports the Microsoft .NET Framework, allowing developers to program SSIS in their choice of .NET-compliant languages, as well as native code.

Let’s discuss it with an example of creating package variables. The steps involved in constructing and executing a package uses the Business Intelligence Development Studio environment. Before creating the package, you first need to create a project that can host the package. Open Business Intelligence Development Studio from the Start->Programs menu. Select File->New Project from the menu, and you will see a dialog box. Select Integration Services Project as the project template, and specify the name of the project as SSISDemo. Once the project is created, you will see the package designer window of the default package Package.dtsx. Now, go to the ToolBox and select Script Task. Drag and drop that task on to the control work-area. Your executable is ScriptTask. Right click on the window and select “Variables” from the popup menu. Click on the Add Variables tab. Add two variables named FirstVa and SecondVar. Right click on the script task and click on Execute. Your package should successfully execute. This is how package variables are added.

How to Access these Variables from the SQL Design Script?

1. Right click ScriptTask and select Edit mode.
2. Click Design Script, then you can see the script editor.
3. Type the following code:
VB

Dim localVar=dts.Variables["FirstVar"].value.ToString()
MsgBox(localVar);

How to Load the Package

We can load the package using two ways:

• From the local folder.
• From the SQL Server folder.

Code to Load the Package from the Local Folder

using DTS = Microsoft.SqlServer.Dts.Runtime;
using System.Collections;
using System.Collection.Generic;

DTS.Package dtsPackage = null;
DTS.Application dtsApplication = null;

dtsApplication = new DTS.Application();
//Load package by specifying SSIS package file path
dtsPackage = dtsApplication.LoadPackage(@"c:\Package.dtsx", null);
DTS.Variables packageVariables1 = dtsPackage.Variables;
packageVariables1["FirstVar"].Value = "12345321";
packageVariables1["SecondVar"].Value = "2312";

DTS.DTSExecResult packageResult = dtsPackage.Execute();

The dtsPackage.Execute will return either success or failure messages.

Load the Package from the SQL Server Folder

string p = @"C:\Package.dtsx";

// Verify that the folder exists by using ExistsOnSqlServer method.
Boolean folderExists = app.FolderExistsOnSqlServer("myNewFolder1", ".", null, null);
Console.WriteLine("Folder exists? {0}", folderExists);

// Load a package and save it.
DtsPackage.Package pkg; // = app.LoadPackage(p, null);

//app.SaveToSqlServerAs(pkg, null, "newPkg", ".", null, null);
pkg=app.LoadFromSqlServer("newPkg", ".", String.Empty, String.Empty, null);

DtsPackage.Variables vars2 = pkg.Variables;
vars2["OpeId"].Value = "value from c#";
DtsPackage.DTSExecResult result2 = pkg.Execute();

// Verify that the package was saved.
Boolean packageExists = app.ExistsOnSqlServer("newPkg", ".", null, null);
Console.WriteLine("Package exists? {0}", packageExists);

//Remove the folder.
app.RemoveFolderFromSqlServer("myNewFolder1", ".", null, null);

// Verify that the folder was removed by using the ExistsOnSqlServer method.
folderExists = app.FolderExistsOnSqlServer("myNewFolder1", ".", null, null);
Console.WriteLine("Folder exists? {0}", folderExists);

To maintain the successfully executed data and failure data, store these values in separate array lists and maintain them for reports.

if (pkgExecResults == DTS.DTSExecResult.Success)
{
    //store the values.
}

If you get any error while executing the SSIS package, you need to register the MSXML DLL using:

Regsvr32 "C:\WINDOWS\system32\msxml3.dll"