April 9, 2009 at 8:22 am
I need to get 2 tables from an as400 db2 database and delete the existing rows before it adds the data. I have the delete rows working fine but I am having trouble with the rest. I created ole db data connections for the SQL server and as400 and they seem to work fine. I set up oledb data sources for each table. When I execute each of the data sources I get an error that says "Cannot retrieve the column code page from the oledb provider. If the provider supports the defaultcodepage property, the code page from that property will be used. Change the value of the property if the current code page string values are incorrect. If the component won't support the property, the code page from the components local id will be used". If I hit the OK button the data displays just fine in the popup window. Also both of my destinations have an error which I think is related "Column "Prcl_Nbr" cannot convert between unicode and nonunicode data types".
When I hit F5 from the control flow data tab I can see that all of the string data types coming in have similar unicode errors.
On my data flow tab I have the source and destination controls for each table. Is there an order in which they get executed? not that it matters just curious.
Thank you in advance.
Quinn
April 10, 2009 at 10:27 am
I was able to fix part of the problem. On the source side on the AS400 I changed alwaysusedefaultcodepage to true and that took care of the errors I was getting. I am still getting the same error on the destination side. I tried changing the same property on the destination but that didn't seem to help.
Thanks
Quinn
April 12, 2009 at 9:25 pm
It sounds like you've got a mismatch in your variable character types between the source and destination. SSIS won't implicitly change a VARCHAR (ASCII) to NVARCHAR (Unicode) or vice versa. Since you're dealing with AS400 as the source, you're probably stuck with whatever you've got, which I suspect is Unicode. For both of your destinations, configure the string columns to match those in yoru source. If you're not familiar, the DT_STR is SSIS representation of the ASCII type, and DT_WSTR corresponds with the Unicode type.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
April 13, 2009 at 9:19 am
Tim
Thanks for the help. I changed all of the fields from nvarchar to varchar. That helps. I don't have any design mode errors left but when I run the package I get this error message:
Information: 0x40043007 at Extract AS400 Data, DTS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0202009 at Extract AS400 Data, SQL Server Destination for ATReal table [3630]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
Error: 0xC0202071 at Extract AS400 Data, SQL Server Destination for ATReal table [3630]: Unable to prepare the SSIS bulk insert for data insertion.
Error: 0xC004701A at Extract AS400 Data, DTS.Pipeline: component "SQL Server Destination for ATReal table" (3630) failed the pre-execute phase and returned error code 0xC0202071.
Information: 0x40043008 at Extract AS400 Data, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Extract AS400 Data, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Extract AS400 Data, DTS.Pipeline: "component "SQL Server Destination for ATSales table" (3818)" wrote 0 rows.
Information: 0x4004300B at Extract AS400 Data, DTS.Pipeline: "component "SQL Server Destination for ATReal table" (3630)" wrote 0 rows.
Task failed: Extract AS400 Data
Do you know what could be causing this error?
Thanks again.
Quinn
April 13, 2009 at 9:32 am
Quinn,
Are you using a SQL Server Destination to write the data to your SQL Server database? If so, I recommend changing to an OleDB Destination. The SQL Server Destination is problematic and has a tendency to throw strange errors such as this. The OleDB Destination is reported to be a little slower than the SQL Server destination, but the tradeoff in performance is minimal considering the amount of effort required to make the SQL Server destination work properly.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
April 14, 2009 at 8:03 am
Tim
Thanks again for your help.
I changed the destination like you suggested. I don't have any design time errors but when I run it I get this error:
Error: 0xC0202009 at Extract AS400 Data, Extract ATSales from AS400 [3859]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Error: 0xC0047038 at Extract AS400 Data, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Extract ATSales from AS400" (3859) 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.
Error: 0xC0047021 at Extract AS400 Data, DTS.Pipeline: 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.
Error: 0xC0047039 at Extract AS400 Data, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" 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.
Error: 0xC0047039 at Extract AS400 Data, DTS.Pipeline: 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.
Error: 0xC0047021 at Extract AS400 Data, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Extract AS400 Data, DTS.Pipeline: 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.
Error: 0xC0202009 at Extract AS400 Data, AS400 ATReal table extraction [3426]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Error: 0xC0047038 at Extract AS400 Data, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "AS400 ATReal table extraction" (3426) 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.
Error: 0xC0047021 at Extract AS400 Data, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread1" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Extract AS400 Data, DTS.Pipeline: Post Execute phase is beginning.
I googled that error and didn't find much just a thread on the IBM site that talks about using the ibmdasql provider. There seems to be quite a few people who are having trouble getting data from the as400.
Any suggestions?
Thanks
Quinn
April 14, 2009 at 11:58 am
For my data transfers from our AS400 I have set up a Linked Server that uses the iSeries Access ODBC driver. Then I use TSQL syntax in my SSIS queries. In SSIS I use the .Net ODBC provider with a DataReader Source in my Data Flow.
This job runs daily and I have never had any problems with it.
April 14, 2009 at 1:59 pm
Tim and Ed,
I got it. I found a thread on the IBM site when I googled the error message I was getting. It had reference to a client side and server side ptf's that we installed and it started to work. Now all I have to do is figure out how to get it run on a scheduled basis. I am going to see If I can get it to work with the odbc provider like Ed suggested.
Thanks
Quinn
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply