Is there a as400/db2 native provider to extract data?

Is there a as400/db2 native provider to extract data?Yes. In SSIS,you can create a native OLEDB connection use Microsoft OLEDB provider for DB2 (DB2OLEDB), then use OLEDB Source Adapter to pull out data from the connection.

Thanks
Wenyang|||

When I create a new connection, I do not see the Microsoft OLEDB provider for DB2 as one of the options. Can I install it from somewhere?

|||Currently, it's part of the Host Integration Server product.

We're working on making it available to SQL Server users, but this hasn't been finalized as yet.|||Ok. I downloaded and installed the trial version of the Host Integration Server. In HIS, I can establish a connection to the AS400 and submit a simple query which works fine. However, when I create a OleDb data source object in SSIS using the Microsoft Ole DB Provider for DB2 I get the error:
Test Connection Failed because of an error in initializing the provider. The parameter is incorrect.
I have tried many different combinations of settings and get the same error. Any ideas on what the problem is?|||Hi
Could you pass on more information on how you get the error?

1) How do you create your DB2 connection in SSIS designer?
2) How you configure your DB2 connection? Especially what you set for "Host CCSID" and "PC code page" in your HIS DB2 connection?
3) Which version of HIS you are using? I currently have HIS intergration server 2004 release installed on my box and I do not get that error when creating a DB2 connection for SSIS OLEDBSource adapter.

Thanks
Wenyang|||The AS/400 driver in even the latest version of Host Integration Services (2004) is buggy and should not be used. What you want is to get your hands on the IBM Client Access V5R3 CD. Run through the setup program on this disk and choose to do a custom install. This will expose a selection that is not enabled even if you choose to install everything. There is somewhere under "Data Options" or something related to data an option to install a ".NET Data Provider". This will install a very good working driver that will allow you to code in .Net in Visual Studio and access the AS/400 as well as it will install non buggy versions of OLE DB for the AS/400. If you have any questions just email me at GregA@.IntellectualArtist.com

Greg|||

Greg,
Could you explain why you came to the conclusion of "The AS/400 driver in even the latest version of Host Integration Services (2004) is buggy and should not be used."? And let us know what problems you were facing? We SSIS, as well as the provider team will be happy to assist you on any issues you encounter.

For Redmanmc 's problem in an earlier post, he may just need to reset the properties of "Host CCSID" or "PC code page" in his connection string to get expected results.

Thanks

|||

Dear Wenyang - I'm not sure what Greg's issues are but I'm having problems with OLEDB connection to AS400. When I try to read from an AS400 LIB into a dataset (Visual Basic .NET 2003) I get the error "Memory Allocation Error". I would greatly appreciate any help you can provide in resolving this issue. Here's more details on the exact problem I'm experiencing:

A “Memory Allocation Error” experienced when attempting to fill a dataset object by opening an IBM VSAM file from a data adapter.

Error Message:

{System.Data.OleDb.OleDbException}

[System.Data.OleDb.OleDbException]: {System.Data.OleDb.OleDbException}

HelpLink: Nothing

InnerException: Nothing

Message: "Memory allocation error."

Source: "Microsoft OLE DB Provider for AS/400 and VSAM"

StackTrace: " at System.Data.OleDb.OleDbDataReader.ProcessResults(Int32 hr)

at System.Data.OleDb.OleDbDataReader.GetRowHandles()

at System.Data.OleDb.OleDbDataReader.ReadRowset()

at System.Data.OleDb.OleDbDataReader.Read()

at System.Data.OleDb.OleDbDataReader.HasRowsRead()

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

at ASG.GLConsolidation.DataAccess.DBHelper.ExecuteDataset(EnumProviders v_enuProvider, IDbConnection connection, CommandType commandType, String commandText, IDbDataParameter[] commandParameters)"

TargetSite: {System.Reflection.RuntimeMethodInfo}

Detail Description:

A .net component executes a “non-query” command against the AS/400 system creating two VSAM files. Immediately following creation, the .net component fills two data sets with the contents of the newly created VSAM files. A “memory allocation error” is experienced when attempting to fill the first data set causing fatal application fault.

This is newly experienced. This process ran successful when last executed several months ago.

Changes to Environment:

AS/400 operating system upgraded to version 5.3 from 4.2. Microsoft environment unchanged.

Data provider:

The SNAOLEDB driver is being used. Versions 5.0.0.873 and 6.0.1701.0. This error is experienced on both versions of the driver. It has been tested on two different systems, one with HIS 2000 and HIS 2004 installed.

Development Language:

VB .net

.Net Framework Version:

Microsoft .Net Framework 1.1 Version 1.1.4322 SP1

Client Operating System:

Microsoft Windows Server 2003 Enterprise Edition