Store varbinary data result into SSIS variable through Execute SQL Task
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works. 1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object. 2. Execute SQL Task 2 - update tableB set columnB = ? What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result. Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary? Thanks, -Ash
February 13th, 2008 3:59am

Hi there, have you tried using an ADO connection type rather than oledb or ado.net? If you use ADO, you get the option to specify that your parameter is of type adVarBinary. This may help your issue... Hope this helps.... Sam
Free Windows Admin Tool Kit Click here and download it now
March 1st, 2008 4:13pm

Ash_,You cannot reference and object variable in an Execute SQL task directly. Perhaps you could look into using a Dataflow. See this sample:http://rafael-salas.blogspot.com/2008/03/ssis-sample-package-to-retrieve-and.html
March 1st, 2008 9:24pm

Hi Sam, Thanks for the info. I've not used ADO, will let you know when I do. -Ash
Free Windows Admin Tool Kit Click here and download it now
March 4th, 2008 9:15pm

Hi Rafael, Thank you for looking into this issue. It's great info that you provided the test package. -Ash
March 4th, 2008 9:18pm

I had a similar issue where I 'had' to populate a variable with a varbinary for comparing records. Defining the variable as a string andpre-fixing with a value of'0x' did the trick. - Subah
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2008 10:48pm

This is of real help
August 12th, 2008 10:09am

I know this question has already been answered, but I wanted to share something related so that someone facing this situation knows what to do. Per BOL, if you are using SSIS 2008, you can store the value of a VARBINARY SQL data type into a DT_BYTE SSIS data type and then use it as-is in the subsequent SSIS components. The URL that defines SQL-to-SSIS data type mappings is here: http://msdn.microsoft.com/en-us/library/ms141036%28v=SQL.100%29.aspx Hope this helps. Cheers!! Muqadder.
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2011 5:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics