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 V5R3Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB ProviderSQL 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 Name4- 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

Any other error messages?

i have a similar problem with my SSIS package .. I cannot use the drivers u mentioned .. Does anyone have clue about it. ??

I too have had zero luck trying to use any of the iSeries OLEDB drivers with SSIS. I have been forced to use the ODBC driver, which works fine. I would assume that the OLEDB drivers would perform better, but obviously haven't been able to test this theory.

Phil, These are the errors which i can see in SSIS progress tab: [OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Thanks, Babar

Is there a SQLDUMPER_ERRORLOG.log file being generated where your package (dtsx) resides? I have had this issue in debug mode. however, calling the packagewith any other method does not result in the error. It is alo isolated when connecting using the IBMOLEDB provider. see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=541734&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=747554&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=542735&SiteID=1

Luckily or unluckily there is no dump file is being generated. I have also tried to execute the package without debugging mode. The same error exists in the command window of package execution utility.

I have little success in narrow down the issue. According to my test runs, this problem only occure if i try to use SQL command mode in OLEDB source and destination. These two controls produces no error with Table or View mode and package executes successfully. :) Unfortunately In Lookup transformation, both SQL command and Table mode produce the same error. But if i specify Table or View Mode and Table (in the database) has no data then no error is produced. As i manually enter some data in the lookup table the lookup transformation fails again. :(

Have you had any success resolving this issue? I am getting the same use-SQL-get-an-error result...

Hello Just wanted to share some info I found after a long research. Many people seem to have this issue and no-one has been willing to sharethesolution and provide a workaround instead.. It looks like IBM has accepted the problem and created a PTF (APAR SE27941) to fix it. I have not tried it, but it really describes the problem. As we are behind loading PTF's on our system, maybe with this post someone will beat me to it and can let us know if it really works. This is the link to the APAR description .. http://www-912.ibm.com/n_dir/nas4apar.NSF/c79815e083182fec862564c00079d117/a802b6eb29d32cac8625726c0041efc9?OpenDocument If you search in the IBM PTF database using the APAR number you will get the details of the cumulative PTF. Good luck.. and let us know if it works... LEO

Hi, IBM has a solution http://www-1.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9 Jaimir G.

jaimir, the link is not a solution, only description of problemsome fix?

Diego, The article explains that it is necessary to apply a "hotfix"on the operating system of as400: "PTFs Available R540 SI26879 7226 " in as400 'pts' is a host fix, this file (SI26879) can be download from the web page IBM Jaimir G.

thank U

I, too, am having this problem, but only on one development machine. It works fine on the server, and also on another similar development machine. I'd suggest they are identical machines, but, obviously, something is different on the one that works. Can anyone shed any light on the problem??? Thanks, Roy