Hi

I'm using SSIS to import data from Excel files into a SQL 2005 database.
What I've noticed is that sometimes it inserts NULL's where there should be
data (I am doing a straight insert into holding tables)

For example, I have a column for the version of a software entry in the
excel file. This column is formatted as Text. The first x rows could be a
value such as 2000, 2003 (ie for a row for Office 2000 or 2003). I then may
or may not have an entry such as XP in this version column (ie for a row
Office XP). The problelm is when it encounter XP, it actually inserts NULL
into the table as well as when I do a previous using the package designer.

This is a very last minute problem we became aware of (we are due to deploy
in a few hours) and this is a major problem, since we have multiple packages
to apply this to before deployment.

Is there anyway I can fix this problem.

Al

HI,

The problem is due to the OLEDB Connection for Excel.
While reading Excel file through OLEDB Connection, It decides the data type of the column based upon the nature of the data in first four rows.
For example in your case in version column if u r having data as follows

Version

2003
2004
2005
2006
XP2007
XP SP1

Then OLEDB decides the data type of the column version is number. So while reading it returns null value for XP2007,XP SP1.

The only way to solve this is to create a DataFlow source component which reads the excel through Office Interop services. i.e., opening the Excel file and reading cell by cell

Hello Al,


are you seeing this issue perhaps?

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hi Allan

This definitely looks like the issue.

I did try reformatting the cells as text, but this only seemed to work when
Excel reports that a number is in a text column (you know the little warning
smart tab which displays next the cell). If I reformat and this tab isn't
shown, the problem still persists. For now we've gotten past the problem by
exporting the excel to a text file and using the flatfile source for SSIS.

The main problem I have is the excel files are created by non-technical
users cutting and pasting out of source files into a standard template file.
As you can imagine, then requiring them to reformat columns correctly isn't
something we can really expect them to reliable and consistantly do.

Any idea if this solution mentioned in the link also applies to SSIS?

Many thanks

Al

"Allan Mitchell" wrote:

Hello Al,

It is not the tool that is the issue it is the Excel Driver. This does not
change between DTS and SSIS


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com