asked
DB2 UDB to SQL Server 2012 using SSIS
Hello: I am new to SSIS and I desperately need your help! I need to move 10,000 tables from a DB2 database (9.7.4) to SQL Server 2012 along with their indexes (if possible) as well as data. I have figured out how to migrate the tables using data flow and OLE DB Source / Destination. It works great but at this rate it will take me forever to migrate thousands of tables. Does anyone know how to do this using a script or variables to pass the table name into SQL ? perhaps a loop of some kind. There is one issue that is a problem and that is the data type of datetime needs to be mapped to datetime2 in SQL Server - otherwise the data load portion fails (I have already tested this). This is easy to fix when I move one table at a time, but somehow I need the package to do that for me if I am able to migrate thousands of tables. The SQL on DB2 to grab the table name is: SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'PSADMIN' and type = 'T' Any suggestion would be GREATLY appreciated.
Have you tried the SQL Server Import and Export Wizard? You can get to it by right-clicking on your database, then choose Tasks, and then choose Import Data. I'm not sure whether or not it will handle your data types correctly, but if not it still might be easier to use the wizard and then convert data types after importing into SQL Server. Of course, if this will be a recurring task with a lot of data, copying it to a temp location and then copying again for data type conversion might not be an option. Since you said "move", I hope that's not a problem for you. We had to import hundreds of tables from Oracle and ran into data type (and other) issues, so I wrote a small C# app that generated the data flows based on input from text files generated from Oracle SQL scripts I also wrote. It can be done if necessary, but the wizard method is definitely the preferred route. The main reason we couldn't use the wizard was because we wanted to use the Attunity drivers for all non-blob data. It proved much faster than the standard Oracle OLEDB drivers, and that ETL needed to run daily. Data type mapping was a secondary reason because my custom Oracle scripts generated the proper CREATE TABLE statements to be executed in SQL Server. The indexes can be scripted. I'll see if I can post the script I wrote.
Thanks Tom... I like your C# idea. Yes, I have tried the wizard, but it bombs because of the data type. It appears as "unknown' data type for all the datetime and I can't go through 10,000 tables and change them manually. I was looking for something that says "if datatype is this....then do that..... " Like you said, a program approach may be better. Perhaps SSIS can't do what I am asking without a program of some sort. It would be so much easier with fewer tables, but this is a PeopleSoft application and there are thousands of tables.
That sounds like part of the problem we had. I'm trying to find out whether or not I'll be able to post some of my code here or maybe get it to you another way. It was written for Oracle, but perhaps it would be a good starting point for you.
15 People are following this question.
