Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Integration Services

Accessing AS400 Data

I've been trying to use the integrations services to access AS400 data with almost no success. Any suggestions or help you can give me would be appreciated. Some of the issues I can't make sense of are the following.

1. When I set up an OLEDB data source for the AS400 (using IBM's Client Access provider) I always get a warning message about "Cannot retrieve the column code page information from the OLEDB provider". What do I do about this warning?

2. If the oledb datasource is a table I can sometimes get it to work (though it seems very slow) but if I make the source a sql query it always fails in the first step with an OLEDB error. Any ideas about where to look for the problem?

Below is a sample outlook log from a failure. The only change from a previous example which success was that I changed the Access Mode from OpenRowSet to SQL Command and replace the table name with a sql statement select all the fields in the table.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning

Warning: 0x80202066 at Data Flow Task, OLE DB Source [3820]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning

Warning: 0x80202066 at Data Flow Task, OLE DB Source [3820]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning

Warning: 0x80202066 at Data Flow Task, OLE DB Source [3820]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Information: 0x402090DC at Data Flow Task, Flat File Destination [4320]: The processing of file "c:\txpfstat.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning

Error: 0xC0202009 at Data Flow Task, OLE DB Source [3820]: 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" (3820) 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.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning

Information: 0x402090DD at Data Flow Task, Flat File Destination [4320]: The processing of file "c:\txpfstat.txt" has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (4320)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: 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.

SSIS package "Package.dtsx" finished: Failure.

[4700 byte] By [BillHamaker] at [2008-2-25]
«« Displaying a List Horizontally
»» Matrix Subtotals
# 1
1. The warning is expected, you'll just need to set the "DefaultCodePage" property at your OLEDBSource to the code page you want to use for your string columns, then you can safely ignore it.
2. The fatal error is 0x80040E00, which means "Invalid Accessor", it's raised from the provider rather than from SSIS Source adapter. You may need to double check the SqlCommand you used in the OLEDBSource to see whther that's in good grammar. I tried several cases to switch from OpenRowSet to SqlCommand when connecting to a AS400 DB2 server, I see no problem (I am using the MS OLEDB provider for DB2 though).

Thanks

Wenyang at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 2
1. I don't really understand code pages so I don't know how to set the DefaultCodePage property or what value to set it to. But since the data comes through okay when using OpenRowSet I guess I can just ignore the warning.

2. The SqlCommand is trivial (e.g. "select * from taxdta.txpfstat") so I'm sure the grammer is fine. Certainly the parser within SSIS thinks it fine.

Can anyone else test whether or not SSIS beta works on SQL commands using the IBM Client Access OLEDB data provider? I don't have access to the Microsoft provider to use that instead.

I'm not trying to do anything fancy here just copy entire tables (including creating the table on SQL Server) from the AS400 to SQL server without adversely impacting AS400 producution. Perhaps someone can suggest a better solution besides SSIS for copying from the AS400 to SQL Server.

BillHamaker at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 3
I am having the exact same problem trying to transfer data from a PeopleSoft/Oracle database (not on IBM) to a SQL 2005 database. The same transfer works fine in SQL 2000. Any help would be greatly appreciated. The code page that shows on the data source is 1252 and the locale id is English (United States). My SQL statement parses without errors and I can preview the first 200 rows of the query.
blachmann at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 4
Bill,

Did you get this resolved. Running into the same issue in accessing tables on the iSeries?

thanks,

Guido

GuidoVanV. at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 5
Anyone had any luck with this?

I can get it to work on my dev pc, but not on my server Sad ...

pc = windows xp sp2
server = windows 2003 sp1

both using Client Access v5r3m0...

JpFortin at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 6
I have the exact same problem... Sad
Reckless at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 7
We are talking about this in two seperate threads...;) post the link below in case someone here missed the other one which contains more insights on the issue
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=107266

Thanks
Wenyang

WenyangHu at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 8
I have almost the same issue. My problem is, I can connect to AS400 without problem using the DTS but (now here comes) when I use this DTS in a Job Schedule the job never ends, I have no errors, no warning, nothing. The first time I stopped the Job 21 hours after it started.
I did the test to make a connection to the sql and send it to a flat file, not a single problem. As soon as I change the connection to the 400 then I have the problem. And as I said before, if I run the DTS manually it runs fine. I'm using September CTP and IBM Client Access V5R1M0 w/ sp SI11806.

If somebody have any idea I appreciate

Thanks,

Arty

ArtyArochita at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 9
Hi Artiy,

i have had the same problem with the job sceduler but fixed it. The reason is this:
If you are running the dts by yourself then you always have to login to as400. Once you have done your account informations are cached until you log out.
The jobsceduler does not login with the credentials of your ODBC DSN. To fix it you have to create an account on your PC/Server and the as400 which has the same name and the same password. Then use this user for the DTS-Task.

checkity

checkity at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
SQL Server
SQL Server Integration Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified