Create SSIS Custom Control Flow Task Part 4 - Something You Need To Know

Today I am going to put an end to this custom control flow task topic. In my previous posts (Part 1 (Project Creation), Part 2(Configuration), Part3 (Coding)) I have presented you a basic solution template. Now I want to talk about some "advanced" topic

List variables on the task UI

This is the most common requirement when designing a SSIS component. Doesn't matter it is a connection manager, a control flow task, a log provider, or a data flow task, you want to give developers some kinds of flexibilities to config the task dynamically.

The variables of the SSIS package are stored in the TaskHost class, so to list variables on your task UI, we can use below codes:

       foreach (Variable var in TaskHostValue.Variables)  
       {  
         if (!var.SystemVariable)  
         {  
           cbx.Items.Add(var.QualifiedName);  
         }  
       }  

It will retrieve all variables stored in the TaskHost class, and if the variable is not a system variable, add it into a combo box (cbx).

Read variable value during the execution phase

Once we done the configuration through our custom UI, certainly we need to get the stored value of the variable during the task execution phase. It actually follows the same way when we do the script task:

     public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)  
 public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)  
     {                
       Variables varFileFormat = null;  
       variableDispenser.LockOneForRead(FileFormat, ref varFileFormat);         
     }  

In codes listed above, we locked the variable FileFormat stored in VariableDispenser and then read the value from it.

Pass complex configuration setting

Sometimes, actually I think most times, the configuration on the custom UI could be quite complex. To pass the configuration setting to the custom task, you may want to use some easy ways to do it, for example, a data table. Then in the execution phase, we simply need to read row by row to get the setting as a whole.

However bear in mind, firstly only simple types can be passed into the task. Here the simple types refer to string type and numeric type.

Now a bit explanation: SSIS package actually will be saved in XML format. So if you store the configuration setting in a complex object type, SSIS won't know how to save it in XML format. Therefore only simple types can be passed into the custom task.

So to allow the setting, for example, a data table, can be saved, at the Task project, we need to implement IDTSComponentPersist interface. Under this interface, we have SaveToXml and LoadFromXML methods, which will tell the package how to save the setting and how to read the setting.

However, to implement IDTSComponentPersist interface, you cannot use "short hand" method to write class properties, for example

     //below is not allowed  
     public string Server { get; set; }  

This style, if I am correct, was first introduced in C#3.0. Basically when we write code in this style, the backend compiler handles the property, create a temp object to automate the property implementation , etc. etc. (Well, I am not professional in programming... :( So correct me if I am wrong)

So you might understand now, because backend compiler will create the temp object, the task cannot really identify the relationship between the temp object and the actual property. So to implement the interface, we need to have the getter and setter implemented explictly.

To save your time, I put one of my implementations below.

SaveToXML
It reads a datatable object DtParameters and then create XML tree nodes based on data rows.

     //save into package  
     public void SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)  
     {  
       XmlElement elementRoot;  
       XmlNode propertyNode;  
       elementRoot = doc.CreateElement(string.Empty, "DtParameters", string.Empty);  
       if (DtParameters != null)  
       {  
         if (DtParameters.Rows.Count > 0)  
         {  
           //  
           foreach (DataRow row in DtParameters.Rows)  
           {  
             propertyNode = doc.CreateNode(XmlNodeType.Element, "Parameter", string.Empty);  
             propertyNode.InnerText = row["ParameterValue"].ToString();  
             XmlAttribute attrParameterName = doc.CreateAttribute("ParameterName");  
             attrParameterName.Value = row["ParameterName"].ToString();  
             propertyNode.Attributes.Append(attrParameterName);  
             XmlAttribute attrRequired = doc.CreateAttribute("Required");  
             attrRequired.Value = row["Required"].ToString();  
             propertyNode.Attributes.Append(attrRequired);  
             elementRoot.AppendChild(propertyNode);  
           }  
         }  
       }        
       propertyNode = doc.CreateNode(XmlNodeType.Element, "Server", string.Empty);  
       propertyNode.InnerText = Server;  
       elementRoot.AppendChild(propertyNode);  
       propertyNode = doc.CreateNode(XmlNodeType.Element, "SelectedReport", string.Empty);  
       propertyNode.InnerText = SelectedReport;  
       elementRoot.AppendChild(propertyNode);  
       propertyNode = doc.CreateNode(XmlNodeType.Element, "FileName", string.Empty);  
       propertyNode.InnerText = FileName;  
       elementRoot.AppendChild(propertyNode);  
       propertyNode = doc.CreateNode(XmlNodeType.Element, "FileFormat", string.Empty);  
       propertyNode.InnerText = FileFormat;  
       elementRoot.AppendChild(propertyNode);  
       doc.AppendChild(elementRoot);  
     }  

LoadFromXML
It read the package XML node and get the saved parameter values into the datatable _dtparameters

     //load from package  
     public void LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)  
     {  
       _dtparameters = new DataTable();  
       _dtparameters.Columns.Add("ParameterName", typeof(string));  
       _dtparameters.Columns.Add("Required", typeof(bool));  
       _dtparameters.Columns.Add("ParameterValue", typeof(string));  
       foreach (XmlNode n in node.ChildNodes)  
       {  
         if (n.Name == "Server")  
         {  
           _server = n.InnerText;  
         }  
         if (n.Name == "SelectedReport")  
         {  
           _selectedreport = n.InnerText;  
         }  
         if (n.Name == "FileName")  
         {  
           _filename = n.InnerText;  
         }  
         if (n.Name == "FileFormat")  
         {  
           _fileformat = n.InnerText;  
         }  
         if (n.Name == "Parameter")  
         {  
           string parametername = n.Attributes.GetNamedItem("ParameterName").Value;  
           bool required = Convert.ToBoolean(n.Attributes.GetNamedItem("Required").Value);  
           string parametervalue = n.InnerText;            
           _dtparameters.Rows.Add(parametername, required, parametervalue);  
         }  
       }  
     }  

So now this topic is done! :D

Create SSIS Custom Control Flow Task Part 3 - Coding the Project

Now I am going to continue this topic, hopefully I can finish this topic in two posts.

In the Part 1 and Part 2 we had built the project, added the references, and generated keys. So in this post, we are going to add some basic codes and attributes into the project.

Firstly let's put our focus on MyControlFlowTask project.

In the MyControlFlowTask project, we need to add a using statement for the Microsoft.SqlServer.Dts.Runtime reference we created before, then make the MyControlFlowTask derived from the Task class.

Starts from here, we can add three override methods: InitializeTask, Validate, and Execute.

For these three methods:

• The InitializeTask will be called when we create the task on the IDE;
• The Validate method obviously will be called when we need to validate the task, such as setting of the task and validation phase before the excution phase;
• The Execute method will be called when we start to run the task;

So my screen looks like below. Because my purpose for this topic is to build a template, so for demostration purpose, I just want this task to pop up a message box and let me know, I am running successfully :P.

Now time to decorate this project a bit. Look at below screenshot, most attributes are easy to understand:

• DisplayName: it will be displayed on the IDE as the task name;
• Description: it will be displayed on the IDE when you move your mouse onto the task;
• UITypeName: This value will be used to build the relationship between the task and task UI. Note the type name must be in its full name;
• IconResource: the icon will be displayed on the IDE. However to build the icon, you need to select the icon, go to properties panel, change the Build Action to "Embedded Resource"

Now we have finished the task project. Time to work on the MyControlFlowTask_UI project. Firstly go to the MyControlFlowTask_UI class. This class will be acting as a bridge to connect the Task and our task form (To make it clear, MyControlFlowTask - MyControlFlowTaskUI - MyControlFlowTask_Form).

To work on it, we need to implement IDtsTaskUI interface:

When you implement the interface, there should be three methods added into the class: Delete, Initialize, and New. To make our template simple, we only need to ensure the property "TaskHostValue" can have an assigned value in the Initialize method.

Now time to link the UI to the form, we call the GetView method and pass the TaskHostValue property into the form. Notice the red line under the form, it is becasue we have not create this form.

Now add a Winform control into the MyControlFlowTask_UI project and name it "MyControlFlowTask_Form"

By default Visual Studio should have built the default constructor for us. But look back the step above, we need to have a constructor to receive the TaskHost value, so we need to create a different constructor:

We are close to the end. Now let's build the solution. (You may ask, what do you do to the form? Just a constructor? My answer is yes, just a constructor. Remember we are building a template. This form will serve the purpose of the task configuration, so at least in this post, as long as it can receive the TaskHost value, it is good enough)

If you follow the steps carefully, the compiled dll should be created under the ..\DTS\Tasks\ folder directly, and the assemblies should be registered in GAC as well.

Now open the SSDT or Visual Studio with BI developer, and create a package. You should see the task under the Common task category (note if you have SSDT opened already, you need to close it an dopen it again, because assemblies will be loaded at the beginning of the application.)

Drag the task onto the control flow design panel, double click it, the form should pop up.

 

Close the configuration form and execute the package by press F5, a message box should pop up and tell you "executed"

Once click the OK button, the green tick icon should be shown on our first control flow task

Congradulations! We have create our first control flow task, and most importantly, this solution can be reused when we need to create a new control flow task, simply need to generate a new key.

So it is close to the end. But I planned to have a part 4, to discuss some extra things you may need to use when you need to create a business-ready task. Now see you next time.

Create SSIS Custom Control Flow Task Part 2 - Project Configuration

Finally get some time to continue this topic.

In the Part1 I had completed tasks of building the projects, and adding the references required. Now it is time to work on project property window.

Firstly let's copy the path of the task project. We can do so by activate the MyControlFlowTask project, then at the properties panel, find the path under Project Folder node and copy it.

Now open the Developer Command Prompt window for VS2012, and navigate to the project folder.

Now generate a key under the project folder by calling sn.exe. In my case, the full command is "C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\sn.exe" -k MycontrolFlowTask.snk

After run the command, we have got the key to sign the project. However, we need to use this key to link the task project and task UI project. So we need to do now are:

• export the token of the public key
• sign both task project and task UI project by this key

So let's continue.

Within the command window, input below command to output the key into public.out.

"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\sn.exe" -p MycontrolFlowTask.snk public.out

Then we need to read the token from public.out, by using command

"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\sn.exe" -t public.out

Now your command window probably will look like below

Good, we are close to the end of the project configuration. Now go back to the task project, open the project property window and navigate to the Signing section. Check the box Sign the assembly, expand the dropdown box and click <Browse...>. The Select File dialogue window should pop up.

Because we just generated the key under the task project folder, you should be able to find the key file immediately.

Now, remember we need to put the assemlbies into GAC, it is better to do some post build command to simplify our life, for example, uninstall the assembly if the assembly already exists, and then deploy the new assembly. Below screenshot should give you an idea what we can do by utilising Visual Studio post build event

So now we have finished general configuration of the task project. Follow the same steps to configure the task UI project:

• Sign the UI project by using the key file under the task project folder
• Add the post build command for "MyControlFlowTask_UI.dll"

Finally we can start our coding for the component, I will go through it in my next post :P.

Create SSIS Custom Control Flow Task Part 1 - Create the Project

Start from here I am going to spend a few posts to introduce my approach for creating a SSIS control flow task. 

Custom component in SSIS gives you lot of flexibilities, and can extend SSIS capacities, can improve code reusability, can improve ETL manageability, blah blah blah…  But because the custom components are created on top of SSIS, there certainly are some troubles. So what I did is create some standard procedures to simplify my job.

Now let me demonstrate my approach through a custom control flow task. The approach starts from solution setup, then goes down to individual project setting, does some coding, and finally a deployment.

The component is created by Visual Studio Express 2012 for Windows Desktop, and essentially, as long as we reference the correct DLLs, the solution can be used for 2008R2, 2012, and 2014.

Custom Control Flow Task requires two assemblies installed in GAC: one is for the Task, which will be called when the task is executed. The 2nd assembly actually contains a winform object for the IDE (BIDS, SSDT, or VS), as well as an UI object for task initialization. So our first task is to have a solution with two class library projects:

Open VS Express and create a class library project. In my demonstration, I named my project to “MyControlFlowTask”.

By default the name of the class in the project is “class1.cs”. It doesn’t make sense in most cases, so I changed the name to “MyControlFlowTask.cs”

Now as I mentioned, we need a 2nd class library project. To make the name consistent, I named the class to “MyControlFlowTask_UI.cs”

In the UI project, I then added a windows form and named it “MyControlFlowTask_Form.cs”.

The solution is ready, now we need to dig into to project level. Let’s begin with project reference

 

The reference we need is Microsoft.SqlServer.ManagedDTS, which can be found at extensions category. Note here version 11 is for 2012, 10.50 is for 2008R2, and 12 is for 2014.

Notice I had System.Windows.Forms referenced as well, by default it is not in the reference list. It is because when the task is executed in the package, I just want to show a messagebox :P (coding is not the most important thing in this series)

Add Microsoft.SqlServer.ManagedDTS into the UI project as well. But this time we need one more reference for Microsoft.SqlServer.Dts.Design.

Now we have done project references. What we need to do is to sign the projects, because after compile these assemblies needs to be added into system GAC.  I will continue the topic in my next post.