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.

howeve 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