The process to import data from AS400  uses a SQL Server  Linked server. The Linked Server points to an ODBC Connection.

 The ODBC connection (DSN) uses drivers to enable the connection between SQL Server and AS/400.

 Once the connection is established , t-sql statements extract data from the AS400 files.

Pre checks:

Ensure the ODBC connection  is  created on the server and the Linked server called MYAS400_ODBC (for this example)  is created to point at that ODBC connection.

In the example below , the ODBC is in the Data Source section “USP37899”

Having the right drivers for the ODBC is important. Assuming security and connectivity is OK , to the SQL Server and AS/400 , the wrong drivers for the ODBC is the most likely reason for failure. Find some other common errors and workarounds on SQL Server Import Data from DB2 AS400 iSeries

 Methods available for ODBC set up include:

a)       Install iSeries Navigator on the SQL server. This installs the relevant drivers

b)       Use  ODBC Driver for AS/400 from IBM

c)       OLE DB Provider for AS/400 from Microsoft

2. Import the Data from AS400 to SQL Server using OPENQUERY

OPENQUERY executes the  query on the configured linked server.

      INSERT STAGING_TABLE SELECT *
FROM OPENQUERY (MYAS400_ODBC,''select              
UNIQUE_ID,
DOCUMENT_TITLE ,
ACTIONED ,
ACTIONED_BY ,
ACTION_REQUIRED ,
CATEGORY ,
CREATED_BY ,
FROM MYI90FILe.MYI90name
           

Author: Jack Vamvas (http://www.sqlserver-dba.com)