Cloud, google, SQL Server 2012, SQL Server 2014, SQL Server 2016, Uncategorized

SQL Server on GCP

I recently tried out running SQL Server 2016 on a Google Cloud Platform Windows-based Virtual Machine (GCE – Google Compute Engine Service).

This is a quick way to try out new features of the latest version of SQL Server.  In this case, I wanted to test out the R (language)-in database services.

Although you can certainly ‘click’ in the GCP console to start an instance of SQL Server on GCE,  you may want to script activity (for use with the Google gcloud tool).  To that end, I created a simple script to do this.  Also I added a script to enable and test the R-in database feature.  Here’s a link to my GitHub Repo.

What do you think?  Interested to try this out?  Let me know how it goes for you.



Cloud, Data Science, Microsoft, SQL Server 2012

Using D&B Company Cleanse Match with PowerBI and SSIS

I’ve been doing some work with the Dun & Bradstreet Company Cleanse & Match offer in the Windows Azure Marketplace.  A common data quality scenario I encounter is the business need to create more complete customer (company) records for various business reasons (marketing, collection, etc…).

Shown below is a sample of how this offer works.  You can see that three records with different types and amounts of information have been combined into one completed record.  You may also note that the D&B D-U-N-S number has been associated with the identified company.

D&B Cleanse&Match
D&B Cleanse&Match

This D&B offer brings three important concepts to this table:

1) The D&B business database is global, comprehensive and verified.
2) D&B uses a proprietary, powerful and configurable cleanse/match algorithm to correct, complete and de-duplicate records.
3) D&B offers flexibility in terms of integration with Microsoft APIs and Tools.

I’ve already blogged (and screencasted) about the integration between D&B Cleanse & Match and SQL Server 2012 Data Quality Services.  In today’s blog, I’ll include information about working with SQL Server 2012 SSIS and about integration with PowerBI.

Note: In addition to the D&B Cleanse & Match offer, D&B has several other offers in the Windows Azure Marketplace and also in the Windows Azure Store.  For example, here is a screenshot of the integration of their ‘Business Insight’ offer from within the Windows Azure Store (in the Windows Azure portal).

D&B Business Insight
D&B Business Insight

If you are at the SQL Pass Summit this week, be sure to stop by the D&B booth to get access to the preview SSIS component and to learn more about their many offers on the Windows Azure Marketplace.  They are also running some fun contests (for cash!) at the show.

Below are a series of screencasts which show the integration between D&B Cleanse & Match and Microsoft products in greater detail.  First is the SSIS component demo.

Next is a series of 3 demos, which provide a detailed use case (creating a rich customer contact list for a growing business in a particular industry) using public data, PowerBI and D&B data and algorithms to produce a complete, validated, useful prospect list.

In part one, I use PowerQuery to shape public (US Census) data

In part two, I use PowerQuery and D&B data to create a targeted company contact list, with the attributes I value for this scenario (such as ‘green-certified’) and those that I’ve identified based on my earlier data research (such as which US States I was to focus on).

In part three, I again use PowerQuery and D&B data to further enrich the prospect list, by adding actual contact information (names, email addresses and phone numbers) to produce an actionable prospect list for my marketing team. I also show the new Data Gateway (Eldorado) from Office 365.

Are you interested in learning more?  Check out this information page.

Big Data, Cloud, Data Science, Microsoft, SQL Server 2012

Working with DnB Company Cleanse Match Data

DnB Cleanse Match

I’ve been working on some data cleansing projects lately and to that end I’ve tried out working with the DnB Company Cleanse Match Dataset in the Windows Azure Marketplace.  This dataset allows you get more complete information about companies and to combine duplicate records.  Shown below is a screenshot which illustrates what you can do with this service.

DnB Company Cleanse Match

To try it out, you can email DnB for a promo code (send mail to ‘‘).  You can use this service in a couple of different ways, these include using it with Excel (PowerQuery or any other service that supports consuming OData feeds), SQL Server 2012 Data Quality Services or programmatically by downloading the proxy class for C# from the Azure Data market (available after you subscribe to the service) and coding against the API.

I’ve made two screencasts to show how this works.  First, here’s the screencast on Power Query / API.

Second, here’s the screencast using the dataset with SQL Server 2012 DQS.

Also here’s the stub code for the API:

string USER_ID = "<windows live id user id>";
string ACCT_KEY = "<your key>";
var ROOT_URI = "";
var serviceClient = new DnB.DnBContainer(new Uri(ROOT_URI));
 serviceClient.Credentials = new NetworkCredential(USER_ID,ACCT_KEY);
var l =
(from d in serviceClient.SuggestCompanyDetails
 ("Dell", null, null, null, "TX", null, "US", null, 3, 0)
 select d);

foreach (var a in l)
    Console.WriteLine("Result " + a.DunsNumber);


SQL Server 2012

Trying out DnB Data with SQL Server 2012 DQS

I tried out a new service offered in the Windows Azure DataMarket from Dunn & Bradstreet which is designed to be used with SQL Server 2012 Data Quality Services.  If you’d like to try this out as well, then email to D&B at this address ( and they will email you a promo code so that you can try it out as well.

Also, here is a blog post from the Microsoft DQS team which also details the process.

If you are new to DQS – I have 11 screencasts on YouTube that you can try watch to learn more.

SQL Server 2012

Updates in SQL Server 2012 SSIS Packages

In working with some customers lately, I happened on some of the the enhancements to the SQL Server 2012 SSIS deployment model.  These include the ability to deploy based on projects using the new SSISDB catalog (rather than by individual packages) and also to work with parameters.  Here’s the MSDN reference about these changes.

Also I made a short screencast to demonstrate how these features work (including the new Integration Services Project Conversion Wizard) – enjoy!

Are you using these new features?  Which ones are providing the greatest value for you?