SSIS Excel Import Forcing Incorrect Column Type

by

I’ve seen this issue before, it’s Excel that is the issue not SSIS. Excel samples the 1st few rows and then infers the data type even if you explicitly set it to text. What you need to do is put this into the Excel file connection string in the SSIS package. This instruction tells Excel that the columns contain mixed data types and hints it to do extra checking before deciding that the column is a numeric type when in fact it’s not.

;Extended Properties="IMEX=1"

It should work with this (in most cases). The safer thing to do is export the Excel data to tab delimited text and use SSIS to import that.

More Related Contents:

  1. Importing excel files having variable headers
  2. How to handle this scenario in single SSIS package?
  3. Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?
  4. Datatype for storing ip address in SQL Server
  5. Import Excel spreadsheet columns into SQL Server database
  6. Is there a Boolean data type in Microsoft SQL Server like there is in MySQL? [duplicate]
  7. Real vs. Floating Point vs. Money
  8. How do I pass value to a stored procedure parameter in OLE DB Source component?
  9. How can I import an Excel file into SQL Server? [closed]
  10. How to load an XML file into a database using an SSIS package?
  11. Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility
  12. Running an SSIS Package using dtexec
  13. SSIS reading LF as terminator when its set as CRLF
  14. Dynamically assign filename to excel connection string
  15. Temporarily disable all foreign key constraints
  16. Convert from DateTime to INT
  17. Change type of a column with numbers from varchar to int
  18. What are the differences between Merge Join and Lookup transformations in SSIS?
  19. How to execute SSIS package when a file is arrived at folder
  20. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  21. How to avoid SSIS FTP task from failing when there are no files to download?
  22. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  23. How to loop through Excel files and load them into a database using SSIS package?
  24. Why do table names in SQL Server start with “dbo”?
  25. How Do I Split a Delimited String in SQL Server Without Creating a Function?
  26. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  27. Database design: Calculating the Account Balance
  28. Conditional JOIN Statement SQL Server
  29. How do you deal with blank spaces in column names in SQL Server?
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment