How to make a generic pipeline Oracle = SQL Server

Question

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 · Answer

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 · Answer

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 · Answer

You are welcome, Christophe. On the Select snap&gt;Views&gt;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 · Answer

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” =&gt; “Mapper” =&gt; “Generic JDBC - Insert” and I get the error “The conversion from UNKNOWN to UNKNOWN is unsupported.”
I change to “Oracle - Execute” =&gt; “Mapper” =&gt; “SQL Server - Insert” and it is … OK.  :+1:

mina · Answer

Awesome. For the Timestamp issue, I found the answer in SQLServer Insert snap doc. It called out:
TIMESTAMP datatype in SQL Server is not supported. According to the Microsoft official document, "The timestamp syntax is deprecated.

Forum Discussion

How to make a generic pipeline Oracle => SQL Server

7 Replies

Recent Discussions

Javascript to promote top level lists

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?

Multipart Reader failure - 'content-type' was not found