Strange Problem with IBMDA400 OLE DB Provider

• Babar Shahzad

  SSC Veteran

  Points: 270

  More actions

  January 19, 2007 at 8:15 am

  #115412

  Dear All,

  My objective to use SSIS to to pull data from DB2 and store into SQL Server.

  Working environment is:

  ===========================

  Database: DB2 UDB iSeries V5R3

  Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

  SQL Server 2005 SP: Latest i-e SP2

  In connection manager, i have changed the following properties in OLEDB driver settings and connection with DB2 is tested successfully.

  1- CATALOG LIBRARY LIST = AS/400 library

  2- PERSIST SECURITY INFO = TRUE

  3- INITIAL CATALOG = Database Name

  4- Defualt Collection = AS/400 library

  After testing connection, I Created an OLE DB source and in OLE DB Datasource Editor set following properties:

  1- VALIDATE EXTERNAL METADATA = FALSE

  2- ALWAYS USE DEFAULT CODE PAGE = TRUE

  In OLEDB source editor, if i use "Table or View" name option then the drop table list is populated perfectly and i can specify any table. With this option Package executes fine. But if i use the SQLCommand and specify the query, it prase the query correctly and i can preview the data in this source editor. But upon execution package return the following error message.

  [OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

  There is a workaround of this problem i-e to use Datareader in source but i am interested to go with OLEDB.

  Can anyone help in this regard.

   

  Thanks in Advance

• January 22, 2007 at 8:00 am

  This was removed by the editor as SPAM

• BJ Custard

  SSC Rookie

  Points: 41

  More actions

  January 23, 2007 at 10:00 am

  #684986

   The configuration steps are as follows...

  1. Create a Data Source for the AS/400, using provider NATIVE OLE DB\IBM DB2 UDB FOR ISERIES IBMDA400 OLE DB PROVIDER, WITH THE FOLLOWING CHANGES

     • CATALOG LIBRARY LIST is the AS/400 library
     • USER ID should be populated
     • PASSWORD should be populated
     • PERSIST SECURITY INFO should be TRUE
     • DATA SOURCE should be your machine DNS name (tho IP might work)
     • INITIAL CATALOG should be the actual machine name (mine is populated when I click on the dropdown)

  2. Create a Connection Manager for the above data source
  3. Create an OLE DB source/destination...

     • OLE DB Connection Manager from above
     • Data access mode = Table or View (or SQL Command for a OLE DB Source)
     • Name of Table/View should be <Catalog Library List>.<Table>.  If you config is correct, this dropdown will contain a list of them to select from...
     • On the 'Advance Editor' screen, under Component Properties, set

       • VALIDATE EXTERNAL METADATA = FALSE
       • ALWAYS USE DEFAULT CODE PAGE = TRUE

  4. We had to turn on JOURNALLING on the DB2 tables to write to them.  (This may or may not be a requirement...)

  Hope this helps...