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.