Integrity Constraint Error on SQL Server column with no constraints

I have created a simple package to load an Excel Spreadsheet into a SQL Server table. There is a one to one relationship between the columns in the .xls file and the columns in the DB record. I am getting integrity constraint errors when I try to load all numeric data from the spreadsheet (defined as Category General in excel, not defined as numeric but consisting of all numeric characters) into a column defined as (nvarchar(20), not null) in SQL Server Management Studio. There are no constraints on the column.

I have been able to temporarily bypass the offending rows, but I do need to load them into SQL Server. The problem column has a mixture of data, two examples would be: N255, 168050. It's the 168050 value that's causing the Task to bomb. How can I get this loaded into my table ?

I am running the package from within MS Visual Studio 2005 Version 8, Excel is version 2003 (11.8120.8122) SP2

Thanks,

Chris

Try setting the extended property IMEX to 1 in the Excel Source properties. Search this forum for IMEX and you'll get plenty of answers.|||And make sure you don't have any empty values in the excel source. Your table is set to not accept NULLs, so make sure there are none going into it.|||

Ok, so I did a search of this forum and found the recommendation to add "IMEX=1; MAXROWSTOSCAN=0" to the Excel Connection manager connection string. So I hunted and pecked and found that if I displayed the properties on my excel connection manager, there was a property called Expressions with three dots next to it that if you clicked on would display a Property and an Expression column. I chose connection string for the Property and pasted the recommended string into the Expression. I tried with and without quotes, eliminated the ; MAXROWSTOSCAN=0 part

(IMEX=1), also tried IMEX==1 because that's what the ensuing error message recommended - All to no avail.

what am I missing ?

Thanks,

Chris

|||Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

Your connection string would have to be similar to the above. Look at your existing ConnectionString property, copy it, and add IMEX=1 to the Extended Properties as I have done above.|||

Sorry to drag this out, but I had to add a Connection String Property to my Excel Connection Manager properties. When I bring up the Expressions dialog as I described above, all I see is a blank dialog box. Should I be using an Excel Connection manager or a modified OLE DB connection manager ? I have a text book that recommends using the latter for an excel file if you need finer control.

|||

You may use either one connection if you plan to set it only once. You do not need to use Expressions, change the ConnectionString property directly.

If you plan to edit/change the connection settings, than you might be better with pure OLE DB connection because the Excel wrapper might override the settings you manually put into the connection string.

Thanks,

Bob

|||

I directly edited the Connection string for my Excel Connection manager, adding IMEX=1; to what was there. The complete string is now:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES";IMEX=1;

I am unable to use the connection manager like that - I'm getting another error "Could not find Installable ISAM"

I assume HDR=YES means there is a header row in the spreadsheet, which there is in this case.

Thanks,

Chris

|||

Christohperrobin wrote:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES";IMEX=1;

IMEX goes *INSIDE* the Extended Properties property as I've shown earlier.

....Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";|||

I put the quote (") in the wrong place. I corrected that so my connection string is now:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

IT'S WORKING FINE NOW!

Thanks for all your help,

Chris

|||

Actually I had to run it a few more times before I got all the rows to load. The package was still skipping (Redirecting) some of the rows due to the SAME error on another column that had mixed format data just like the one that originally caused the problem. This other column also had both straight numeric and combination strings (ex: 84001, 57B42).

From what I gathered reading about this problem, it looks like Integration Services makes certain decisions about the metadata associated with the contents of Excel files based upon the first few rows of the input spreadsheet. With this in mind I moved the row containing the 57B42 value up closer to the top, so now instead of the first 10 rows having all numeric values in that column, there was a non-numeric value appearing early on in the mix.

When I tried to run the package after this change, It threw an error right at the beginning indicating there was a problem with the Excel Data Flow source. The error indicated that there was mismatched metadata between the Excel Source and the SQL Server OLE DB Destination. The error message also offered to fix the problem without my intervention. I did choose to allow this to happen. After that the package ran to completion and all 2034 rows in the spreadsheet loaded into the DB table.

I am just wondering how I should deal with this in the future. Do I actually have to be concerned with the order of the rows in my spreadsheet ? Why should it be a problem to load character data and apparent numeric data into an nvarchar field ?