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.
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|||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.|||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.|||Bill,
Did you get this resolved. Running into the same issue in accessing tables on the iSeries?
thanks,
Guido|||Anyone had any luck with this?
I can get it to work on my dev pc, but not on my server 
...
pc = windows xp sp2
server = windows 2003 sp1
both using Client Access v5r3m0...
|||I have the exact same problem... |||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|||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|||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
No comments:
Post a Comment