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.
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)