How to make a generic pipeline Oracle => SQL Server

[Christophe]

Hello,
I would like to build a generic pipeline to load data from an Oracle DB to a SQL Server DB.
I have more than 100 queries to load, so I would like to be able to call the pipeline with the Select (from Oracle) and the destination table as parameters.
I already did a pipeline which works well if I have only Varchar in the select.
If I put a timestamp in the select, I get the error “The conversion from UNKNOWN to UNKNOWN is unsupported.”
I there a way to do what I am tring to do ?
Thanks

[mina]

Hi Christophe, have you tried the Create table if not present option on the Insert snap?
The patterns catalog has an example of going from SqlServer to Snowflake

[Christophe]

Thanks for your reply.
My select in the Oracle DB is quite complex (multiple joins) so I use a “Oracle - Execute”.
I don’t know how to implement your proposal.

When I use “Create table if not present”, all fields are created with the same type and size : VARCHAR(32700)
which is not possible in SQL server :
The size (32700) given to the column ‘XXX’ exceeds the maximum allowed for any data type (8000).

[mina]

You are welcome, Christophe. On the Select snap>Views>add a 2nd output view and connect it with the 2nd input view of the Insert snap. That will create the target table using source datatypes. I haven’t tested SqlServer but it should work. The Execute snap does not expose source ddl as an output. In that case, you could try a Structure or Mapper snap prior to the insert (mapping just one column passing the rest) but you may need to check the data types you got in the target.
That’s my 2 cents and I hope more people will share their experiences.

[Christophe]

Hello,
Here is what I try :

1/ With the “Create table if not present” option
Here is what I get :
Unable to create table: “yyy”.“xxx”
Resolution:
Please make sure the connection, table name, and schema are correct
Reason:
SQL [CREATE TABLE …
“CREATED_DTTM” TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
)
]; The name “SYSTIMESTAMP” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
So, the “Create table if not present” option does not work properly.
2/ Without the “Create table if not present” option
It works.

But as my select is not simple as “select * from Table”, I can not use “Oracle - Select” but “Oracle - Execute”.
I had “Oracle - Execute” => “Mapper” => “Generic JDBC - Insert” and I get the error “The conversion from UNKNOWN to UNKNOWN is unsupported.”
I change to “Oracle - Execute” => “Mapper” => “SQL Server - Insert” and it is … OK. :+1: