Integrity Constraints

I had a question regarding using XML as a database rather than a data format.
Can some/all types of integrity constraints that we (can) have in a SQL database be represented/mapped to XML?
Could anyone explain or give some pointers to this...
-Aayush
I'm not sure exactly what you're asking. In general, XML isn't a database
so using it as a database doesn't make sense. In the limited scope of the
topic of this newsgroups, SQL Server 2000 and SQLXML allow you to store the
data in an XML document as one or more rows of normal relational data.
Because the XML is mapped to relational data, the integrity constraint on
the relation data apply so for example you may not be able to insert a
document that has order lines if no corresponding order header exists. Note
that this is possible because XML is being shredded into relational data and
is not an inherent part of XML. In general, XML schemas don't support
defining or enforcing most integrity constraints.
Does this answer your question?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Aayush Puri" <anonymous@.discussions.microsoft.com> wrote in message
news:3EC02BEB-4AEA-438C-AB11-4D4F33C1CD6D@.microsoft.com...
>I had a question regarding using XML as a database rather than a data
>format.
> Can some/all types of integrity constraints that we (can) have in a SQL
> database be represented/mapped to XML?
> Could anyone explain or give some pointers to this...
>
> -Aayush
>
|||Hey,
Thankx for the reply. Yeah I know that it makes little sense to use XML as
a database rather than a data format...but the app. which I am trying to
design is for users *not* having any SQL database. I was just just wondering
if XSD allows me to specify constraints alike SQL or if possible things like
triggers etc...
U got my point right.
Thankx,
-Aayush

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 ?