SQL Servers DTS (Data Transformation Services) can be a huge pain in the butt, but it also has some great tools for data migration / loading.   So if you’re moving lots of data between databases / servers, chances are you are probably using it.

I spend a lot of my time in the DTS world, and have gotten used to its idiosyncrasies and short-comings.   I was recently educated on a method that will make me more productive and provide greater functionality for my DTS packages.  

Create and call your own COM component in the ActiveX Script Task. (Thanks to Jay Meekin at Magenic Inc (my new Employer)    

But…  I’m not just creating any COM object, I’m using C# to do this.    Mingling the old with the new so to speak.  

This might not be new to some of you more advanced developers, since I've never coded a COM object, it was a little out of my comfort zone, but really wasn't difficult at all.  Database folks that spend their time only writing SQL for their DTS packages should definitely spend time to explore this option.

So why is this a big deal??   DTS has no way to re-use subroutines or methods in it’s ActiveX tasks.    You have 3 choices in DTS if you want to reuse code.

1. Copy and paste between a bunch of DTS ActiveX scripts. (not really re-use)
2. Write only SQL related code and call stored procedures
3. (my new favorite)  Write your own com object and call it in your ActiveX Task.

To help you get started, here’s a quick cheat sheet.

1. Create your Class / Methods in a dotNet language (you must have a constructor that has no parameters)
2. Put the  DLL in %SystemRoot%\System32 (On all the boxes that will execute the DTS Package)
3. Run REGASM to register the DLL (you might have to search for this if it’s not in your path)
4. Add CreateObject(“YourNameSpace.YourClassName”) to your ActiveX Script
5. Have Fun!!

Bill