I was going to post this all in one shot, but that would have been far too lengthy. So I've decided to divide it into three parts: why you should consider a Custom Task, how to make one, and adding a custom UI to your Custom Task. Those ought to give you all the steps to take the contents of your Script Task, turn it into a Visual Studio project to compile a custom DLL, add that to BIDS, drop it on to your Control Flow surface, and merrily design packages with reuse confidence.
Out-of-the-box Tasks
The tasks that ship with SQL Server Integration Services (sometimes referred to as those that come "out of the box") are native (unmanaged) code. They cover a wide range of capabilities, from database commands to file system and internet service interaction. They include flow-control tasks such as a Foreach Container and Sequence Containers to manage encapsulation and concurrency.
What's a Script Task?
Script tasks are SQL Server Integration Services' primary vehicle for making the Control Flow of SSIS packages extensible. If what you want done isn't available out-of-the-box, they wanted to provide some method for you to still accomplish your task. (I have heard that this type of design decision is not common among ETL tool vendors - that's a shame, IMO.)
In SSIS 2005, the Script Task allowed you to roll your own code in Visual Basic.Net (VBA to be precise). SSIS 2008 extended this capability by replacing the VBA engine with VSTA, permitting VB.Net or C# code in the Script. Both versions allowed you to do almost anything a full-fledged .Net framework application could do - on top of the ability to interact with the currently running SSIS package. You could reference external assemblies, perform complex logic, ...
BIDS (Business Intelligence Development Studio - another name for Visual Studio when skinned for BI projects) allowed you to supply .Net code to accomplish the tasks you wanted to, and provided a neat harness that removed a lot of the complexity of plumbing into the COM interop you would otherwise need to do in order to execute in an IS package and communicate with it.
What's a Custom Task, and Why Do I Care?
A Custom Task is the next step in extensibility. It's essentially a .Net Framework DLL containing code that conforms to a predetermined interface. If you use a lot of Script Tasks to extend your packages' capabilities, you're probably quite aware of the Script Task's Achilles heel, and are looking for some alternatives.
Why Choose One Over The Other?
If at all possible, use an OotB Task. The tasks that come with SSIS are very reliable and accomplish their jobs well.
That said, sometimes there isn't anything in that toolbox that does the job you're looking for. A very common tool that seemed to have missed Microsoft's cut (or vision) was a task that would detect file existence, and/or report on file properties such as creation date or modification date. In these cases, the Script Task is the tool of choice to add those capabilities.
So what space is left for Custom Tasks? The best answer to that is reusability.
Reusability of Scripts
"Sure, Scripts are reusable - doesn't your copy and paste work?" - Captain Obvious
Of course they do - and of course you can reuse the code in a Script Task by copying and pasting into another Script Task. In fact, you can copy the Task itself (not just the code) and paste it. (You will have to edit it in order to get it to recompile, though - the paste doesn't trigger that step.)
The biggest problem with that statement, Captain, is that it's reusable at a cost. The costs?
- Some difficulty referencing external assemblies in BIDS 2005.
- Script Tasks are rarely pure copy-and-paste reusable. Variable names change from package to package, and there are always other small variations.
- Fixing bugs means fixing bugs in each Script Task you've ever used that code in... and there's no way to find out where you've used it.
- The only way to communicate "parameters" (or customize one use from another) in your Script is to hard-code them (not very flexible) or pass in variables (which can get tiresome).
- There's no assistance provided to the package designer using the Script as to how they should use it. Unless they coded the script, they won't know how many or which variables to pass in or out.
Pretty much the only "argument" (reason) you can have to move to a Custom Task is that you've gone the Script Task route already, and have felt the burden of maintaining that codebase. If you're looking to make your Task run faster - don't go custom - it won't help. If you're looking for an easier debugging or coding experience - you won't find it here.
Take the Next Step!
If you've decided that now is the time to end the insanity of maintaining a reused Script Task, there may still be one option you should consider before you pull the trigger on converting to a Custom Task. Ivan Peev has created quite a few Custom Tasks under the umbrella of CozyRoc. One of those Tasks is the Script Task Plus, which does away with the primary pain point of reusability. You owe it to yourself to give it a shot - it's free for eval.
If that's not for you, or if you're just plain code-crazy and would love to give this a shot, read on to part two and find out how to actually get it done.
No comments:
Post a Comment