February 2, 2011 at 1:53 pm
I have an as400 linked server and have used it without issue until recently.
running sql2005 sp3; linked server provider is Microsoft OLE DB for ODBC Drivers.
using iSeries Access on odbc dsn name.
linked server test connection is okay.
I am using the four part name in the select statement: (use all upper case)
SELECT * FROM TURDTA.CORP400.TURDTA.PSKUTAB
get the following:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command, The results if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command, The results if any, should be discarded.
I can run above using the openquery format.
Also, I can run the select for another table in library TURDTA, or run same table from different library TURDTAT.
so the table sizeame is not an issue.
So I am thinking the issue is with the table not soo much the linked server or odbc.
But I do not see anything unusual; I even select only one column type char without success.
I tried using cast to char on numeric fields without success.
I tried doing debug on as400 and on odbc...but not much help.
The sql server is on a vmware machine.
I was able to generate linked server create window and run it on my sql2008 server and create associated odbc, and that works....this is kinda weird....any help is appreciated.
Thanks, Rich
February 4, 2011 at 2:40 pm
so I found this topic and now I have ssis working....trick is to use data reader
AS400 - SSIS Issue Resolved.
I tried to build a connection between iSeries (as400) and SQL in SSIS using OLEDB Provider IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider.
Testing the connection was successful.
I used the Data Flow Source--> OLE DB source
I used a query to extract the required data from a table. It worked fine and on preview it also retrieved the required data.
However on executing the package, it failed giving me the error as follows
"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."
This issue can be resolved by using an ADO.Net connection manager with the Data flow source as Data Reader.
Connection Manager
1. Create a new ADO.Net connection Manager
2. Set the Provider to .Net Provider --> ODBC Data Provider
3. Create a DSN (Control Panel -->Administrative Tools-->Data Sources ODBC -->System DSN)
4. In the connection manager for Data source specification select the DSN created. Provide the login information.
5. Test the connection.
Data flow source:
1. Use the DataReader source
2. In Advanced Editor select the Ado.Net connection manager just created.
3. In Component Properties tab --> Custom properties, in SQLCommand specify the required query string (select * from DatabaseName.TableName)
4. Check the column mappings for accuracy
5. Go to Input and Output properties -->Data reader output -->External columns (Select the columns which were of type varchar in the table, they will now be of the datatype UnicodeString (DT_WSTR). This is because by default DataReader reads strings as unicode strings. This implies that in the destination table in SQL these columns must be of type unicode i.e NVARCHAR instead of VARCHAR)
Data Flow Destination
Create the required Data Flow destination and connect the source and destination.
Now the package will successfully extract data from iSeries and update the table in SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply