Db2 Connection Problems SSIS (OLEDB)

  • I'm using the IBM DB2 connect client software (Version 8.2) and SQL Server 2005. In the SSIS package I have defined a connection to the database and I have tested it successfully. If I choose "Table or view" as Data access mode in the OLE DB Source monitor all works fine. If I choose a select statement it doesn't work. After I run the package I see a command line window for only a second and afterwards the package runs into an error. Does anyone have an solution for this?

  • Have you tried the OLE DB Provider for DB2 available in the feature pack? http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

  • I haven't tried that. Thanks for the Info.

    Uli

  • I have just tried to install the MS OLE DB2 driver on our SQL server, but this doesn't work because we are using the Standard Edition of SQL Server2005. Any other ideas?

  • Hi

    I have just hit the same problem

    How do you write to DB2 using SSIS?

    And yup I also do not have enterprise or developer edition of 2005.

    doh!

  • Uli,

    Look at this article:

    http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

    This will tell you what you get with your edition of SQL.

    From what I can see at a glance, you get the basic transforms, but not the advanced transforms with Standard Edition.

    Will your normal ODBC driver not be populated in the OLEDB list? Havent been priviledged enough to pull from DB2 in my SSIS packages, but it shouldnt be that difficult?

    Considered using a linked server with openqueries?

    Sharon, what edition of SQL do you have?

    ~PD

  • Thank you for your help.

    I have defined an ODBC connection which I can choose inside the SSIS package. What I haven't done is to define a linked server.

    But apart from that if I define a OLE DB source in the SSIS package and put a select statement in the box the preview works fine. I can see all the data from the DB2 database which I want to import into my SQL DB. But when I run the package I see the command line windows for a second and afterwards I get an SQLdumperror file on my Desktop.

    Uli

  • I have a DB2 assignment which I have to start on Monday, so will get the drivers loaded shortly and be going through some similar pains.

    Will post as soon as I understand a little more

  • Part of your decision will come from where your DB2 database is hosted (AS/400 or Linux). Each has its own method of connection. I have worked in both environments and in each case, I have setup a linked server. For the AS/400, I loaded IBM's iSeries software on my SQL Server and setup and ODBC connection. In SQL, I created a linked server using that ODBC connection.

    The Linux environment was a bit more complex. Before I left that employer, I had it working using IBM's software for DB2 version 9.5. I used it to create the ODBC connection and created a linked server in SQL. The problem I ran into here was that it needed to operate INPROCESS with SQL so my SQL box eventually came to a crawl and had to be restarted. My successor used the Microsoft DB2 drivers and was able to get it working successfully, both reading and writing to DB2.

    All of this was done using SQL Server 2005 Standard Edition. Enterprise edition is NOT required.

  • Hi

    I am using the AS/400, also with IBM iSeries.

    Where did you get the iSeries software for your SQL server? and is it 2005?

    My version of SQL2005 is Standard Edition 9.00.3054.00

    SP2

    I did setup a linked server using ODBC connection, but when testing doing an update to it, it waited a while then said:-

    [font="Courier New"]"The OLE DB provider "MSDASQL" for linked server "JDEODBC1" reported an error. The provider ran out of memory.

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "JDEODBC1"[/font]

  • You'll probably need to contact your IBM account rep to get the iSeries software if you do not already have it. As far as I understood, it was part of our maintenance agreement with IBM that they shipped out new versions as they were released. I don't remember the last version we used.

    Once you have that installed, you'll create an ODBC connection using the drivers included. Then, create the linked server for the new ODBC connection. Both the ODBC connection and your linked server need to have the user/password entered that has appropriate rights on DB2.

    The final step is to turn on logging on the appropriate members on DB2. If logging is not turned on you will get an error when trying to write to DB2. If you are only needing to pull data from DB2, this is not necessary.

    The final gotcha - any connection to DB2 will ONLY be able to access the first member of each file. So, you'll need to make sure that is setup correctly in DB2. Or, you'll need to use aliases on DB2 (however, I do not know if aliases will allow you to write back to DB2).

  • Forgot to mention, your version of SQL Server is just fine for this. It does not require Enterprise edition to work.

  • And yesterday we did try and install Microsofts' new OLEDB for DB2 connector and it straight away came up with "this is the wrong version of SQL........"

    Doh

  • At the time I had to do this, Microsoft didn't offer any tools outside of some specialized server that they wanted $10K for and we didn't have the money to do it. I had to make do with what we already had which was the iSeries software. One benefit I later discovered was that the iSeries Navigator proved to be an extremely valuable tool for finding out how DB2 was setup. I was able to browse the database in an IBM native world so I could see if the problem was on that side or in my code in SQL.

    All that to say, I wouldn't put much effort into the Microsoft tools since the IBM tools work just as well or better.

  • Dear Old Hand,

    How do I check the AS/400 to see if the ODBC drivers are there ?

    Is there a command to view ?

Viewing 15 posts - 1 through 15 (of 16 total)

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