SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

windows_mss
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

USA
1782 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)
AS
BEGIN
DECLARE
	@charvalue	varchar(514),
	@i			int,
	@length		int,
	@hexstring	char(16),
	@tempint	int,
	@firstint	int,
	@secondint	int
SELECT
	@charvalue = '0x',
	@i = 1,
	@length = DATALENGTH (@binvalue),
	@hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
	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
END
return @charvalue
END
It's an iterative approach so performance won't be its long suit.
HTH



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  
 New 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.05 seconds. Powered By: Snitz Forums 2000