I’m extracting data from the table to flat file using SSIS,during this process I’m facing the data conversion issue in the varBinary(max) data column. To overcome this issue I have used Data Conversion in my data flow to convert to varBinary(max) to DT_NText but I’m getting the following error,
Error at Patient Documents Data Flow Task [Flat File Destination ]: The data type for "input column 'MYCOLUMNNAME' (1355)" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.
Error at Patient Documents Data Flow Task [SSIS.Pipeline]: "component "Flat File Destination" (1252)" failed validation and returned validation status "VS_ISBROKEN".
Error at Patient Documents Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Patient Documents Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)
So I have used TEXT type in data conversion but I'm not getting the Binary value "0x" is discarded,
DB Content = 0x3C436C696E69 (It's Part of the content)
After Data Conversion from VARBINARY(max) to Text "3C436C696E69" , "0x" is missing. Exactly I need to extract the varBinary data to flat file as it is like in DB.
Kindly Guide me where I'm doing wrong and help me out to overcome this issue.
___Known Is An Drop,Unknown Is An Ocean....