SQL Server Import Data from DB2 AS400 iSeries

A SQL Server Import Data from DB2 AS400 iSeries  is straightforward , once you’ve overcome a few problems.

 A common error message on a SSIS import is :

 Text was truncated or one or more characters had no match in the target code paged

 Associated with the following types of detailed error messages.

 - Executing (Error)

Messages

• · Error 0xc020901c: Data Flow Task: There was an error with output column "TITLE" (20) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
   (SQL Server Import and Export Wizard)
   
• · Error 0xc020902a: Data Flow Task: The "output column "TITLE" (20)" failed because truncation occurred, and the truncation row disposition on "output column "TITLE" (20)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
   (SQL Server Import and Export Wizard)
   
• · Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Query" (1) returned error code 0xC020902A.  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.
   (SQL Server Import and Export Wizard)
   
• · Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
   (SQL Server Import and Export Wizard)
   
• · Error 0xc0047039: Data Flow Task: 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.
   (SQL Server Import and Export Wizard)
   

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
 (SQL Server Import and Export Wizard)

The initial SQL Server target table definition used  the  data type set VARCHAR for some columns. VARCHAR is  for non-Unicode character data

 As the  data migration was  occurring from DB2 AS400 – I took a guess that some Unicode may be used.

 The first thing we should try is changing the VARCHAR columns to NVARCHAR, which support Unicode character data .

 This worked for all columns except 1 column.  To confirm, I took the column out of the SQL Server Import and the data imported successfully.

 I changed the Code Page types , but this had no effect.

 I’ve found 3  workarounds:

1)      Export the data from DB2 AS400 isSeries into a flat file with delimiters. Use sql Bulk Insert  to import into a SQL Server table. This method has never failed.

2)      Use DTS.

3)      Use a Linked Server. Create the Linked Server as:

                  General

                  Linked Server     AS400

                  Provider IBM AS400 OLE DB Provider

                  Product Name   <put_some_text>

                  Data Source      <server_name or ip_address>

                  Security

                  Map local account

                  Check the Allow Inprocess under IBMD400 Provider Options

A sample query:

   SELECT * INTO import_table FROM AS400.ServerName.A.aTable

 Related Posts

Import Data from AS400 to SQL Server

SQL Server Linked Servers and User permissions