CODEWITHSUNDEEP
sql-serverssisetlssis-2012
Biswa
Biswa

Reputation: 331

Issue importing spreadsheet with column null in SSIS

I have a strange issue while importing file from spreadsheet to our sql database table. If first row of any column is null then the whole column reflect as null in the table even if there is data in the 5th or 10th row for that column. Did any one face this issue while importing .xls data to sql server database? Again if the first row of that column is not null then the data will get imported properly.

Upvotes: 1

Views: 3122

Answers (1)

Hadi
Hadi

Reputation: 37313

The issue is that is when exporting Excel files with mixed data types column it will replace non dominant types by null.

This cannot be solved in a direct method, but based on my experience there are two workaround that can gives good results.

  1. Adding a dummy first row that contains random text values like abc, and adding ;IMEX=1 to the connectionstring, this will force excel to read the data type from the first row and it will read all data as Text

  2. Read Excel File without Headers (in the ConnectionString you should have HDR=NO). also add IMEX=1 to the connectionstring, Write the Columns name in the Excel Source manually, Skip the first Row (Which contains the columns headers) using Script Component.

Note: Importing data from excel using OLEDB or Ace (used in Excel connection manager) provider doesn't give 100% results, so these workarounds are to minimize the errors as much as possible

Upvotes: 2

Related Questions