Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to convert Varbinary(10) to a SSIS variable Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 10:01 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:13 AM
Points: 702, Visits: 766
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
Posted Monday, March 11, 2013 11:05 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:13 AM
Points: 702, Visits: 766
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 »

Add to briefcase

Permissions Expand / Collapse