Extracting VarBinary(MAX) column to FlatFile

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

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Extracting VarBinary(MAX) column to FlatFile

Author

Topic

windows_mss
Starting Member

1 Post

Posted - 2014-07-21 : 15:29:19

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
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-07-21 : 19:32:36

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:[CODE]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[/CODE]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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources