Last day I run into an issue while trying to import data from an excel workbook into some MSSQL 2008 tables.
On the setp "Review Data Type Mapping" of the Import/Export Wizard I got the infamous
red icon, next to two of the columns I wanted to import
Clearly the source data and the target data for those two columns are of different types and SSIS doesn't have a mapping between a Double and a nvarchar.
The mapping between SSIS data types are defined on a xml file located under the folder "Microsoft SQL Server\100\DTS\Binn" named DTUtil.exe.config
Luckily this XML file can be augmented to support conversions of others SSIS data types as long as the the the formatting of the target and source data types allow the conversion.
First step is to discover what SSIS data types are the columns generating the issue.
On the Review Data Type Mapping screen I located one of the column experiencing the
unknown column type conversion and double click to see the conversation details.
On the Review Data Type Mapping screen I located one of the column experiencing the
unknown column type conversion and double click to see the conversation details.
By looking on the Column Conversion Details I notice that the SSIS type of my source colum is (DT_R8)
Column Type: Double
SSIS Type: double-precision float [DT_R8]
and the SSIS type of the target or destination column is DT_TEXT.
Column Type: nvarchar
SSIS Type: Unicode text stream [DT_NTEXT]
Next I open the DtwTypeConversion.xml file using Tool for Men (aka Visual Studio)
By looking on the section for the DT_R8 type I confirmed that conversion of DT_R8
to DT_TEXT is not supported on the current mapping.
Column Type: Double
SSIS Type: double-precision float [DT_R8]
and the SSIS type of the target or destination column is DT_TEXT.
Column Type: nvarchar
SSIS Type: Unicode text stream [DT_NTEXT]
Next I open the DtwTypeConversion.xml file using Tool for Men (aka Visual Studio)
By looking on the section for the DT_R8 type I confirmed that conversion of DT_R8
to DT_TEXT is not supported on the current mapping.
As a matter of fact my current mapping for the DT_R8 type only supports: conversion DT_R8 types to DT_DATE, DT_NUMERIC AND DT_DECIMAL
Time to add the DT_NTEXT type to this mapping to let SSIS knows that the conversion type of a Double to nvarchar is possible.
With the extra mapping for the conversion of double (DT_R8) to nvarchar(DT_NTEXT) added, this is the way this section of my DtwTypeConversion.xml looks like:
1. Create a copy or back up of the DtwTypeConversion.xml before you modify it.
2. On window 7 and Server 2008 you will need to open the DtwTypeConversion.xml with
Administrator rights.
3. After applying the changes to the DtwTypeConversion.xml and before importing the
data, back up your database just in case your data lose precision.
4. if working with real data, this is data that will be used on a real application,
I would suggest you test this procedure on your developer environment to minimize
down time to either your clients or company.
Don't underestimate the power of the Dark Side.
Time to add the DT_NTEXT type to this mapping to let SSIS knows that the conversion type of a Double to nvarchar is possible.
With the extra mapping for the conversion of double (DT_R8) to nvarchar(DT_NTEXT) added, this is the way this section of my DtwTypeConversion.xml looks like:
1. Create a copy or back up of the DtwTypeConversion.xml before you modify it.
2. On window 7 and Server 2008 you will need to open the DtwTypeConversion.xml with
Administrator rights.
3. After applying the changes to the DtwTypeConversion.xml and before importing the
data, back up your database just in case your data lose precision.
4. if working with real data, this is data that will be used on a real application,
I would suggest you test this procedure on your developer environment to minimize
down time to either your clients or company.
Don't underestimate the power of the Dark Side.
No comments:
Post a Comment