Getting “SSIS Type: (Type unknown …)” error when using SQL Server Import and Export Wizard

Recently, I encounter a issue when transfer a table with hierarchyid data type between two SQL Server 2008 R2 instances using SQL Server Import and Export Wizard. The error looks like this:

Capture

From the error message above, it was a SSIS Type unknown for hierarchyid data type in the SSIS mapping file C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML.

Then, I went to that directory and open MSSQLToSSIS10.XML mapping file, which is data type mappings between source data type and destination data type. However, I cannot find a data type mapping entry for hierarchyid data type, therefore I got above error because the SQL Server Import and Export Wizard does not know what data type to use for hierarchyid in the destination table.

I tried to manually create a mapping entry for hierarchyid data type using similar format as any other data types. To change this file we need to open it under an administration account security context, right-click the Notepad, run it as administrator, open MSSQLToSSIS10.XML file; and then add the following XML entry in the mapping file and save the mapping file:

    <!-- hierarchyid -->
     <dtm:DataTypeMapping >
      <dtm:SourceDataType>
       <dtm:DataTypeName>hierarchyid</dtm:DataTypeName>
      </dtm:SourceDataType>
      <dtm:DestinationDataType>
       <dtm:SimpleType>
        <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
       </dtm:SimpleType>
      </dtm:DestinationDataType>
     </dtm:DataTypeMapping>

    After that, when I tried to run SQL Server Import and Export to transfer that table again, I did not get the above error and table transferred successfully. Problem resolved Smile

    One more thing is that if we are running 64-bit OS, we may get both 32-bit and 64-bit SQL Server Import and Export Wizards. For 32-bit SQL Server Import and Export Wizard, update the related mapping file under C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\ folder; and for 64-bit or 32-bit OS, update it under C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\ folder.

    Hope this helps.

    Advertisements

    About Alex Feng

    SQL Server DBA in Alibaba Cloud Computing
    This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

    8 Responses to Getting “SSIS Type: (Type unknown …)” error when using SQL Server Import and Export Wizard

    1. brian says:

      Great post! Just ran into this today.

      One comment, if you’ll create a data flow task from or to a table with a hierarchyId, you’ll see that SSIS actually interprets hierarchyId as DT_BYTES (not DT_IMAGE).

    2. Nallaperumal says:

      Hi
      After changing the ‘MSSQLToSSIS10.XML file’, it cross the current level. and throwing error while importing the data to the table.

      Can u pls help me..

    3. Nallaperumal says:

      – Executing (Error) :: AS Mentioned below….
      Messages
      Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column “hierarchyid” (21) to column “hierarchyid” (73). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
      (SQL Server Import and Export Wizard)

      Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output column “hierarchyid” (73)” failed because error code 0xC020907F occurred, and the error row disposition on “output column “hierarchyid” (73)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
      (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” (57) failed with error code 0xC0209029 while processing input “Data Conversion Input” (58). 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)

    4. Alex Feng says:

      Hi,
      Please add the above XML block to the end of XML file, just before . Also, update XML accordingly based on the version (32-bit/64-bit) of Import Wizard as I mentioned above.

      Please let me know if you have further question.

      • Nallaperumal says:

        Thanks for your reply., Still is not working
        This is the step i have done

        1. I am using 32 bit.
        2. I opened the file from C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML
        3.Added the content block you have mentioned and saved the file.
        4. Again if i try to import the data from the Import wizard, it throws an error while importing the data to the database.. the error as follows

        – Executing (Error)
        Messages
        Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column “hierarchyid” (21) to column “hierarchyid” (73). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
        (SQL Server Import and Export Wizard)

        Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output column “hierarchyid” (73)” failed because error code 0xC020907F occurred, and the error row disposition on “output column “hierarchyid” (73)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
        (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” (57) failed with error code 0xC0209029 while processing input “Data Conversion Input” (58). 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)

        Waithing for your reply..

        The hierarchy id in excel is like /3/1/

    5. Misha says:

      Thanks a lot. Worked for me.

    6. louissequin says:

      Remember to change the XML file in both Program Files and Program Files (x86). BTW, DT_IMAGE works fine. I cannot vouch for DT_BYTES, though they both essentially just take what is there and move it without doing any conversion so they should both work.

    7. Lucas says:

      i can’t save mapping file because i’ve got message that my file is use, sql server was shut down, ssis was shut down, ssas shut down
      what should i do with this error a cant import data to excel

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s