SQL 2005 SSIS Excel Source Insert NULL Values for all Records

Hi,

I'm just getting confused today, because I tried to import Data from a Excel Source, but one column contains only NULL Values, but in the Excel Source there are a few entries for this column.

So the problem was SSIS Excel Source only look at the first 8 Rows if they contain the same value SSIS decide to use the same data type for all entries.

To fix this problem you have to add the following property to the Connection String of your Excel Connection Manager:

Excel 8.0;HDR=YES;IMEX=1

The property IMEX = 1 specifies that all datatypes should be import which are defined in the Excel Source.

 


Posted Dec 05 2006, 12:11 PM by Nadine Storandt
Filed under: ,

Comments

Ionut Trufin wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 10-28-2008 19:02

Thanks a lot for the help.

I ran into a similar problem and I was preparing to give up when I ran into this post. IMEX=1 solves the problem regardless of the data type SSIS chooses.

Sanjay wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 06-07-2009 21:38

Thanks for the information.

I have another problem.In SSIS i am not able to open a xlsx file using Excel Source.

Can anyone help me please how to do this.

Thanks.........

Mary wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 08-20-2009 13:50

Simply superb solution :)

Oliver wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 12-01-2009 19:40

A user provided spreadsheet contained numbers that should be considered as text (ex. 001230).  When loading the data to SSIS some numbers were converted to NULL even though in the spreadsheet there is a value (ex. 12333).  I tried formatting the entire column to TEXT and even copying the values to another column that was pre-formatted to TEXT but it did not solve the problem.  I had to paste the entire column fist to Notepad and then paste them back to Excel.  That worked.

However, your solution saved me the trouble of trying to fix the Excel spreadsheet.

Thanks a lot for sharing!

Kasper de Jonge BI Blog » Import Excel in SSIS and get Null values in stead of Numeric values wrote Kasper de Jonge BI Blog » Import Excel in SSIS and get Null values in stead of Numeric values
on 03-25-2010 15:32

Pingback from  Kasper de Jonge BI Blog » Import Excel in SSIS and get Null values in stead of Numeric values

SrinivasuluM wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 04-27-2010 7:27

Simply superb solution :)

i am struggling with this issue from last two days

me wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 07-13-2010 14:34

Thank YOU!

Zeta wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 07-13-2010 18:16

Thanks for the solution.

Rajeev wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 07-23-2010 18:42

Worked like a charm

vivek wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 11-29-2010 8:40

Thanks a lot your solution has saved a lot of time .............thanks for sharing ..............great JOb ....... :)

Tony P Raju wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 01-06-2011 7:22

I did it in SSIS 64 bit. It is working Properly

Congruus wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 01-12-2011 17:01

Just perfect, thx.

Gilles Renou wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 07-28-2011 20:24

You saved my day!

Many thanks

baba wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 08-04-2011 21:06

perfect

Rock wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 10-31-2011 21:24

Thanks for this info.

It solves my problem.

shruthi wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 03-02-2012 20:16

please tell me where to add the above string in connection string

please post an example.

Carlos wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 07-26-2012 0:32

Great I was having the same problem with SSIS 2008

Werner Moecke wrote re: SQL 2005 SSIS Excel Source Insert NULL Values for all Records
on 01-03-2013 20:03

Actually, I found that you need to perform BOTH steps suggested by MS, otherwise just either one alone won't cut it.

To summarize:

You *NEED* to include the "IMEX=1" parameter in the extended properties in the connection string AND move a couple lines with chars to the beginning rows of the excel spreadsheet.

Add a Comment

(required)  
(optional)
(required)  
Remember Me?
developers.de is a .Net Community Blog powered by daenet GmbH.