none
Timestamp datatype in SSIS

    Question

  • Hi,

    I have a column with Timestamp datatype in my source and destination table. I have to compare between the timestamp in the source and destination and split the flow using the conditional split. Since the timestamp column cannot be compared I thought of converting into binary(8).

    The error I am getting is DT_BYTES cannot be used with binary operator "!=". The type of one or both of the operands is not supported for the operation. To perform the operation one or both of the operands needs to be explicitly cast with cast operator.

    If I try to convert into string then I get all empty data.

    Is there any way to compare between the varbinary data in ssis ?

    Thanks,

     

    Friday, July 30, 2010 8:47 PM

Answers

  • You are correct, but everyone keeps misinterpreting you, thinking you're talking about a DateTime type.  The TIMESTAMP type in SQL is a ROWVERSION data type - not a type containing time information.

    Now, I've refrained from posting here to date because I really haven't worked with this data type in SSIS... but since everyone is misunderstanding you, I thought I'd see if I could help.  The above timestamp data type page says this:

    A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

    That tells me that you should be representing that column in SSIS as a DT_BYTES column - which it sounds like you tried.  It also sounds like the SSIS expression language (used in the Conditional Split) doesn't like to compare two instances of those types.  This is most likely due to there not being a true definition of what "<" or ">" mean in reference to those values, even though it should be comfortable with "=" and "!=".

    In a brief test, what worked for me was casting the DT_BYTES columns to DT_WSTR of twice the size.  In your case, with a (DT_BYTES, 8) set of columns, cast them to a (DT_WSTR, 16).  So your expression in a Conditional Split should be:

    (DT_WSTR, 16)[TimestampColumnA] != (DT_WSTR, 16)[TimestampColumnB]
    
    

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by nadirsql Saturday, July 31, 2010 7:28 PM
    Saturday, July 31, 2010 3:47 PM

All replies

  • Sorry if I didnt make my question clear. But, I didnt find the link useful for my question. I wanted to know if timestamp datatype (not the date time datatype) can be compared in ssis. I thought may be by converting into varbinary, but that is also not giving me the result.

    If none of that possible, what is the use of timestamp datatype ? I thought the timestamp datatype would give me the row version and can be easily compared to see if there is any update in the record.

    Thanks,

    Friday, July 30, 2010 9:01 PM
  • Hello,

     What do you mean by Timestamps Comparison. Do you need to compare at millisecond level as well, if so then you can't do it in SSIS 2005 as it doesn't support Timestamp at Millisecond Level. But If you want to Compare only till Second Level then you can compare it taking the source Timestamp Column as DT_DBTIMESTAMP in the Conditional Split.

     Let me know if you require any more info on this.

     


    Chandu - SQL Server 2005 - Please mark posts as answered where appropriate.
    Saturday, July 31, 2010 8:54 AM
  • if you really want Timestamps to be compaired i think the best think is to move the data to a staging table in SQL then do what you want.

    just a suggestion


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s)
    Saturday, July 31, 2010 12:32 PM
  • You are correct, but everyone keeps misinterpreting you, thinking you're talking about a DateTime type.  The TIMESTAMP type in SQL is a ROWVERSION data type - not a type containing time information.

    Now, I've refrained from posting here to date because I really haven't worked with this data type in SSIS... but since everyone is misunderstanding you, I thought I'd see if I could help.  The above timestamp data type page says this:

    A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

    That tells me that you should be representing that column in SSIS as a DT_BYTES column - which it sounds like you tried.  It also sounds like the SSIS expression language (used in the Conditional Split) doesn't like to compare two instances of those types.  This is most likely due to there not being a true definition of what "<" or ">" mean in reference to those values, even though it should be comfortable with "=" and "!=".

    In a brief test, what worked for me was casting the DT_BYTES columns to DT_WSTR of twice the size.  In your case, with a (DT_BYTES, 8) set of columns, cast them to a (DT_WSTR, 16).  So your expression in a Conditional Split should be:

    (DT_WSTR, 16)[TimestampColumnA] != (DT_WSTR, 16)[TimestampColumnB]
    
    

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by nadirsql Saturday, July 31, 2010 7:28 PM
    Saturday, July 31, 2010 3:47 PM
  • Todd, you are just Awesome!!! Though you said you havent worked with it, it worked great for me. Everytime you answer my question, I learn new things. Actually, I have even tried converting it to DT_WSTR, but I think I tried it with different length. This time this just worked great. I also want to know why we need to double the size when we are casting DT_Bytes  to DT_WSTR.

    I have another question in my mind. We have this Timestamp Column with Timestamp Datatype so as to find if there is any update in the row. I am now using conditional split with (DT_WSTR, 16)[TimestampColumnA] != (DT_WSTR, 16)[TimestampColumnB] expression as you have suggested. Is this timestamp column this great ? Or, there are any disadvantages using this for finding out the updates in a row. If so, I havent found developers using this much often, rather they use GUID or Datetime. We are not using GUID, or a DateTime column instead a Timestamp. Is it a best approach to use Timestamp Column for finding out the delta change ?

    Saturday, July 31, 2010 7:40 PM
  • The likely reason why you need to double the space is that I believe it treats the BYTES like a DT_STR conversion - you might not need all 16 bytes, but in some circumstances you will.  I just know that I got an error with eight.

    I can't answer your "best practice" questions about the timestamp column - you may want to ask that in the T-SQL or Database Design forum.  My hunch is that the timestamp column is more robust for this purpose because it's theoretically possible for two updates to a row to update a datetime column with the same value, and GUIDs are huge.


    Todd McDermid's Blog Talk to me now on
    Saturday, July 31, 2010 10:41 PM
  • Sorry for coming back to the same question.

    It seems that the following link says that DT_Bytes can be use with != in SSIS Expression. But why was I getting the error :

    "DT_BYTES cannot be used with binary operator "!=". The type of one or both of the operands is not supported for the operation. To perform the operation one or both of the operands needs to be explicitly cast with cast operator."

    http://msdn.microsoft.com/en-us/library/ms141714.aspx

    The expression set, expression1 and expression2, must follow one of these rules:

    Binary   Both expression1 and expression2 must evaluate to the DT_BYTES data type.

    Monday, August 02, 2010 3:49 PM
  • Todd I realize this post is really old, but I was just wondering if you could help a little bit,

    What you posted works great for me, however I need to use this newly created field with data type (DT_WSTR) in an aggregate component to get the max timestamp value of the loaded records

    Appreciate your support. :)


    Best Regards Omar Sultan

    Wednesday, February 13, 2013 5:50 AM