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.

(DT_WSTR, 2) MONTH((DT_DATE) "05/9/2017")
5
(DT_WSTR, 2) DAY((DT_DATE) "05/9/2017")
9
(DT_WSTR, 4) YEAR((DT_DATE) "05/9/2017")
2017

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

Again, you must explicitly cast the result to DT_WSTR.

(DT_WSTR, 2) DAY(GETDATE())

A few more examples:

(DT_WSTR, 4)YEAR(GETDATE()) + RIGHT(REPLICATE("0", 2) + (DT_WSTR, 2)MONTH(GETDATE()), 2)
201705
(DT_WSTR, 4) YEAR(GETDATE()) + RIGHT(REPLICATE("0", 2) + (DT_WSTR, 2) MONTH(GETUTCDATE()), 2) + RIGHT(REPLICATE("0", 2) + (DT_WSTR, 2) DAY(GETUTCDATE()), 2)
20170509
(DT_WSTR, 10)(DT_DATE)"05/9/2017"
5/9/2017
(DT_WSTR, 24) (DT_DBTIMESTAMP2, 4) GETDATE()
2017-05-04 16:43:38.5860
REPLACE(REPLACE((DT_WSTR, 32) (DT_DBTIMESTAMP2, 4) GETDATE(), ":", ""), ".", "")
2017-05-04 1643176420

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

SUBSTRING(REPLACE(REPLACE((DT_WSTR, 32) (DT_DBTIMESTAMP2, 4) GETDATE(), ":", ""), ".", ""), 12, 128)
1703534770

(DT_WSTR, 32) (DT_DBTIMESTAMP2, 4) GETUTCDATE()
2017-05-04 23:44:47.6230

or

(DT_WSTR, 32) (DT_DBTIMESTAMP2, 4) (DT_DBDATE) "5/28/2017"
2017-05-28 00:00:00.0000

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

(DT_WSTR, 4) YEAR(GETUTCDATE()) + RIGHT(REPLICATE("0", 2) + (DT_WSTR, 2) MONTH(GETUTCDATE()), 2) + RIGHT(REPLICATE("0", 2) + (DT_WSTR, 2) DAY(GETUTCDATE()), 2) + SUBSTRING(REPLACE(REPLACE((DT_WSTR, 32) (DT_DBTIMESTAMP2, 4) GETDATE(), ":", ""), ".", ""), 12, 128)
201705061730058600

Leave a Reply

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