SSIS Package Integrated with C#.NET

SSIS Packages

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 load (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 the 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 the example for creating package variables. The steps involved in constructing and executing a package using 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 the dialog box as below

As Figure shows, 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 ToolBox and select Script Task. Drag and Drop that task on to the Control WorkArea. So, your executable is ScriptTask. Right click on the window and select “Variables” from the popup Menu as shown below:-

Click on Add Variables tab

Add two variables named FirstVa and SecondVar as shown below
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 in SQL Design Script?

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

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

Let’s see now how to load the package

We can load the package using two ways - From local folder - From SQL Server folder

Code to load the package from 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 Package from 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 list and maintain 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” 

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

(Refresh)

-- There are no messages in this forum --