Map data types and data structure dynamically from Oracle to MS-SQL server RRS feed

  • Question

  • is a hard nut to crack. Background: I have created ETL packages in SSIS moving data from an Oracle database source to a MS-SQL server database destination. Right now I'm just doing a 1-1 load with no transformation. However sometimes the packages fail, especially if there is an extra column added to the Oracle table or a datatype is changed in the Oracle table then the load fails. That leads me to the following two questions:

    1. Is it possible in SSIS to count the number of columns in source and destination tables (I've gotten this far) and if there is a discrepancy drop the destination table and recreate it a new table in SQL server based off the DDL from the Oracle table? The challenge here is 1) the datatypes in Oracle will have to somehow be converted to T-SQL data types and 2) I'm not even sure this is possible in run-time mode.

    2. Same as above just for data type discrepancies between my source Oracle table and my destination SQL table, meaning if there is a data type mismatch between the source and destination data types then either ignore it or map it correctly.

    I need this package to run as a scheduled job from SSMS.

    Wednesday, August 26, 2015 7:53 PM

All replies

  • Both #1 and 2 are only possible if you write a program that will "count the number of columns in source and destination tables" and the rest and then generates the package. Upon a discrepancy found needs to remove the old package and put in place an updated copy.




    Wednesday, August 26, 2015 9:09 PM