2015-05-26

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.

2015-05-15

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.