My First SSIS Custom Task


I was never a fan of DTS package. When I started my current job, I rewrote all the DTS packages that I have to touch, which are left by VB programmers from two decades ago using .NET and made them all windows services. Later I had to revise my strategy to use SQL jobs to call stored procedures, since my management decided not to pursue the development of an application monitoring tool, and we had to rely on SQL agent to handle error logging and notification.

My bias towards DTS, or its newer name SSIS, came from working mostly on OLTP side of the house, where the volume of data SSIS package can handle in less time never has the same importance compared to the constent changing requirement from business. Also, for support purpose, programmers, including me, who do not treat dragging and dropping in an IDE as programming, are more willing to rewrite code, than open the SSIS packages.

Knowing the importance of OLAP development, and the popularity of SSIS among Business Intelligence world, I decided to write a custom SSIS task, as a goodwill gesture to remedify my unfair treatment of SSIS over the years.

Here’s a link from Microsoft MSDN on how to write an custom SSIS task
http://msdn.microsoft.com/en-us/library/ms345156.aspx

It is the official document, and it includes lots of links to related topics and definitions. However, I found this link easier to follow, with more screenshot of examples, and it’s creating a task to zip and unzip files, which you can actually create and use it for your work. It’s a perfect starter.
http://grounding.co.za/blogs/brett/archive/2007/11/02/developing-a-custom-control-flow-task-for-ssis-part-1.aspx

I followed the steps in the above article, and created my first Custom Task, which at this point only displays a window, where I can open a text file and browse the content.

I compiled it, copied it to the right folder, added it into GAC, loaded it up in Visual Studio, but when I tried to drag a task into my project, I got the following error

The task user interface specified by type name ‘XXXXXXXXXXXXXXXXXXX’ could not be loaded. (Microsoft.DatatransformationServices.Design).

I opened up google, put the following text in the search box “ssis custom task the task user interface specified by type name could not be loaded”, and found quite a few people ask the question, but I didn’t find a good answer. Surprisingly a lot of people cited the same article I was using to create the task. I knew it must be something that I didn’t do as the same in the article. It turned out that I created everything using my own namespace and class name, and when I come to write this piece of code, I wasn’t paying too much attention to the UITypeName.

[DtsTask(DisplayName = “Unstructured File Task”,
             Description = “A custom task for parsing unstructured files.”,
             TaskType = “Xceed Task”,
             UITypeName = “UIName,DllName,Version=1.0.0.0,Culture=Neutral,PublicKeyToken=f92588b9af2d2534”,
             IconResource=”UnstructuredFileParser.Unstructured.ico”)]

The correct format for UITypeName should be “<Class name>,<Dll name>,Version=<Dll version>,Culture=<Dll Culture>,PublicKeyToken=<PublicKeyToken>”, and I put the wrong name in <Dll name>.

This will not give you an error when you compile it, but Visual Studio will give you the error since it will not find the right object.

Later I find another error that can only be caught at runtime, in my code I did something like

this._fileName = node.Attributes.GetNamedItem(“FileName“).Value; but mistakingly I created the property as Filename with a lower case n. The error I got is property “FileName” cannot be found.

I hope this is helpful to whoever is going to make the same mistake when creating their own custom SSIS task, so WordPress pubsubhubbub, send a message to google to index this blog under “ssis custom task the task user interface specified by type name could not be loaded”.

Advertisements

2 comments

  1. Glad you’ve decided to give SSIS a try – it suffers from the dual stigmas of being completely alien to those familiar with DTS, and yet is just as scary to those that were scared of DTS.

    I’ve got a series of posts to assist in Custom Task development as well – starting from a common basis of having the code working in a Script Task: ttp://toddmcdermid.blogspot.com/2009/06/converting-your-script-task-into-custom.html.

    I’ve also found that there are lots of good samples on CodePlex (http://ssisctc.codeplex.com) of varying complexity and styles – it’s almost guaranteed that you’ll find a snippet in one of those projects to help with whatever hurdle you’re trying to overcome.

    Enough self-promotion from me. Good luck with your Task. When it’s done, please do consider publishing it and have it listed on the CodePlex site I mentioned above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s