DB2 to Sql Server Data Transfer using SSIS

  • Hi,

    I am trying to transfer data from IBM db2 to sql server 2005 database. I ue IBM oledb driver for db2 for connecting to db2. But when i run the package starts running but during its pre-execute stage package gets cancelled. I couln't find the reason.. Can anybody help me?

    Thanks.

  • Hi,

    Check the version of the DB2 oledb driver, it's probably an early version that is not *quite* compatible.

    I remember having problems when 2005 was first released with DB2 SSIS extraction. Google "DB2 OLEDB drivers" and try to get hold of the latest one; in the end I had to buy a 3rd party driver to get it to work.

    Good luck and sorry it's 4 years ago so I cannot remember much more about it than that!

    Richard

  • If you are in the Enterprise edition there is free Microsoft OLE DB driver for DB2 and check the thread below for the IBM ODBC driver it also works.

    http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

    http://www.sqlservercentral.com/Forums/Topic734388-148-1.aspx

    Kind regards,
    Gift Peddie

  • Hi,

    Thanks for your reply. The version of db2 i am using is 8.1 and it is the latest one. When i try to connect in db2 control center its connecting and also if i try to create a package using import wizard in sql server management studio the package is getting created perfectly. But if i used BIDS then its giving me the above problem. i thought the password might not be saved and added a config file and saved the password. But then too it gave the same errors. can you suggest me some fix for this?

  • Read the thread I posted in the previous post the last two posts to that thread show alternative places to store the password try both solutions and post again.

    Kind regards,
    Gift Peddie

  • You might also need to check with your AS400 administrator.

    Could be a permission issue on that end.

    Greg E

  • Hi,

    Thanks for ur reply.

    I am not using AS400 for connection to db2.

  • Hi,

    When you created the package using import wizard, could you save the package to a file location and try opening it up in BIDS. While using the wizard, towards the end you have two check boxes:

    Execute Immediately and Save SSIS package. Trying Saving the SSIS package to the file system.

    I thought it would worth a try since it works from the wizard.

    Thank you

  • Some data types do not map correctly in SQL Server. Check the mapping before you start the import process.

  • smriti.subramanian (8/15/2009)


    i thought the password might not be saved and added a config file and saved the password. But then too it gave the same errors. can you suggest me some fix for this?

    What is the protection level of the package?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Set up a Transaction Replication between Sql and DB2 . We are currently doing the same in one of our project. and it is working fine. and try using db2oledb provdied as a connection string to connect between db2 and sql.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply