Load Hexadecimal value into an Image column

Question

Load Hexadecimal value into an Image column

balasach82

SSCrazy Eights

Points: 9653
More actions
May 11, 2011 at 8:14 am

#236004

How to load Hexadecimal value into an Image column using either ssis 2005/DTS?
We are converting an pdf file into bytecode/byte array and then trying to load that into an 2005 table column with image datatype

Viewing 5 posts - 1 through 4 (of 4 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

If you're using SQL2005 you should not be using the IMAGE data type...use VARBINARY instead.

You can convert a hex string to VARBINARY using the CONVERT function...make sure you pay attention to the Style parameter.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 2

We have to use Image datatype; thats the requirement. So if is there a way to load bytecode into image field by any means?

And what is the Style parameter you mentioned?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

balasach82 (5/11/2011)
We have to use Image datatype; thats the requirement. So if is there a way to load bytecode into image field by any means?

IMAGE is a deprecated data type. Microsoft recommends against using it in new development. See this article for more information:

http://msdn.microsoft.com/en-us/library/ms187993.aspx

Sorry, I do not know of a way to manage data within the IMAGE data type as you need it. I would make sure my requirements-provider(s) understood that before moving forward with that data type and try to move towards using VARBINARY.

balasach82 (5/11/2011)
And what is the Style parameter you mentioned?

See the section on "Binary-Character Conversion" for the practical usage of the Style parameter of the CONVERT function when converting from strings to binary types:

http://www.sqlmag.com/article/tsql3/sql-server-2008-s-t-sql-features/2

Also see the "Binary Styles" section in this reference article for the CONVERT function:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

balasach82 (5/11/2011)
How to load Hexadecimal value into an Image column using either ssis 2005/DTS?
We are converting an pdf file into bytecode/byte array and then trying to load that into an 2005 table column with image datatype

How do you do the conversion of the pdf file into byte array? Do you use the standard 'Import Column" transformation to load the file into a data flow column? Then inserting the data should be only a matter of connecting the imported column into the destination table column.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/