Click here to Skip to main content
Click here to Skip to main content

SSIS Package Integration with C# .NET

By , 16 Jan 2008
Rate this:
Please Sign up or sign in to vote.

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:
  4. 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”

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Hi,
I have 6+ years of experience in Microsoft Technologies and have 2+ years of experience in .NET Technologies. Currenty I am woring in Hyderabad and involed in various domain projects.
 
If you have any doubts please reach me: rajaramanindia@gmail.com

Comments and Discussions

 
GeneralFaster extraction and loading in SSIS Pinmemberblackpower2k725-Apr-09 19:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 16 Jan 2008
Article Copyright 2008 by Rajaraman.net05
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid