locked
Mapping failed when import data from DB2/400 to SQL Server with SSIS RRS feed

  • Question

  • Hi,

    When I use SQL Server 2008 Import and Export Wizard to import data from DB2/400 V7R1 to
    SQL Server 2008, after select source tables and views, click 'Next' button.
    Find the following error:

    Column information for the source and the destination data cound be retrieved,
    or the data types of source colums were not mapped correctly to those available
    on the destination provider.

    "B101E31E"."C50B74IM"."SPSSACTION_ACTION"->[DBO].[SPSSACTION_ACTION]:

     -Column "RESOURCEID": Source data type "NATIONAL CHARACTER VARYING" was not found
    in the data type mapping file.
     ...

    It seems that all DB4/400's data type can't be recognized by SQL Server Import
    and Export Wizard.
    If I click 'Edit mappings...' button and modify the mapping manually, maybe it can
    work. But there are many tables in our database. It's not possible to modify every table's
    every column data type manually.

    So could you please tell me how to deal with this issue?

    Notes:
    1. During importing data with 'SQL Server Import and Export Wizard', I choose the data source 'Microsoft OLE DB Provider for DB2' which installer is downloaded
    from the link http://www.microsoft.com/download/en/details.aspx?id=16978 for the source database.


    Thanks.
    Thursday, August 4, 2011 9:57 AM

All replies

  • I believe that this has the information you are looking for: http://msdn.microsoft.com/en-us/library/gg163937(v=bts.70).aspx

    It describes editable data type mapping files that will control the conversion of data.  I understand this to be setting the default mapping as opposed to individually mapping each column.

    RLF


    Friday, August 5, 2011 5:51 PM
  • Hi,

    Thanks so much for you reply.

    I added some data type to the map file. And then tried to do migration again. Some data could be migrated into the target table.

    But in our source table, there is a  'NATIONAL CHARACTER LARGE OBJECT' data type. I couldn't find this data type in the following table:

    http://msdn.microsoft.com/en-us/library/gg163937%28v=BTS.70%29.aspx

    I edited this data type in the map file according to following table which is defined by myself.

    DB2 Data Type Name                                    

    DB2ToMSSql

    DB2ToMSSql10

    DB2ToSSIS10

    NATIONAL CHARACTER LARGE OBJECT        ntext            ntext                DT_NTEXT

    Could you please tell me whether the above map info is correct?

    If not, could you please tell me the correct map info?

    Thanks.

    Tuesday, August 9, 2011 9:12 AM
  • The NATIONAL means that it is UNICODE data.  That is what the N in Ntext stands for.  However TEXT, NTEXT, IMAGE are all deprecated data types.  Ideally, you should try mapping th NVARCHAR(MAX).   But I don't know if the wizard understand that, so try it out as a test to see what happens.

    RLF

    Tuesday, August 9, 2011 6:56 PM
  • Hi,

    Thanks for you reply.

    I modified the map file according to the following table. And then try again.

    DB2 Data Type Name                                    

    DB2ToMSSql

              DB2ToMSSql10

          DB2ToSSIS10

    NATIONAL CHARACTER LARGE OBJECT       NVARCHAR(MAX)   NVARCHAR(MAX)   DT_WSTR

    During using SQL Server Import and and Export Wizard, in 'Review Data Type Mapping' page, find the column conversion details info is as below:

    [Source Information]
    Source Location : DB2400
    Source Provider : DB2OLEDB
    Table: "B101E31E"."C50B74IM"."SPSSAUDIT_EVENTS"
    Column: DETAILS
    Column Type: NATIONAL CHARACTER LARGE OBJECT
    SSIS Type: Unicode string [DT_WSTR]
    Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\DB2ToSSIS10.XML

    [Destination Information]
    Destination Location : 9.30.214.255
    Destination Provider : SQLNCLI10
    Table: [dbo].[SPSSAUDIT_EVENTS]
    Column: DETAILS
    Column Type: ntext
    SSIS Type: Unicode text stream [DT_NTEXT]
    Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML

    [Conversion Steps]
    Step 1: DT_WSTR to DT_NTEXT
    SSIS conversion file: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\binn\DtwTypeConversion.xml

    Then copy the table data (append the rows to the existing table). Find some executing error like below:

    - Executing (Error)
    Messages
    Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
    An OLE DB record is available.  Source: "Microsoft DB2 OLE DB Provider"  Hresult: 0x80040E14  Description: "An internal network library error has occurred. A network level conversational protocol error has occurred. SQLSTATE: HY000, SQLCODE: -343".
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - SPSSAUDIT_EVENTS" (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.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)

    I click 'Back' button to review that column data type mapping. And find the column conversion details info changed. The changed detailed info is:


    [Source Information]
    Source Location : DB2400
    Source Provider : DB2OLEDB
    Table: "B101E31E"."C50B74IM"."SPSSAUDIT_EVENTS"
    Column: DETAILS
    Column Type: NATIONAL CHARACTER LARGE OBJECT
    SSIS Type: text stream [DT_TEXT]
    Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\DB2ToSSIS10.XML

    [Destination Information]
    Destination Location : 9.30.214.255
    Destination Provider : SQLNCLI10
    Table: [dbo].[SPSSAUDIT_EVENTS]
    Column: DETAILS
    Column Type: ntext
    SSIS Type: Unicode text stream [DT_NTEXT]
    Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML

    [Conversion Steps]
    Step 1: DT_TEXT to DT_NTEXT
    SSIS conversion file: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\binn\DtwTypeConversion.xml

    I tried to change the map file DB2ToMSSql10.xml and DB2ToMSSql.xml from 'NVARCHAR(MAX)' to 'NVARCHAR'. And then do migration again. And the above issue still exists.

    So could you please help analyze what the map file should be changed into?

    Thanks so much.

     

    Wednesday, August 10, 2011 10:07 AM
  • I do not know, since I do not  have DB2 to test against.  But, try NTEXT, since it is not impossible that the migration tool just does not handle the mapping that I suggested.

    FWIW,
    RLF

    Monday, August 15, 2011 5:11 PM