Strange Problem with IBMDA400 OLE DB Provider | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Strange Problem with IBMDA400 OLE DB Provider

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,
Babar
If you are using the SP2 on the production server then it is better not to use as it is still in CTP stage.
Also check for any update to the DB2 driver in the IBM arena in this case, the error looks like the permission issue in opening the package with the specified driver at client’s end. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Has anyone found a solution to this problem? I am having the exact same problem, and after several days of uninstalling HIS, reinstalling the IBM drivers and pulling my hair out, I am at a loss.
Funny thing is other DBA’s can run a working package on their machine that does this, and on mine it fails. We found this after I continued to get this error working on a new package. This tells me it is definitely something on my machine, but I can’t find it! Going to ODBC connections are not an option; the entire ETL structure we built for this data mart using this current approach.
]]>