Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Extracting VarBinary(MAX) column to FlatFile
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 07/21/2014 :  15:29:19  Show Profile  Reply with Quote
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 [1252]]: 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 help me out to overcome this issue.

Bustaz Kool
Flowing Fount of Yak Knowledge

1834 Posts

Posted - 07/21/2014 :  19:32:36  Show Profile  Reply with Quote
I can't find the original link (frankly, I haven't tried REAL hard) but this is some code from MS that converts varbinary to a hex string:
CREATE FUNCTION [dbo].[fn_hexadecimal](@binvalue varbinary(256))
RETURNS varchar(514)
	@charvalue	varchar(514),
	@i			int,
	@length		int,
	@hexstring	char(16),
	@tempint	int,
	@firstint	int,
	@secondint	int
	@charvalue = '0x',
	@i = 1,
	@length = DATALENGTH (@binvalue),
	@hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
	SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
	SELECT @firstint = FLOOR(@tempint/16)
	SELECT @secondint = @tempint - (@firstint*16)
	SELECT @charvalue = @charvalue +
		SUBSTRING(@hexstring, @firstint+1, 1) +
		SUBSTRING(@hexstring, @secondint+1, 1)
	SELECT @i = @i + 1
return @charvalue
It's an iterative approach so performance won't be its long suit.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000