Refering dlls in SSIS Script Task

Introduction

Sql server 2012 is offering a support to add your favorite language in SSIS script task and play your business logic. This article reflects how we can refer your own class library to a script task.

Overview

Let’s start with Script task. Script task is a way of extending SSIS functionality when none of the built in components satisfy your needs. You can play your own custom code and integrate with built in components.

Most of the peoples do variable manipulation in Script task and some perform business task as well. Sometimes we encounter a situation like we have to map a data set variable to a Custom class and refer the same in another script task. The better solution is to create your own library with the class definition and refer it in your solution.

Below you can find the steps to add the reference and how I used it.

Create your own SSIS helper class library

1) First we need to create a helper project that is going to be referred in ssis package.   Open visual studio 2013 and select class library.

2) Add a new class  StudentDetails.cs 

add the below block of code in to the the class.

public class StudentDetails
{
      public int Id { get; set; }
      public string Name { get; set; }
}

3)  Strong name the assembly

Before adding this dll as the reference we need to strong name the assembly.  For doing this open “Visual Studio Command Prompt (2010)” , browse our class library project folder and execute the following command to create the key.

sn.exe -k PublicPrivateKeyFile.snk

4) Add the generated key file to the project and metioned it in the Assemblyinfo file.

To add the snk to the project file, right click the project file and select add and existing item click ok add the file to the solution.

SSISHelper->Right Click ->Add-> Existing item -> select the snk file. 

Edit the AssemblyInfo.cs and specify the key file.

[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("PublicPrivateKeyFile.snk")]

5) Add project dll to GAC

As SSIS is refering only GAC dlls, build the solution in release mode and add the dll to GAC. Follow the command to add perform adding.

gacutil -i C:\Users\rmadamba\Desktop\SSIS\SSISHelper.dll

Now u have added the dll to GAC and you can refer the same in your SSIS script task. Next is create an SSIS package and add script task to refer the dll.
 

6) Create a SSIS project in your visual studio.

Here we are creating SSIS package in which we will generate students with an Id and Name. For this create a package variable of Object type to hold the student details (In this case this will be an Array of Students) and Script task to add the student details to the variable collection.

Next we will loop through all the Students one by one and add this to a destination table. To add the student details one by one inside the foreach  container we need to have a Script task which will cast the current Student detail to separate variables.

Adding variable to the package

Add new StudentDetails variable to the package.

This will be used to store the collection of student in script task.

Adding script task

Drag and drop a script task to the place holder and open it for editing the script. Add the SSISHelper dll reference to the project. 

Specify the variable in ReadWriteVariables will allow the script to read and modify the values.

Next click on the Edit Script to open the script window. Add the SSISHelper dll to the reference.

Add the following code to your script.

public void Main()
{
      ArrayList studentCollection = new ArrayList();
      SSISHelper.StudentDetails student;

     // Generate student id and name and add to the ArrayList
     for (int i = 1; i < 11; i++)
     {
          student = new SSISHelper.StudentDetails();
          student.Id = i;
          student.Name = "No name" + i;

          studentCollection.Add(student);
      }

      Dts.Variables["User::StudentDetails"].Value = studentCollection;

      Dts.TaskResult = (int)ScriptResults.Success;
 }

Adding ForEachLoopContainer

Add a foreach loop container to and connect as the next task after the script task. This will loop through each Student and insert one by one to the database.

Select the Enumeration type to Foreach From Variable Enumerator as the Variable we are looping is a IEnumerable type.

Create new variable to hold the student details while iterating and Map to to the variable in foreach.

Create a Script task inside the foreach loop container

Create another script task inside the foreach container . Here we will update each Student name and assign it to two another variable. This will be used as a parameter to the insert Stored procedure.

Set the variables to the Script task. Here we are creating two new variables to hold Student Id and Student name. this will be used as the parameter for the data insert.

After this set the Script task "script variables". Here the read only variables will be looping object "user::Student" and the readandwrite will be "user::Id", "user::Name".

Open the script for editing and refer the same SSISHelper dll and add the followind code snippet.

public void Main()
{
    SSISHelper.StudentDetails std = (SSISHelper.StudentDetails)Dts.Variables["User::Student"].Value;

       // Assign the id and name to the variables.
       Dts.Variables["User::Id"].Value = std.Id;
       Dts.Variables["User::Name"].Value = std.Name + "Updated";

       Dts.TaskResult = (int)ScriptResults.Success;
 }

Next we are going to store the result to Database. For this we are going to add an Execute Sql Task and specify the strored procedure which will accept "User::Id", "User::Name" as the parameter. This procedure will insert the parameter result into the destination database.

Specify the variables in parameter field.

After this setup database connection and mention the Stored procedure which accept the parameter. Running the SSIS package will give you the result of :

Points of Interest

The above mentioned examples are crafted only for explanations. In script task you can add your own buiness logic to execute. But make sure database operations from the sctipt task will reduce the performance rather using the ssis components.