Compare 2 SQL tables to find missing rows fix the difference.

Problem

I have 2 database tables in 2 different databases and they should contain the same data. In this particular case the data is inserted by an application that inserts the data row by row. The primary key is of type uniqueidentifier with default value newid(), so it will never be the same in both tables. The table should have the same number of rows and all columns should contain identical data.

Solution

The T-SQL clause EXCEPT can help to verify if the tables contain the same rows.

From the SQL-BOL: EXCEPT returns any distinct values from the left query that are not also found on the right query.

Example

Compare all columns:

SELECT * FROM db1.dbo.Product
EXCEPT
SELECT * FROM db2.dbo.Product

This will return all rows in table of db1 that are not found or different from the table in db2.

But with the primary key column that is always different (because the guid is generated per table and per row) all rows will be returned, even when the number of rows is equal and all the other columns are equal.

To compare the columns that are different except the primary key column we slect the columns by name, except the primary key column:

SELECT [name], [price], [categoryid], [supplierid] FROM db1.dbo.Product
EXCEPT
SELECT [name], [price], [categoryid], [supplierid] FROM db2.dbo.Product

In this case all the rows that are in db1 but not in db2 will be show.

To add the missing rows to the db2 table (the primary key has default value newid()):

INSERT INTO db2.dbo.Product ([name], [price], [categoryid], [supplierid])
SELECT [name], [price], [categoryid], [supplierid] FROM db1.dbo.Product
EXCEPT
SELECT [name], [price], [categoryid], [supplierid] FROM db2.dbo.Product

SQL 2008 Create a unique index on field with NULL values

According to ANSI standards a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values. In SQL Server a single NULL is allowed but multiple NULL values are not.

In SQL Server 2008,  it is possible to define a unique filtered index based on a predicate that excludes NULLS:

CREATE UNIQUE NONCLUSTERED INDEX ix_Mycolumn_notnull
ON MyTable(Mycolumn)
WHERE Mycolumn IS NOT NULL;

Odata service with EF 6 and WCF services 5.6 fails with Request Error

Problem

I tried to follow a few more examples about building OData services with Visual Studio 2013, Entity Framework 6 and WCF Services 5.6, but when I tried to browse the Wcf (OData) service from VS 2013, it starts IE wiith an error message:

“Request Error. The server encountered an error processing the request. See server logs for more details.”

I tried various combinations, but to no avail (I even used another development system to try in case my development environment was botched up).

Solution

Eventually I found an anwser in stackoverflow.com foru, by Kourosh.

“it seems the entity framework 6 and wcf data services 5.6 , need some provider to work together. you can download the provider simply by using nuget package console manager :

Install-Package Microsoft.OData.EntityFrameworkProvider -Pre

it’s version is alpha 2, so in future, search for final release. it worked for me however.

final thing is instead of DataService, you need to use EntityFrameworkDataService. T is the name of your entities.”

Thank you Kourosh. Now my OData service with EF 6 and WCF data services 5.6 works!

Procedure

Step 1: install the package

Step 2: edit the WCF webservice c sharp code and replace DataService with EntityFrameworkDataService.

using System.Data.Services.Providers;

namespace DataAccessMydata

{

    public class ODataMydata : EntityFrameworkDataService<MydataContext>

Adding query options to the SSIS OData Source

 

In my previous article about SSIS OData source I created an SSIS 2014 package with an OData source connected to the Odata.org sample webservice.

OData query options

The OData Source has query options that can be used to alter the collection that is being used. I have made some tests on these options.

Option	Description
$expand	Expands related entities inline
$filter	Filters the results, based on a Boolean condition
$inlinecount	Tells the server to include the total count of matching entities in the response
$orderby	Sorts the results
$select	Selects which properties to include in the response
$skip	Skips the first n results
$top	Returns only the first n the results

See also Odata uri conventions paragraph 4

Set query options in OData Source editor

In the Data Flow select and edit the OData Source. The ‘Query options:’ textbox can be used to set options

$orderby

e.g. to order by the Product name: $orderby=Name. To Verify the result, click on the ‘Preview…’ button.

$filter

e.g. select all products with Rating 3: $filter= Rating eq 3

e.g. select products released in the year 2006: $filter= year(ReleaseDate) eq 2006

e.g. select discontinued products: $filter= DiscontinuedDate ne null

$top

e.g. select top 5 products ordered by Name descending: $top=5&$orderby=Name desc

$select

e.g. select only 2 columns on the products table: $select=Price, Name

$inlinecount, $format

Not usefull for SSIS purposes.

$expand

I will investigate this further once I have better samples than the odata.org samples..

SSIS OData Source available from Microsoft

A few days ago Microsoft has released an OData Source for SSIS 2012 / 2014. Downloads can be found in the links below:

Microsoft OData Source for Microsoft SQL Server 2012

Microsoft SQL Server 2014 Feature Pack

Documentation is available in the Books Online OData Connection Manager

Currently I am using SQL 2008 and 2012. There is no OData Source for SQL 2008. I’ve downloaded the OData Source for SQL 2012. Installed the msi file on my SQL test server and on my SQL Development PC with VS 2012.

Add an Odata Source to SSIS package in VS 2012

In a solution with the Project deployment model. Open the the Solution explorer, right click on Connection Manager, select New Connection Manager.

Select ODATA, Click on Add…

The OData Connection Manager Appears

For Service document location I have used the sample sources from odata.org.

http://services.odata.org/V3/OData/OData.svc/

Use Windows authentication to make the connection.

With the Odata Version 4 sample, the connection manager generated an error. Version 3 worked.

Click OK

The connection managers Tab shows the OData Source

Use the OData Source in a Data Flow task

IN the SSIS package with the OData Source, Create a new Data Flow task.

In the Data Flow Task add an OData Source. In the SSIS Toolbox the OData Source can be found under the Common tools, not under the Other Sources.

I cannot help but notice the return of the coloured icons (in contrast to the VS 2012 B&W scheme)

Drag the OData Source onto the Data Flow Task surface.

Edit the OData Source.

OData connection manager: OData Source

Use collection or resource path: Collection

Collection: Products (in the dropdown you will see all available collections (Products, Categories, Suppliers, etc…)

Click on preview to see the Products data (up to 20 first)

In the OData Source Editor look at the columns

Text columns are in Unicode text stream format, Dates are in database timestamp format.

For testing purposes I created a SQL table to accept the products:

CREATE TABLE [dbo].[ImportProduct](

    [ProductId] [int] NOT NULL,

    [ProductName] [nvarchar](255) NULL,

    [ProductDescription] [nvarchar](255) NULL,

    [ReleaseDate] [datetime] NULL,

    [DiscontinuedDate] [datetime] NULL,

    [Rating] [int] NULL,

    [Price] [decimal](12, 4) NULL,

 CONSTRAINT [PK_ImportProduct] PRIMARY KEY CLUSTERED 

(

    [ProductId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

In the SSIS Dataflow task I can now convert the data from the OData source to the format of the destination table in SQL server.

Next, an ADO.NET Destination is added.

The result after running the package: 11 rows are inserted into the SQL table.

Conclusion

The SSIS OData Source is a nice addition to SQL SSIS 2012 an d2014. It works with OData version 3.0. Now all we need is an OData Destination  component.

In my next post I have tried adding query options to SSIS OData source