SSIS – The script task junkie!

The script task in SSIS was a fantastic improvement on DTS Active X script. The reasons for this in my view are 2 fold:-

  1. Firstly, by allowing the user to code in Visual Studio it is far easier to develop and debug. Because the developer can make use of intellisense & object explorer, it means even the casual programmer can read & write code.
  2. Secondly, because we can use it to access other external .net assemblies, it provides functionality for logic that can’t be achieved using the standard SSIS components.

These are both reasons in themselves that make it a compelling choice when deciding how to develop your package logic.

However, I have noticed at both client sites and on the forums, that there are those (the script task junkies!) that use the script task in almost any scenario when it is just not necessary. It makes maintenance and support tricky at best and sometimes impossible…

Microsoft SQL Svr Developer Edtn 2008 English DVD (PC)

Variable assignment

There is no need to assign your variables in the script component in most cases. It is far better to use configurations or derive your variables from an expression. I have worked at client sites where I’ve found part hardcoded expressions in the script task, where all the developer was doing was deriving a folder path. This could have been achieved much easier using an expression.

Why Not?

By using the expression editor, you can evaluate the variables at design time without having to run the package or set up breakpoints/watchers. This is quite a big deal because, if you need to run the package in order to find out how the variables are being assigned, then you are exposing yourself to the risk of unintended data changes.

Exceptions

There is an issue in SSIS 2005 whereby you cannot assign a variable using an expression if the result evaluates to a string larger than 4000 characters. In this scenario, the work around is to assign the variable within a script.

File System Operations

Again here, unless there is some complex business logic in your loops and naming conventions, there’s no need to use the script task. I’ve seen advice given that its easiest to use a script task – it’s not. Its far easier to use the file system component. I’ve also seen it said that using this causes an error if the file doesn’t exist – not if you wrap a for each loop container around it (it simply won’t run if the container doesn’t find a matching file).

Exceptions

Whilst I don’t believe there are many exceptions to this, there may be a need to prevent the rest of the package executing if no files have been copied. In this instance you can use a script task to increment a variable each time you go round the loop. Then test for a value greater than zero in the precedence constraint in order for execution to continue. You could also avoid the need for this, by checking the name of the variable that holds the file name. If this variable now has a file name rather than say a dummy value, you know that a file was found.
There are occasions also where you only want to execute the file system task if the file in question matches a specified criteria. For instance if you want to delete files that haven’t been modified in a month, then many of these attributes can only be retrieved using a script. Having said this, I would still strongly recommend using the script to assign the attribute variable and using a mixture of precedence constraints, and expressions to define the business logic.

String and Date Manipulation
Why not?

For the same reasons as previously mentioned, it is far easier to support and manage SSIS packages when the logic is transparent. The expression language in SSIS is also very powerful and there is little manipulation that can’t be done using it.

Conclusion

It’s always important to keep in mind as you’re developing your SSIS packages, how easy will it be to maintain what you’ve just written. Remember that you won’t be thanked by anyone if your package can launch a rocket ship, make breakfast and walk the dog – but no one can figure out how to change it.

In the case of the script component, I always ask myself if I can achieve the same logic without using it? If the answer is yes, then I go back to the drawing board. If it’s no then I use it, keeping in mind that the support staff / DBAs (who’s VB/C# coding ability may not be that great) who come along after me should be able to understand what it’s doing.

As ever with software development, it’s easy to code something clever with bells and whistles on it, but making it easy to manage and support takes much more effort.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.