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
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.
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=220.127.116.11,Culture=Neutral,PublicKeyToken=f92588b9af2d2534”,
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”.