Issue while converting string to a varbinary in SSIS

Question

I'm running the following SSIS package:
/api/attachments/239998-ssis.png?platform=QnA
The process covers the following datatypes:

Excel datasource: Unicode String [DT_WSTR], Length :255
Data Conversion: image [DT_IMAGE]
OLE DB Destination : Image [DT_Image] (The corresponding column in the SQL table is max(varbinary))

For some reason I am getting the following error:

The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Clearly, the DT_IMAGE datatype is longer than DT_WSTR, so why am I getting the potential loss of data error? I tried DT_TEXT and DT_NTEXT in the conversion task, but no luck.

Answer 1

Did you try using DT_Bytes instead of DT_Image? The DB column type is VARBINARY right?

Not sure what you're storing in the string but it is unlikely to be something that you'd store in VARBINARY in the DB. I question this conversion in general. In many cases if you have binary data in a string then it is base64 encoded. Therefore you'd have to go through the intermediate step of decoding it first. If you don't then your binary data is wrong. Attempting to take a Unicode string and store it directly as binary in the DB would also make it effectively garbage unless somebody knew to convert it back to a string but that isn't going to work properly I believe.

Answer 2

DT_Bytes is large enough to hold the 512 bytes that are needed for your DT_WSTR value. Storing it in a larger type doesn't change anything. Have you tried using that type and gotten an error? If so then what error?

I don't know how it worked before because a string is not something storable in DT_Image. Again, baffled as to why you would do this but store the value into a string. Then do a transform to convert it to binary. Then store the resulting value into your binary column. I suspect the error is because it doesn't like converting directly from string to binary (because it doesn't make sense).

Answer 3

Hi @olpl1543344 ,

Could the issue be related to your source data?

I could also re-produce the issue, and then I configured the error output to redirect the rows.

Some of the columns could be converted successfully and the other will thrown the error

The data value cannot be converted for reasons other than sign mismatch or data overflow.

You may also redirect the error flow to some destination and put a data viewer on that path to see which value can't be converted and then do further analysis.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.