Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Extracting VarBinary(MAX) column to Flat File - Facing Issue. Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 1:22 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 7, 2015 8:13 AM
Points: 37, Visits: 144
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 Guide me where I'm doing wrong and help me out to overcome this issue.


___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming
Post #1594797
Posted Monday, July 21, 2014 5:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 38,338, Visits: 35,257
Try NOT using references to Unicode when dealing with binary data, which is no where near being Unicode.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1594880
Posted Monday, July 21, 2014 5:54 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 12, 2015 6:11 PM
Points: 5,467, Visits: 7,659
Derive the column after the conversion and prepend the 0x.

DT_BYTES has a max length of 8000, which won't handle your MAX. Since there's a single binary value you shouldn't need to overengineer the solution.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1594888
Posted Thursday, July 24, 2014 9:34 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 7, 2015 8:13 AM
Points: 37, Visits: 144
Hi Evil,

Thanks for your reply, when i prepend 0x in script it's working. But when i tried the same in SSIS using derived column I'm getting exception(i have attached the screenshot as attachment)



___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming


  Post Attachments 
Capture1.JPG (9 views, 140.64 KB)
Post #1595940
Posted Thursday, July 24, 2014 10:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 12, 2015 6:11 PM
Points: 5,467, Visits: 7,659
windows_mss (7/24/2014)
Hi Evil,

Thanks for your reply, when i prepend 0x in script it's working. But when i tried the same in SSIS using derived column I'm getting exception(i have attached the screenshot as attachment)



Expressions don't use the same quotation components.

You want "0x" + @Column



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1595963
Posted Thursday, July 24, 2014 11:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 7, 2015 8:13 AM
Points: 37, Visits: 144
Yes Evil, 0x+@Column. Currently File is extract with the following data "3C436C696E6963" after DT_Text conversion but it has be prefixed with Hex String 0x3C436C696E6963

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming
Post #1595976
Posted Thursday, July 24, 2014 12:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 12, 2015 6:11 PM
Points: 5,467, Visits: 7,659
windows_mss (7/24/2014)
Yes Evil, 0x+@Column. Currently File is extract with the following data "3C436C696E6963" after DT_Text conversion but it has be prefixed with Hex String 0x3C436C696E6963


You misunderstand. You're using apostrophes (like T-SQL wants) instead of quotes (like Expressions want).

Switch '0x' + Column
to "0x" + column



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1595989
Posted Thursday, July 24, 2014 2:14 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 7, 2015 8:13 AM
Points: 37, Visits: 144
Yeah.. I tried that too.. but getting the same exception

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming


  Post Attachments 
Double.JPG (6 views, 122.46 KB)
Post #1596015
Posted Thursday, July 24, 2014 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 12, 2015 6:11 PM
Points: 5,467, Visits: 7,659
Have you already converted content_document to type TEXT before attempting that? Otherwise, you're attempting to append "0x" to a binary value, which it fails. If you're trying to convert it inline, you have to convert the binary first, so "0x" + (DT_Text)Content_Document


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1596024
Posted Wednesday, July 30, 2014 11:47 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 7, 2015 8:13 AM
Points: 37, Visits: 144
Thanks Guys for your guidance...

I have overcomes this problem using XQuery functionality.


___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming
Post #1597880
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse