Problem importing into SQL Server 2012 from AS400

Simone Pagliani 1 Reputation point
2020-09-25T08:35:17.457+00:00

Hi, We've quitted using the AS400 for a SQL Server based software.

We'd like to migrate the databases from it into a server having OS Windows Server 2012 Standard and SQL Server 2012.

We're talking about around 20 databases with hundreds of tables.

After having created the DB under SQL Server, I've tried to use the Import/export wizard to mass import the tables.
After some search and tests, I'm using the ".Net Framework Data Provider for Odbc" as a data source, since the teoretically obvious "IBM DB2 for i5/OS IBMDA400 OLE DB Provider" doesn't alloow me to even read the list of tables in the database.

As a connection string, I've used:
"Dsn=AS400ACG;Driver={Client Access ODBC Driver (32-bit)};datasource=10.0.0.3;userid=QSECOFR;password=XXXX;datacompression=True"
(I've tried even a "Dsn=AS400ACG;Driver={ODBC Driver 13 for SQL Server};datasource=10.0.0.3;userid=QSECOFR;password=XXXX;datacompression=True", with no better result)

The problem is that, even if most of the tables are copied without problems, a very little amount gives error, copying only part of the records, if any.

I got errors like:
"Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "DESUP" (31) to column "DESUP" (101). The conversion returned status value 4 and status text "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 1: The "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[DESUP]" failed because truncation occurred, and the truncation row disposition on "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[DESUP]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 0 - 0" (77) failed with error code 0xC020902A while processing input "Data Conversion Input" (78). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)"

The column is a description one, 25 chars not null. I've tried modifying the arriving format, enlarging it to 50 chars and allowing the NULL value. No effect.

During the step, it show a windows, titled "Convert Types Without Conversion Checking", warning that:
"[Source Information]
Cannot locate the mapping file to map the provider types to SSIS types"

It seems that, into the C:\Program Files (x86)\Microsoft SQL Server\140\DTS\MappingFiles folder, it doesn't find the right file.

How should it be named?
Where can I find it?
Is it another reason it doesn't works?

Thank you in advance,
Simone Pagliani

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,459 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
495 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,606 Reputation points
    2020-09-30T07:13:24.927+00:00

    Hi @Simone Pagliani ,

    first problem-connect to the AS400 server

    If the database server is not configured to accept connections on the default port (1521), enter the port number that is used for DB2 connections in the Server port box. You need to determine this port number.
    https://learn.microsoft.com/en-us/sql/ssma/db2/connecting-to-db2-database-db2tosql?view=sql-server-ver15

    second problem-connect to SQL Server

    If you select Encrypt Connection, please also select the "Trust Server Certificate" check box. If the current windows account is available, you can also choose windows authentication.

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. Olaf Helper 40,916 Reputation points
    2020-09-25T08:56:25.84+00:00

    Cannot locate the mapping file to map the provider types to SSIS type

    You have to edit the data type mapping manually, see Review Data Type Mapping (SQL Server Import and Export Wizard)

    Why do you want to want to migrate manually, you can use the free MS Tool SQL Server Migration Assistant for DB2 (DB2ToSQL)

    0 comments
  3. Simone Pagliani 1 Reputation point
    2020-09-25T13:56:02.563+00:00

    Thanks for the answer.

    I've tried using it, but I have found other problems.

    I'll try it again, should I have again the problems I've had, I'll write here again.
    Meanwhile, I'll check even the other link.

    0 comments
  4. Cris Zhan-MSFT 6,606 Reputation points
    2020-09-28T07:30:38.95+00:00

    Hi @Simone Pagliani ,

    Starting with version 8.11, SSMA now supports Db2 i Series to automate the migration process.
    https://techcommunity.microsoft.com/t5/modernization/accelerate-migration-of-db2-for-i-as400-series-to-azure-data-sql/ba-p/1548732

    0 comments
  5. Simone Pagliani 1 Reputation point
    2020-09-29T13:05:10.603+00:00

    29191-20200929-connection-errorb.jpgHi, since you both invite me to use the SSMA, I've tried again to use it.

    But I have two problems, one on each front.

    First, Ive tried to connect to the AS400 server, filling the data.
    As a server port, he propose three values, 5000, 23 and 389. With the first two it gives connection error after two seconds. With 389 it start trying to connect, going on endlessly. I've been forced to brutally stop it, after two hours.
    I've loked for other ports, I've found that the standard port for communication should be 1521, so I've tried it. Same story, I was forced to close it.
    So, I entered the System i Navigator and checked for the ports it use veryfing the connection to the server.
    In the section "Succesful connection with the application server: Data Access", it use the port 8471.
    So, I've tried to use it, but the loop connecting remained.
    I believe it's the wrong port that stops me, since all the other data are correct(checked twice).
    Am I wrong?
    Should am I right, which is the right port, or when should I look for it?

    I've tried to connect to SQL Server, also(SQLServer 2012).I've filled the data, using the SQL Server Autentication user and password.
    But I received the Following error message.
    Since from the second row is mostly in italian, I'll translate it(sorry for any mistake in doing it):
    "Connection to SQL Server failed.
    The connection with the server was established correctly, but an error has happened later during login procedure.(provider: SSL Provider, error:0 - Chain of

    certifications emitted from a certification Authority not avalaible into the local list)"

    How can I solve this?

    I've attached the screenshot of the data I've used and the message I've got.

    Thank you in advance,
    Simone

    0 comments