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

Question

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

gjryan59

Mr or Mrs. 500

Points: 536
More actions
November 9, 2020 at 1:48 pm

#3805939

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?

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

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

ScottPletcher SSC Guru Points: 101073 More actions · Answer 1

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".

gjryan59 Mr or Mrs. 500 Points: 536 More actions · Answer 2

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

ratbak Ten Centuries Points: 1358 More actions · Answer 3

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?

gjryan59 Mr or Mrs. 500 Points: 536 More actions · Answer 4

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".

frederico_fonseca SSCoach Points: 16147 More actions · Answer 5

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)

This reply was modified 3 years, 11 months ago by frederico_fonseca.

alon_ze Grasshopper Points: 13 More actions · Answer 6

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, 2 months ago by alon_ze.

alon_ze Grasshopper Points: 13 More actions · Answer 7

Hey there ,did you manage to find a solution for this? I'm also facing the same issue. Thanks!

frederico_fonseca SSCoach Points: 16147 More actions · Answer 8

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) [:(]

alon_ze Grasshopper Points: 13 More actions · Answer 9

alon_ze

Grasshopper

Points: 13

August 10, 2021 at 8:20 am

#3918345

Right, i did miss that. Thank you Frederico.