I have a project that requires that I perform the following: 1. import an Access 2003 database to SQL Server 2008 2. clean up data types/data/column names 3. export the cleaned tables to an Iseries database I used the Miscrosoft SQL Server Migration Assistant 2008 for Access to import the database into SQL Server. I cleaned up the columns and set the data types to ones that would be compatible with Iseries DB2 Where I am running into problems is with the conversion/export of the tables to the Iseries database server. I have never had to do this before, so I have started with creating an SSIS package to perform this function, but am stuck. The SSIS does a great job of moving data, but I need to move the structure as well. I could certainly use some ideas on how to perform the last part of this task.
I take it you already have an ODBC-connection to your iSeries machine. If that ODBC-provider is any good, you should be able to setup the iSeries machine as a linked server and create the objects with SQL. With linked server, you can use AT to execute statements directly into your iSeries machine. Let's say you have a linked server setup called DB2400, you could alter the create scripts from CREATE TABLE schema.tbl (ID int, col varchar(10)) to EXEC('CREATE TABLE schema.tbl (ID int, col varchar(10))') AT DB2400 I'm no iSeries expert at all, I've just done silly little things like connecting to an AS/400 to read a customer-table in a one-off job. Creating objects in iSeries will take much more iSeries knowledge, like knowing how indexing works etc. I'm sure there are tools to migrate to and from iSeries. My colleagues have used Data Mirror to replicate between iSeries and Oracle, but they have only set it up as a one-way connection to read from iSeries. That - unfortunately - seems to be the common task when googling for some hints. If you want more about linked server and iSeries: [
Dave, its likely you will get better answers to this on a Iseries forum (here maybe? :-/ [
http://forums.systeminetwork.com/isnetforums/forumdisplay.php?f=30] ). If you were coming to SQL from Iseries then we would be full of answers as we are SQL focussed here, Iseries isnt something many of us will have experience with. My expectations would be that it should auto sense the majority of data types during its import stage so you should get most of the way there. I would go for a flat file source with a codefile if necessary - the Iseries equivalent of BCP. Alternately can you script the source database and then convert the T-SQL to Iseries format and build the destination database that way? :