1

In SSIS, I'm starting with a SQL Source (a table). It has 3 columns, including a varbinary column ("FileBlob") that comes from a filestream (this shows up as type DT_IMAGE in SSIS).

In the first data flow component, I convert the varbinary column to DT_TEXT, and output the result to a flat file. This works.

Convert DT_IMAGE to DT_TEXT

In the next step, I read in the flat file I just created, attempting to convert the DT_TEXT column back to DT_IMAGE.

Convert DT_TEXT back to DT_IMAGE

I get this error:

The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.

I'm assuming there's a way to do a round-trip conversion of the binary data to text, and then back to binary. Just not sure what I'm missing. Thanks.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ivan Pelly
  • 147
  • 2
  • 12
  • You're not going to be able to write that data to a flat file and get the same results back (the content likely has the same values as your column/row delimiter in it). Instead, you're likely going to want to look at the Import/Export Column task within your data flow – billinkc May 31 '19 at 18:08
  • [Export Column Task](https://stackoverflow.com/questions/12712632/export-varbinarymax-column-with-ssis/12713897#12713897) sample q&a – billinkc May 31 '19 at 18:09
  • I don't think the Export Column task is what I'm looking for. In this case, the binary does represent files, but that won't always be the case. I want the binary (as text), and the other columns, in the flat file, and be able to make the return journey later. Also, it appears that in the conversion from binary to text, the text is a literal representation of the binary... e.g. SQL's 0x0110AAFF for example shows up in the flat file as "0110AAFF", so this wouldn't ever clash with the delimiter. – Ivan Pelly May 31 '19 at 18:31
  • If SQL's varbinary value of 0x0110AAFF is showing up in the flat file as "0110AAFF", what if you added back the "0x" hex prefix before doing the conversion? – digital.aaron May 31 '19 at 18:57

0 Answers0