Solving SQL Server Integration Services Expressions

Solving SQL Server Integration Services Expressions using Expression Builder is a bit of a chore until you have a good understanding and a few working examples of some useful expressions. Here are several common exceptions you might see when working within Expression Builder as well as examples and explanations of correct syntax.

The exception:

Cannot convert System.Int32 to System.String

Here are a couple more exceptions you may come across:

“Cannot convert expression value to property type.”
“Cannot convert ‘System.DateTime’ to ‘System.String’.

Even the expression examples published by Microsoft fail with various exceptions due to type conversion: https://docs.microsoft.com/en-us/sql/integration-services/expressions/cast-ssis-expression

This: MONTH((DT_DATE) “05/31/2017”) throws this exception:
Cannot convert expression value to property type.
Cannot convert ‘System.Int’ to ‘System.String’.

You must explicitly cast the result to DT_WSTR.

This: DAY(GETDATE()) throws the same exception as above.

Again, you must explicitly cast the result to DT_WSTR.

A few more examples:

If you want to extract the time component without the specials characters from DT_DBTIMESTAMP2, use this:

or

This is the version I use to stamp a file I’ve processed with the date and time:

Leave a Reply

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