How to convert Varbinary(10) to a SSIS variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » How to convert Varbinary(10) to a SSIS...

How to convert Varbinary(10) to a SSIS variable

Rate Topic

Display Mode

Topic Options

Author

Message

Ells

Posted Monday, March 11, 2013 10:01 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:06 AM
Points: 379, Visits: 676

Peeps,
just ground to a halt on one package I am creating. I need to select a varbinary(10) value from an execute SQL task and store that value as a package variable.

So pop an Execute SQL Task down and store the result set as a package variable (string type).

When I execut the SQL in SSMS I get 0x000268CF000304510001

When I run SSIS in debug mode and watch the variable through the locals window I get 000268CF00

it seems to chop the front (0x) and the back(0304510001)?

Execute SQL task contains the Query
select sys.fn_cdc_get_max_lsn() as Banana

Then we use the result set to move Banana into the string package

Am I missing something here??
Thanks
M

Post #1429365

Ells

Posted Monday, March 11, 2013 11:05 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:06 AM
Points: 379, Visits: 676

After googling substantially before hand I stumbled across a blog linked to a codeplex sample. Trawling thorough that I got to

EXECUTE SQL TAsk contains:

declare
@startLSN binary(10),
@startLSN_str nvarchar(42)
select @startLSN = sys.fn_cdc_get_max_lsn()
select @startLSN_str = upper(sys.fn_varbintohexstr(@startLSN))
select @startLSN_str as PkgLSNEnd

This then returns data to a string that I can put in a package String variable.

They dont make this easy :)

Post #1429408

« Prev Topic | Next Topic »

Permissions