How to convert Varbinary(10) to a SSIS variable

• Ells

  SSCrazy Eights

  Points: 9901

  More actions

  March 11, 2013 at 10:01 am

  #275104

  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

• Ells

  SSCrazy Eights

  Points: 9901

  More actions

  March 11, 2013 at 11:05 am

  #1595675

  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 🙂

• morbius82000

  SSC-Addicted

  Points: 406

  More actions

  April 25, 2018 at 10:05 am

  #1988223

  Awesome, just what I was looking for. 🙂