Thursday, December 15, 2005 - Posts

SSIS Tip: Use variables rather than expressions in loops

When using loops in Integration Services you can set the value of sub tasks using expressions. If these epxressions are to be based on the values obtained in the loop I advise that rather than using a complex expression in the task. Instead set a variable to be the complex expression and then set the expression of the task to that variable.

This allows very easy debugging, because you easily see the variables at runtime in the debug windows (Locals)

You can then reuse that value multiple times, i.e. if you want to obtain the name of the file not the complete path. This variable can then be passed to say a script task to provide additional logging/debug information.

SSIS Tip: Always use try catch in Script Component

I have found that occasionally errors that occur in a script component can cause the package to fail but the error is lost.

This might sound like an obvious statement, but I advise that you use the try catch block in your script component. This is especially true when populating a column with a value as this is the most likely cause of errors in script components and largely down to truncation errors.

If you want to redirect errors to another output this is also essential.

Enhanced Error Component

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Enhanced Error Component
The new RSS feed is here

I have finally got my Enhanced Error Component online.

You can download it from here http://sqlblogcasts.com/files/3/custom_components/default.aspx

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Enhanced Error Component