SSIS Variable Casting

  • steve.jacobs

    SSCommitted

    Points: 1830

    Comments posted to this topic are about the item SSIS Variable Casting

  • Koen Verbeeck

    SSC Guru

    Points: 258927

    The intention of the question is very good and it is a nice question, but there are some minor flaws.

    I was tempted to select "None of the above", as with my regional settings none of the options would give the format that was asked. (but since I had to select 3, I knew this was wrong 🙂

    Regarding the explanation: you say one of the first answers gives a type cast error. This is only true in some cases, it depends on what you want to do with the result. The question mentions something about email messages, but doesn't give any specifications. (this is however not relevant for choosing the correct answers)

    Anyway, great question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Veteran Jones

    SSC Eights!

    Points: 937

    This question gave me a headache.. then I went to the reference page and it got worse. Looks like I need to read up on this so I can understand it better.

  • steve.jacobs

    SSCommitted

    Points: 1830

    All:

    I thought I had put in the answer "the format indicated in the question is a determination of my regional \ language settings (en-us \ short datetime: M\d\yyyy h:mm:ss tt)...". My apologies for leaving this "NOTE" out.:crying: However, this exercise is a Datetime casting by using Wide String and String, something those of us have had many experiences in C, C++, MFC and Windows API programming.

    Again, my apologies.

  • Koen Verbeeck

    SSC Guru

    Points: 258927

    steve.jacobs (11/20/2013)


    All:

    I thought I had put in the answer "the format indicated in the question is a determination of my regional \ language settings (en-us \ short datetime: M\d\yyyy h:mm:ss tt)...". My apologies for leaving this "NOTE" out.:crying: However, this exercise is a Datetime casting by using Wide String and String, something those of us have had many experiences in C, C++, MFC and Windows API programming.

    Again, my apologies.

    Don't feel too bad. The intent of the question was clear and people should be able to select the right answers regardless of any regional settings.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Koen Verbeeck (11/20/2013)


    steve.jacobs (11/20/2013)


    All:

    I thought I had put in the answer "the format indicated in the question is a determination of my regional \ language settings (en-us \ short datetime: M\d\yyyy h:mm:ss tt)...". My apologies for leaving this "NOTE" out.:crying: However, this exercise is a Datetime casting by using Wide String and String, something those of us have had many experiences in C, C++, MFC and Windows API programming.

    Again, my apologies.

    Don't feel too bad. The intent of the question was clear and people should be able to select the right answers regardless of any regional settings.

    +1

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Good question - thanks.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • TomThomson

    SSC Guru

    Points: 104767

    Valued Jones (11/20/2013)


    This question gave me a headache.. then I went to the reference page and it got worse. Looks like I need to read up on this so I can understand it better.

    I got a headache too. In the end I ticked 3 random boxes so I could look at the answer and the explanation and the reference. That unfortunately didn't relieve my headache: for example the explanation says the default type of System::ContainerStartTime is datetime. Quite apart from the question "what does 'default type' mean?" it's notable that no datetime type is mentioned on the referenced page, so presumably SSIS casts don't allow it to be cast to anything: that tends to be confirmed by the Integration Services Data Types page where there is no datetime type, nor even a DT_DATETIME type. I don't know why it took me a while to realise that "datetime" wasn't meant to be the name of a type, but a descrition of a type - perhaps because SSIS has more than one type which is a datetime type, so it doesn't specify a definite "the default type". And the SSIS Casts page is full of nonsense anyway, for example the statement "(DT_WSTR,20) casts 20 byte pairs, or 20 Unicode characters, to the DT_WSTR data type" may sometimes be true but will usually be false since the typical thing cast to that target type will be some sort of number or date or time or timestamp that doesn't occupy 40 bytes, for example an 8 byte floating point number representing a date and a time - an object of type DT_DATE - and that sort of thing makes it hard for me to take the page seriously. The explanation can't be blamed for teh faults of that page, and that seems to be only page it makes sense to reference unless there is somewhere some documentation of what the string casts actually do with DT_DATE and DT_TIMESTAMP data, but the explanation makes up for that by explaining what is actually happening, although at one point the language is rather careless, saying that a particular cast adds fractional seconds, which I find quite amusing: claiming that a cast adds precision is almost excusable because precision can be considered an attribute of the type, but of course casts operate on values, not on types, so it isn't really excusable. But claiming that it adds fractional seconds is not even almost excusable. There is a fractional seconds field in the new value created by the cast, and if it put 2, or 37, or anything but 0 in there it would be adding some fractional seconds to teh old value to get teh new one, but it puts 0 in there so no fractional seconds are added.

    I like the question, though. At least the answer is probably right, even if the page referenced contains some junk and the explanation contains some oddities, and it's made me think I should get around to looking at SSIS properly sometime as a preliminary to looking at some other esoteric services associated with SQL Server.

    Tom

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Good question..

    agreed with every one here.. the intention was good..

    Thanks..

  • twin.devil

    SSC-Insane

    Points: 22208

    i got it right ...

    Nice question 🙂

  • Ken Wymore

    SSCoach

    Points: 16365

    Nice question. These conversions in SSIS always give me a headache.

  • sneumersky

    SSCertifiable

    Points: 7667

    Don't feel too bad. I gave lots of folks a headache by leaving the word "NOT" out of a question one time.

  • sqlnaive

    SSCoach

    Points: 17435

    Good question. Feeling nice to cover up after more than a week's leaves. 🙂

  • npodlesny

    SSC Enthusiast

    Points: 162

    Interesting question. I like it and I do use date variables in SSIS Send Mail Task (Added 12/4/13: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)).

    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!

Viewing 15 posts - 1 through 15 (of 22 total)

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