I am importing data from a csv file into a SQLSERVER table

I am having problems with a field in the destination table which is of type
binary.

The source data in the csv file is a Hex representation of the binary data.
e.g.
0x000386006bc886a0

An exception is being raised - General conversion failure pair 1(source
column 'Col001'(DBTYPE_STR), destination column 'Id' (DBTYPEWS_BYTES)

I have read in the help that if the destination column is binary no
translation occurs.

Is there ay way to get around this

thanks

Andrew

In message <A000EC31-437B-4688-81A1-AFED7EA9FFE0 (AT) microsoft (DOT) com>, Andrew
<Andrew (AT) discussions (DOT) microsoft.com> writes
I'd guess that the conflict is around a string to byte conversion. So
you should either store the hex representation in a string/text field,
or convert it back to byte data as part of the transform.

Depending what type of data this is and if you have control over both
ends of the process, you may find the Read and Write file
transformations of use, and skip the whole encoding nightmare.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

I am having a similar problem how would I go about converting a hex
representation into byte data?

Hi!
I have exactly the same problem due to a customer that uses binary(8)
as a identity column :-(
The approach I have tried was to use the "use a query" option in the
DTS wizard and then utilize DTS query builder. Then change the query to
do the conversion with CONVERT while fetching the data!
This sure look to mee as viable way of achieving the conversion,
especially since the parse button functionality says the sql is ok. But
when I run it (or preview in the next step in the wizard) I get a error
message from Microsoft JET Databas Engine that says "Undefined
function 'Convert' in expression".
How does JET come in to the equation? Is this approach an cul-de-sac or
can I enter some JET SQL, that is comparable to TSQL CONVERT, that does
the conversion? How would that look like then?

best regards
Olaf