Create Custom Task in SSIS

Microsoft fitted SSIS with rich set of Tasks and Transformation that an ETL developer would need but sometime you have a requirment which you feel better done in some othere way so SSIS allows you to create Custom task and include it in SSIS.

Whenever any tricky situation comes my boss would say "Why dont you do this with Custom Task or Create package programtically" and I used to think if there is something that cant be done using existing tasks and transformation then perphaps the work doesnt worth doing with SSIS or I dont worth working on SSIS.

Well than one night( wonder why developers always have night rather than day) I thought lets do something programatically.

SSIS allows to have 5 types of custom objects

• Custom tasks.
• Custom connection managers
• Custom log providers
• Custom enumerators
• Custom data flow components

I will create a Custom Task in this post. Most common thing when I debug a SSIS pacakage is to know the value of a variable so I created a custom task to display value of a select variable in a message box ( can do it in script task but isnt it a pain to do such a simple thing there). So lets create a "Display Variable" task.

It is best practice to create two assemblies: one for UI and anothter for actual runtime processing code.

First lets start with UI of DisplayVariable

A1. Create a Class Library project

A2. Add reference to

1.     System.Drawing
2.     System.Windows.Forms
3.     Microsoft.DataTransformationServices.Controls -- This doesnt show up in .Net tab so you can browse to C:\Windows\Assembly to add
4.     Microsoft.SqlServer.Dts.Design

A3. DisplayVariableForm:
Add a Windows Form ( name as DisplayVariableForm) then add one comboBox (CmBxVariableList ) and a Button( butOK)

A4. Code for DisplayVariableFormUI.cs

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Runtime.Design;

namespace DisplayVariableUI

{

    public partial class DisplayVariableForm : Form

    {

        private TaskHost vTaskHost;

        private Connections vConnection;

        const string VARIABLE_NAME = "VariableName";

        const string VARIABLE_VALUE = "VariableValue";

     

        public DisplayVariableForm(TaskHost taskHost, Connections connections)

        {

            vTaskHost = taskHost;

            vConnection = connections;

            InitializeComponent();

        }

        //Fill Variable combo

        private void DisplayVariableForm_Load(object sender, EventArgs e)

        {

            CmBxVariableList.BeginUpdate();

            foreach (Variable var in vTaskHost.Variables)

            {

                CmBxVariableList.Items.Add(var.QualifiedName);

            }

            CmBxVariableList.EndUpdate();

        }

       

        private void butOK_Click(object sender, EventArgs e)

        {

            VariableName = CmBxVariableList.Text;

            VariableValue = vTaskHost.Variables[VariableName].Value.ToString();

            DialogResult = DialogResult.OK;

        }

        //get variable name

        private string VariableName

        {

            get

            {

                if (vTaskHost.Properties[VARIABLE_NAME].GetValue(vTaskHost) != null)

                {

                  return vTaskHost.Properties[VARIABLE_NAME].GetValue(vTaskHost).ToString();

                }

                return null;

            }

            set

            {

                vTaskHost.Properties[VARIABLE_NAME].SetValue(vTaskHost, value);

            }

        }

        //Get variable value

        private string VariableValue

        {

            get

            {

                if (vTaskHost.Properties[VARIABLE_VALUE].GetValue(vTaskHost) != null)

                {

                  return vTaskHost.Properties[VARIABLE_VALUE].GetValue(vTaskHost).ToString();

                }

                return null;

            }

            set

            {

                vTaskHost.Properties[VARIABLE_VALUE].SetValue(vTaskHost, value);

            }

        }

    }

}

A5.  DisplayVariableUI.cs:

To initialize and display the user interface associated with the task we will create a class DisplayVariableUI.cs which will be inherited from interface IDtsTaskUI. When the user interface for a task is invoked, the designer calls the Initialize method, implemented by the task user interface and then provides the TaskHost and Connections collections of the task and package, respectively, as parameters. These collections are stored locally, and used subsequently in the GetView method.

The designer calls the GetView method to request the window that is displayed in SSIS Designer. The task creates an instance of the window that contains the user interface for the task, and returns the user interface to the designer for display. Typically, the TaskHost and Connections objects are provided to the window through an overloaded constructor so they can be used to configure the task.

The SSIS Designer calls the GetView method of the task UI to display the user interface for the task. The task user interface returns the Windows form from this method, and SSIS Designer shows this form as a modal dialog box.

When the form is closed, SSIS Designer examines the value of the DialogResult property of the form to determine whether the task has been modified and if these modifications should be saved. If the value of the DialogResult property is OK, the SSIS Designer calls the persistence methods of the task to save the changes; otherwise, the changes are discarded.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Runtime.Design;

namespace DisplayVariableUI

{

    public class DisplayVariableUI : IDtsTaskUI

    {

        private TaskHost taskHost;

        private Connections connectionService;

        public void Initialize(TaskHost taskHost, IServiceProvider serviceProvider)

        {

            this.taskHost = taskHost;

            IDtsConnectionService cs = serviceProvider.GetService (typeof(IDtsConnectionService)) as IDtsConnectionService;

            this.connectionService = cs.GetConnections();

        }

        public ContainerControl GetView()

        {

            return new DisplayVariableForm(this.taskHost, this.connectionService);

        }

        public void Delete(IWin32Window parentWindow)

        {

        }

        public void New(IWin32Window parentWindow)

        {

        }

    }

}

A6. Now UI code is complete and we have to sign assembly with strong name so go to Properties >> Signing and sign the assembly using a StrongName.

 A7. Build DisplayVariableUI

A8. We will need public key token of DisplayVariableUI assembly in Task project. To create Public key token Open Visual Studio 2008 command prompt , browse to the project folder where strongkey should be created and type

 a. sn -p DisplayVariableUIPrivateKey.snk DisplayVariableUIPublicKey.snk

To see the Public key token

 b. sn -t DisplayVariableUIPublicKey.snk

Copy the Public Key token

Now we move to Task code with actual runtime code is written:

B1. Create a Class Library project ( DisplayVariable.cs)

B2. Add reference to

1.     System.Windows.Forms
2.     Microsoft.DataTransformationServices.Controls

    -- This doesnt show up in .Net tab so you can browse to C:\Windows\Assembly to add

B3. Code for DisplayVariable.cs

 a. Apply the DtsTaskAttribute attribute to the class, this attribute provides design-time information such as the name, description, and task type of the task. we will need to specify PublicKeyToken of UI here.

 b. DisplayVariable class should inherit from Task Class and then override Base Task class DTSExecResult Execute method to show a message box cantaining Variable name and Variable value.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

namespace DisplayVariable

{

    [DtsTask

    (

    DisplayName = "DisplayVariable",

    Description = "Task can display any variable from Variable List",

    RequiredProductLevel = DTSProductLevel.None,

    TaskContact = "Rahul Sherawat-logtorahul@gmail.com",

    UITypeName = "DisplayVariableUI.DisplayVariableUI, DisplayVariableUI, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=6d67e5bed27edc4b"

    )

    ]

    public class DisplayVariable: Task

    {

        //will get Variable Name

        private string UIvariableName;

        public string VariableName

        {

            get { return UIvariableName; }

            set { UIvariableName = value; }

        }

        //will get variable value

        private string UIVariableValue;

        public string VariableValue

        {

            get { return UIVariableValue; }

            set { UIVariableValue = value; }

        }

        //override execute

        public override DTSExecResult  Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

        {

            DTSExecResult execResult = DTSExecResult.Success;

            if (string.IsNullOrEmpty(VariableName))

            {

                System.Windows.Forms.MessageBox.Show("No Variable Selected");

            }

            else

            {

                string VarVal;

                VarVal = VariableName + "\r\n"+ "Value: " + VariableValue;

                System.Windows.Forms.MessageBox.Show(VarVal);

            }

        return execResult;

        }

    }

}

 c. DtsTaskAttribute  attribute PublicKeyToken is the public key token of UI assembly that we will create in next step and this has to updated in Task assembly and rebuild.

B4. DisplayVariable Assembly has to be signed so go to Properties >> Signing and sign the assembly using a StrongName.

B5. Build the project. This will create DisplayVariable.dll in debug folder of project.

C1. Go to DisplayVariableUI project in add reference of DisplayVariable.dll and build the UI project again.

C2. Now both the dll are created and we have to add them to GAC

In Visual Studio Command prompt type

 a. For DisplayVariable.dll

    gacutil -i DisplayVariable.dll
 b. For DisplayVariableUI.dll
    gacutil -i DisplayVariableUI.dll

C3. Copy DisplayVariableUI.dll  and DisplayVariable.dll to \\Program Files\Microsoft SQL Server\100\DTS\Tasks so that it can be picked up in SSIS tool box

C4. Create a new SSIS project. Go to Tools>>Choose ToolBox items..>>SSIS ControlFlow items select DisplayVariable

C5. You should be able to see DisplayVariable Task in ToolBox now.

C6. Drag it to control flow and use it

You can download entire solution from  here

Same Connection Manager on Multiple Server

There was a question on MSDN forum to Run same query on Mulitple Servers.

We can do this by using one Connection Manager and passing the Connection String through Expressions
Below are the steps I followed to achieve this


1. Store Connection String for various connection in a table

2. Declare two Varibles like






3.Use Expression of Source Connection Manager to provide Connection String

4. Use Execute Sql Task to get connections in a Varible of type Objects from the table

5. Use For Each Loop to Iterate on that variable

6. Use Script component to cast the value of CurrConnection to String
        public void Main()
        {
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
            String con;
            con = (string) Dts.Variables["CurrConnection"].Value;
            Dts.Variables["CurrConnection"].Value = con;
         
        }

7. Use any task inside the for loop with Connection Manager and it will use different server for each loop.