How to View AS400 library Files from OLEDB Source (SSIS)

HI,

I'm trying to get data from AS400. using OLEDB source as my connection. i'm using IBM OLEDB provider for iSeries. and working on standard edition of SQL Server 2005.

While using OLEDB source task when i set my access mode to 'table or view' and try to see list of available libraryname.tablenames, i do not get and tables

where as when i use Data access mode as 'SQL Command' i can get data (can only see preview of data) from AS400 but not able to insert that into my destination table. At run time task Fails with the error mentioned below.

I have configured Data links tab inside the OLEDB connection manager also, but when tried to set a default library it gives me error. : "Error code :CWBZZ5042" - ( catalog is invalid ) but it does exist.

Is there some settings that needs to be done from AS400 side or SQL Server side to view the available libray and its tables ?

Can some one help me on the same.

thanks in advance

Shah

Error Message received when executed with SQL command:

Error: 0xC0202009 at Data Flow Task, OLE DB Source [1]: An OLE DB error has occurred. Error code: 0x80040E00.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Task failed: Data Flow Task

Shah,
PLEASE stop reposting your same issue over and over again.|||

HI Phill,

I'm Not posting the same query again,

This query is specific to see the library and table names in the OLEDB source task .

I have got a solution to insert incremental data using ODBC but not using OLEDB ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1057881&SiteID=1 ),

still facing the same issue of insertion of data into database when SQL command is used as 'Data access mode'

anyone having idea on how to see library list of AS400 inside an OLEDB source task and why am i not been able to insert data to my destination when using sql mode?

Thanks !!

|||

amitshah003 wrote:

HI Phill,

I'm Not posting the same query again,

This query is specific to see the library and table names in the OLEDB source task .

I have got a solution to insert incremental data using ODBC but not using OLEDB ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1057881&SiteID=1 ),

still facing the same issue of insertion of data into database when SQL command is used as 'Data access mode'

anyone having idea on how to see library list of AS400 inside an OLEDB source task and why am i not been able to insert data to my destination when using sql mode?

Thanks !!

Have you searched the forums? Please see my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1021702&SiteID=1 on how to configure the Microsoft OLEDB for DB2 connector, if that will help.|||

Shah, the variable sytax is different between the two. OLEDB uses the @. symbol to identify variables the ODBC uses a '?' as a the identifier. look into this area.

I think this might be your issue.

Ryan

sql