none
What is the best protocol to import data into sqlserver using an SSIS Package?

    Question

  • Im trying to import data into SQL Server from EXCEL with an SSIS package. In my data flow I am using "Excel Source" object and an "OLE DB Command" object.  Im getting the following error message, "...Excel Connection Manager" is an incorrect type....."  May someone explain why I get this and point me in the right direction in choosing the proper source for my package so I can complete my task.  Thanks.
    Friday, January 17, 2014 11:46 PM

Answers

  • Hi jsorrisone,

    The data type mismatch issue is a common issue when using Excel Source in SSIS. The Excel driver recognizes only a limited set of data types. The following section of the document Excel Source should point you in the right direction:

    The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:

    • Numeric – double-precision float (DT_R8)
    • Currency – currency (DT_CY)
    • Boolean – Boolean (DT_BOOL)
    • Date/time – datetime (DT_DATE)
    • String – Unicode string, length 255 (DT_WSTR)
    • Memo – Unicode text stream (DT_NTEXT)

    Integration Services does not implicitly convert data types. As a result, you may need to use Derived Column or Data Conversion transformations to convert Excel data explicitly before loading it into a non-Excel destination, or to convert non-Excel data before loading it into an Excel destination. In this case, it may be useful to create the initial package by using the Import and Export Wizard, which configures the necessary conversions for you. Some examples of the conversions that may be required include the following:

    • Conversion between Unicode Excel string columns and non-Unicode string columns with specific codepages
    • Conversion between 255-character Excel string columns and string columns of different lengths
    • Conversion between double-precision Excel numeric columns and numeric columns of other types

    If you need further help, post the complete error message as well as the data nature in your Excel source file for further analysis.

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, January 22, 2014 9:45 AM
    Moderator

All replies

  • In Excel you will get often errors like this, because columns may have values of different types and Excel Connection Manager not always guess them right.

    With CSV (comma separated values) files you will have more control over type assignments.

    But you need to share more about you data, size of it, how many rows, etc.. to get some better advices.

    Saturday, January 18, 2014 3:44 AM
  • Hi jsorrisone,

    The data type mismatch issue is a common issue when using Excel Source in SSIS. The Excel driver recognizes only a limited set of data types. The following section of the document Excel Source should point you in the right direction:

    The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:

    • Numeric – double-precision float (DT_R8)
    • Currency – currency (DT_CY)
    • Boolean – Boolean (DT_BOOL)
    • Date/time – datetime (DT_DATE)
    • String – Unicode string, length 255 (DT_WSTR)
    • Memo – Unicode text stream (DT_NTEXT)

    Integration Services does not implicitly convert data types. As a result, you may need to use Derived Column or Data Conversion transformations to convert Excel data explicitly before loading it into a non-Excel destination, or to convert non-Excel data before loading it into an Excel destination. In this case, it may be useful to create the initial package by using the Import and Export Wizard, which configures the necessary conversions for you. Some examples of the conversions that may be required include the following:

    • Conversion between Unicode Excel string columns and non-Unicode string columns with specific codepages
    • Conversion between 255-character Excel string columns and string columns of different lengths
    • Conversion between double-precision Excel numeric columns and numeric columns of other types

    If you need further help, post the complete error message as well as the data nature in your Excel source file for further analysis.

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, January 22, 2014 9:45 AM
    Moderator