Trying to use rowversion (Timestamp) in SSIS as a for a high watermark

  • Trying to use rowversion (Timestamp) in SSIS as a for a high watermark to extract changed data

    Failure 1: try to store the Max Rowversion in a variable Varbinary(8) to Variable type Byte Get me this error.

    " failed with the following error: "Input string was not in a correct format.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

    Failure 2: Convert and store the Rowversion value as a nvarchar(max) then try to convert it  back to a SQL variable in the data source Query Declare@rowversion Varbinary(8); Set @rowversion = Convert(Varbinary(8),?,1) this also fails with an Error

    This is forcing me to convert the field on the data side to compare it to the Value WHERE CONVERT(VARCHAR(MAX), CONVERT(BINARY(8), tRowVersion), 1) > ?; this is bad SQL and makes the result set take way to long.

    What am I missing here?  Shouldn't varbinary store just fine in a Byte variable?

     

  • You could try making the data type "rowversion" or "binary(8)" just to see if it works.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • The data already is the problem appears to be that that's not a type option for SSIS variables  only BYTE

  • Do you mean DT_BYTES (byte array), or byte type (single-byte equivalent of a tinyint)?

    Eight bytes definitely won't fit in a one-byte type (Byte).

    Do a select using CAST(rowversion AS BIGINT) or CAST(rowversion AS AS INT).

    When I look at one of my tables w/ a rowversion, I see values over 460 million (460000000).

    To use it in SSIS, couldn't you convert to DT_BYTES(8) (keeps it binary) or DT_UI8 (unsigned equivalent of bigint) in a derived column?

     

     

  • The only option you have in defining an SSIS variable is BYTE as a Binary data type. this isn't a transformation data type problem.  Its using the SSIS variable to test against the Datastream.

    The OLEDB source SQL Scripts where clause looks like this,

    "Where tRowversion > ?"  the ? parm being assigned to the SSIS Variable

    Fails with the following error;

    Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

  • see this example - with it you should be able to set  your ssis to work with the rowversion value

    /*
    if object_id('test') is not null
    drop table test
    create table test
    (id int
    ,rowidentifier rowversion
    )

    insert into test(id) select 1
    insert into test(id) select 2
    */
    /*
    to use rowversion on queries and to extract data to other systems in a "usable" format
    we need to first convert to binary(8) then to char(18/16) as a hex string
    this string can then be stored and used at a later stage to extract records where
    the rowversion is equal or greater than it
    */

    declare @cutoff_rowversion char(16)

    set @cutoff_rowversion = (select convert(varchar(16), convert(binary(8), min(rowidentifier)), 2) from test)

    select @cutoff_rowversion

    select *
    , convert(varchar(18), convert(binary(8), rowidentifier), 1) as format_1
    , convert(varchar(18), convert(binary(8), rowidentifier), 2) as format_2 -- preferred way as it saves 2 bytes
    from test
    where rowidentifier > convert(varbinary(8), @cutoff_rowversion, 2)
  • Thanks Federico , yet this seem to not work properly , at least as far as I cloud see:

     DECLARE @TimeStamp VARCHAR(50) = '0x00000003098F384E'
    SELECT CONVERT(BINARY(8), @LastTimeStamp,1) AS [:)],convert(varchar(18), convert(BINARY(8), @LastTimeStamp), 1) [:(]

    • This reply was modified 3 years, 3 months ago by  alon_ze.
  • Hey there ,did you manage to find a solution for this? I'm also facing the same issue. Thanks!

  • alon_ze wrote:

    Thanks Federico , yet this seem to not work properly , at least as far as I cloud see:

     DECLARE @TimeStamp VARCHAR(50) = '0x00000003098F384E'
    SELECT CONVERT(BINARY(8), @LastTimeStamp,1) AS [:)],convert(varchar(18), convert(BINARY(8), @LastTimeStamp), 1) [:(]

    you forgot to add the style when converting to binary(8)

    DECLARE @LastTimeStamp VARCHAR(50) = '0x00000003098F384E'
    SELECT CONVERT(BINARY(8), @LastTimeStamp,1) AS [:)]
    ,convert(varchar(18), convert(BINARY(8), @LastTimeStamp, 1 /* this is required in this example */), 1) [:(]

     

  • Right, i did miss that. Thank you Frederico.

Viewing 10 posts - 1 through 9 (of 9 total)

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