Problem with a table copy with Dynamic schema

Six Stars

Problem with a table copy with Dynamic schema

Hello everybody !

 

I'm trying to simply copy a table from a MS SQL Server database to another, using the Dynamic column type. 

 

Source is a v11

Destination is a V12

 

Error message is "the implicit conversion from Varchar to Varbinary is not allowed" (sorry if the translation is not perfect)

Since I'm using the same Dynamic type on both sides, I don't see why the types would not match. And I  used this Dynamic type on plenty of other transfers without any problem. But maybe never from this source. Did any of you have the same problem ? To create the destination table manually with some column conversion is quite time consuming (loooots of columns), the Dynamic trick is great to avoid editing the transfer each time a column is modified. So it would be great to be able to use it. 

Highlighted
Eight Stars

Re: Problem with a table copy with Dynamic schema

Hello,

 

Try to use the convert function during select. Eg.

SELECT CONVERT(VARBINARY(25), '0x9473FBCCBC01AF', 1);

Where you change the second parameter with appropriate column name. Hope it helps.

 

Regards

Lojdr

Six Stars

Re: Problem with a table copy with Dynamic schema

hi Lojdr

 

Thank you for your answer ! 

 

But the thing is that i'm using the Dynamic column solution, to avoid specifying the columns each time (so i'm just adding one "Dynamic" type column, on both ends). It's great to avoid editing the job each time a table is edited. Otherwise I'm indeed converting the data for tables with more specific columnbs, and it's working well. 

Four Stars

Re: Problem with a table copy with Dynamic schema

did you ever find a solution on this one? Im doing pretty much the same thing. using a dynamic column to pull from mssql to a flat file and for some tables it gives conversion errors.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download