Inconsistent result using Data Reader source to get data from DB2 RRS feed

  • Question

  • I am having a very wierd issue regarding a DB2 sql query. I need to get data from Db2 and insert into our sql server database. Using data flow task, to get data I am using the data reader source. COnnection is ado.net\odbc connection.

    THis sql query also has some comments in it.


    The first wierd thing is...


    1. On Development server, when I run this query manually, meaning using toad, winsql (connection to the db2 database), the query runs fine. Brings back approx 667 rows which is correct. ON the same server when I try to run this query, via a SSIS pkg, data flow task, using data reader source, gives me error on those comments that exist in that query. But if I run the same SSIS pkg on another server (Integration server). It runs fine. The same pkg also runs fine if I run it from my machine. SO What is different on my Dev server compared to  the Integration server.


    2. Say if I take those comments out from the sql query, then try to run the ssis pkg. The query is stuck at the first record and goes in an infinite loop mode. though my query is not a procedure, it is just a sql statement. But this ssis pkg with the query runs absolutly fine on the other server. I aslo tried using the other types of connection and ole db source but still the same problem on the Dev server.


    What do I need to look for that is so different on the dev server compare to the INT server. I also checked the version on both these server for Visual Studio 2005(by going to About Microsoft Visual Studio), it is the same.


    This is what I have on both the servers....

    Microsoft SQL Server Integration Services Designer
    Version 9.00.3042.00



    Please HELP !!!!


    Thank you.


    Tuesday, April 22, 2008 3:04 AM



    Thanks Phil, Installing a different version of teh IBM DB2 ODBc driver did the trick.

    Thank you.

    Thursday, May 1, 2008 1:14 AM

All replies

  • Check that the DB2 drivers between your servers is the same.  I suspect they are not as SSIS relies on the driver provider to enforce rules such as allowing comments or not (or the ability to correctly parse them, anyway).
    Tuesday, April 22, 2008 3:25 AM
  • Thanks Phil. How do I check for the DB2 drivers. Please let me know. I tried to look for that yesterday but wasn't susscessful.



    Tuesday, April 22, 2008 2:16 PM

    If they are ODBC drivers, you can go to the ODBC control panel item and look at the driver versions there.


    If it's an OLE DB driver, you'll have to look somewhere else -- not sure where -- perhaps in Program Files or something like that.  I'm not real sure.

    Tuesday, April 22, 2008 2:19 PM

    Thanks Phil, Installing a different version of teh IBM DB2 ODBc driver did the trick.

    Thank you.

    Thursday, May 1, 2008 1:14 AM