SSIS Variable Casting

Question

SSIS Variable Casting

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

steve.jacobs SSCommitted Points: 1830 More actions · Answer 1

npodlesny (12/3/2013)
Interesting question. I like it and I do use date variables in SSIS Send Mail Task.
Did anyone tried to verify the given "right" answers?
Here what I've got by trying following:
- created SSIS package
- in control flow added "Send Mail Task"
- added an expression (DT_STR, 60, 1252) @[System::ContainerStartTime] for "MessageSource" property
- evaluation failed as following:
Expression cannot be evaluated.
The expression "(DT_STR, 60, 1252) @[System::ContainerStartTime]" has a result type of "DT_STR", which cannot be converted to a supported type.
(Microsoft.DataTransformationServices.Controls)
Perhaps, author's "using this variable in email messages" refers to something else?.. Curious to find out.
Thank you!

Interesting. That conversion worked for me but I normally do not use it. (SQL Server 2008 R2 SP2)

sneumersky SSCertifiable Points: 7667 More actions · Answer 2

Thought the conversion needed to be Unicode, but I could be wrong...

npodlesny SSC Enthusiast Points: 162 More actions · Answer 3

sneumersky (12/4/2013)
Thought the conversion needed to be Unicode, but I could be wrong...

Correct.

Just for the record, I upgraded all my SQL Server components to Service Pack 2 (Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)) and no difference, same error.

steve.jacobs SSCommitted Points: 1830 More actions · Answer 4

npodlesny (12/5/2013)
sneumersky (12/4/2013)
Thought the conversion needed to be Unicode, but I could be wrong...
Correct.
Just for the record, I upgraded all my SQL Server components to Service Pack 2 (Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)) and no difference, same error.

Used in an OnError event

npodlesny SSC Enthusiast Points: 162 More actions · Answer 5

steve.jacobs (12/5/2013)
Used in an OnError event

Steve,

Thanks to your example I made an interesting discovery:

When expression look just like following (DT_STR, 60, 1252) @[System::ContainerStartTime] engine seems cannot perform implicit conversion into expected Unicode data type, but when you add to it concatenation with some string (even just empty string) like following - it seems corrects user error and you good to go! "" + (DT_STR, 60, 1252) @[System::ContainerStartTime]

Anyway, I suggest to use proper casts to eliminate confusion for next developers that would face our code 😉

Ahh... one more thing, whether to use Send Mail Task in Control Flow or Event Handlers - does not matter. Difference only in Variables collection. For example, variable "System::ErrorDescription" will not be found in Control Flow.

Thank you again.

steve.jacobs SSCommitted Points: 1830 More actions · Answer 6

npodlesny (12/5/2013)
steve.jacobs (12/5/2013)
Used in an OnError event
Steve,
Thanks to your example I made an interesting discovery:
When expression look just like following (DT_STR, 60, 1252) @[System::ContainerStartTime] engine seems cannot perform implicit conversion into expected Unicode data type, but when you add to it concatenation with some string (even just empty string) like following - it seems corrects user error and you good to go! "" + (DT_STR, 60, 1252) @[System::ContainerStartTime]
Anyway, I suggest to use proper casts to eliminate confusion for next developers that would face our code 😉
Ahh... one more thing, whether to use Send Mail Task in Control Flow or Event Handlers - does not matter. Difference only in Variables collection. For example, variable "System::ErrorDescription" will not be found in Control Flow.
Thank you again.

You are absolutely correct pertaining to using proper casts. I never use the DT_STR for various reasons. As for the tasks and event handlers, all I was doing was indicating where I was using the Send Mail Task. Again, pertaining to Variable collection, you are correct as to what is available depending on where you are using the task.

Thanks

sneumersky SSCertifiable Points: 7667 More actions · Answer 7

I forget. Is there a limit on the size of a WSTR variable....something like 4,000 characters. If someone's email message exceeded that number of characters, that could be an issue, but I would think the failure message would be pretty clear in that case......

I was burned by a similar SSIS character limit one time when I was dynamically creating an XMLA string to be executed later in an SSAS Execute DDL downstream task. Horrors of being on the standard edition back in the day!