Export Data from IBM DB2 iSeries to SQL Server

By:   |   Comments (8)   |   Related: > Other Database Platforms


Problem

One of our clients has an IBM DB2 for iSeries ("IBM DB2 for i") database. Connecting using a traditional DB2 ODBC driver does not work for this type of database platform. I need to move some data from the iSeries to a staging database on SQL Server using SSIS, how can I connect?

Solution

In this tip we will provide the steps to install a data source driver and to configure an ODBC connection to the IBM DB2 for i (iSeries). We will also show an example SSIS (SQL Server Integration Services) package that exports data from the iSeries to a SQL Server database.

Driver Installation

In order to connect to the iSeries from SQL Server you have to make sure that an ODBC driver is installed on the server. You can also install it on your development PC if you need to perform ad-hoc data queries or data extracts.

After downloading IBM i Access, start the installation by double-clicking on the cwblaunch.exe file in the driver installation folder:

Source Files

Select setup language:

Select language

Accept the terms in the license agreement on the next screen.

Select destination folder for the installation on the screen after that.

Select the primary language:

Select primary language

Choose the custom setup type on the screen below:

Select setup type

Click the "Next" button on the following screen:

Select setup type

Select the Data Access Providers and clear the selection for the "System i Navigator" and the "Data Transfer Base Support":

Custom setup

Complete the installation by using the default settings on the remaining screens.

Important! Make sure you understand the licensing requirements for the IBM i Access for Windows. For example, ODBC and other data source providers are free, but the "Data Transfer Base Support" requires a separate license. To copy data we only need the ODBC driver, the "Data Transfer Base Support" is not required.

ODBC Data Source

Open the ODBC Data Source Administrator and click "Add" under the "System DSN" tab:

ODBC Select Driver

Select "iSeries Access ODBC Driver" and click "Finish".

Now we will configure the ODBC connection's settings for the iSeries database.

Specify the data source name under the "General" tab and enter the host name of your iSeries as "System":

ODBC Configure Driver - General TAB

Specify the Default Schema or Library under "Server" tab and enable "Override default database" option:

ODBC Configure Driver - Server TAB

Note, that settings may vary depending on your environment. You may need to contact your iSeries administrator for the correct settings. One of the examples (but using the older driver) could be found here.

Enter the package library name under the "Packages" tab:

ODBC Configure Driver - Packages TAB

Click "OK" button to save the new ODBC data source.

Data Export with SSIS Package

We will create the new SSIS project in Visual Studio 2012. You can also create the new project using "SQL Server Business Intelligence Development Studio" or use one of your existing projects.

In Visual Studio's Solution Explorer create the new project's Connection Managers (for both - SQL Server and iSeries):

New Project's connection

Select "Connection manager for ODBC connections" to create the new connection to the iSeries database:

Use ODBC Connection Manager

Select the "DB2 DEV" ODBC connection that was created earlier and enter the password under the "Login information":

ODBC Connection Manager Configuration

Review the new data connection and click "OK" to save it:

ODBC Connection Manager review

To create the SQL Server Connection Manager select the "Connection manager for OLE DB connections". Enter the server name, credentials (if SQL Server Authentication is used) and a staging database name (the database that will hold the exported data):

OLE DB SQL Server Connection

Click "OK" twice to save the SQL Server Connection Manager.

Now we have two Project Connection Managers:

Project with Connections

Alternatively you can create package level connections. Read more about SSIS connection managers here.

Create the new package under SSIS Packages.

Open the package and drag the "Data Flow Task" from the toolbox to the "Control Flow" area:

Add Data Flow task to the Control Flow

Double click the "Data Flow Task" and drag "ODBC Source" and "OLE DB Destination" from the SSIS Toolbox:

Configure Data Flow task

Double click and configure the "ODBC Source". Select a table/view name, click "Columns" on the left and click "OK":

Configure Source Details

Drag the Data Flow arrow from the "ODBC Source" to the "OLE DB Destination". Double click the destination container and configure the properties.

Create the new table by clicking the "New" button or use existing table by selecting the table name from the drop down list. Click "Mappings" on the left to complete the columns mappings and click "OK" to complete the destination configuration:

Configure Destination Details

Read more tips about SSIS Data Flow Transformations here.

Now the package is ready for execution. Run the package and verify the results:

Run Package
Next Steps
  • Read more tips about SSIS Development.
  • Find out more about other database platforms here.
  • See if you can be useful for your company even if you don't support other database systems by bringing data to a SQL Server staging database and building reports.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, December 10, 2020 - 8:31:05 AM - Svetlana Back To Top (87897)
I am not sure what is the best way of doing this as I am not AS400 expert. But you copy data from any data source that can be connected through an ODBC connection with SSIS.

Tuesday, November 3, 2020 - 1:52:50 PM - Phil Back To Top (87750)
Hi is this still a live topic? I am in the process of doing the same AS400 to SQL Server and have never done this before.
Is SSIS the best way still to do this?

Monday, January 22, 2018 - 10:35:55 PM - Phil Back To Top (75009)

 

 Thanks for this post.

Thursday, November 19, 2015 - 12:44:02 PM - helal Back To Top (39104)

Svetlana,

Thank you for the tip and sorry for late response. I have just had a chance to go to this project. The SSIS Export Wizard seems to be working in selecting multiple tables from DB2 librray. However, when tried to export to SQL, I receive the following message taht "The data type couldn't be assigned to the column "X" in "Micrsoft OLE DB Provider for SQL Server". My quesiton is do I need to create all corresponding Tables and columns in SQL before going thru the Wizard?


Thank Yuo,


Helal

Thursday, October 15, 2015 - 8:54:44 PM - Svetlana Back To Top (38912)

Hi Helal,

 

I am not sure what would be the best practice for migrating entire library, but if I would have this task I would probably use SSIS Export Wizard. It allows you to select multiple tables, so you can select all of the tables in the library. I haven't tried this with DB2, but SQL Server to SQL Server works fine.

 

Thanks,

Svetlana

Tuesday, October 13, 2015 - 12:28:21 PM - Helal Back To Top (38878)

Thank you for great article and very precise instructions. This was a life saver for me since I am not a DBA and was looking for solutions/tools to migrate our DB2 data to SQL and decommission OBM AS400 sooner than later. I followed the steps and was able to migrate one table from DB2 to SQL. My question is how do I migrate a library with all tables in that library and migrate them all in one batch to SQL. One of my DB2 library contains 300 tables. Can I migrate all those tables in one batch to SQL? if not, what's the best practice?

 

 

Thank you again,

 

Helal

Tuesday, October 13, 2015 - 10:48:58 AM - Svetlana Back To Top (38876)

Thanks for your comments, John.

In our case the external table is used only by this process.

I will definitely check the link you provided. It will be useful for the SSIS naming conventions document that I am working on right now.

Tuesday, October 13, 2015 - 9:19:51 AM - John Miner Back To Top (38872)

A very good posting Ms. Golovko.  This fits nicely with a client that I am helping.

I have two comments about the SSIS package.  

First, I always use some type of naming convention on the objects.  I suggest using http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/29/suggested-best-practises-and-naming-conventions.aspx or at least a version that works for your company.

Thus, the two objects on the data flow would be called OLE_SRC_IBM_I and OLE_DST_SQL_SRV.

Second, I always set rows per batch and maximum row size to a value like 10,000.  I only set the lock table option if really needed.  For very small data sets, this does not matter.  However, you are locking the table.  For large data sets, it might. 

Again, you are going against an ETL staging database and maybe you have chosen a simple recovery model.  If this was OLTP database with full recovery model, these settings could excessively grow the log and cause locking issues.

Thanks again for a very nice article!

 














get free sql tips
agree to terms