โ06-23-2021 06:35 AM
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
โ06-23-2021 07:37 AM
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
โ06-23-2021 08:44 AM
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).
โ06-23-2021 01:48 PM
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.
โ06-24-2021 01:01 AM
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: