Implementing an Master Data Management Solution With SQL Server Master Data Services and the MDS Utilities (Part 2 Loading the Model)

 [This post uses the old staging functionality from MDS 2008 R2.  With MDS 2012, you should be using entity-based staging which will provision the staging tables for you. See eg http://msdn.microsoft.com/en-us/sqlserver/Video/hh802433] In the previous post in this series on using the MDS Utilities CodePlex project (http://mdsutilities.codeplex.com/ ) to jump-start and MDS implementation  we created a…

1

Implementing an Master Data Management Solution With SQL Server Master Data Services and the MDS Utilities (Part 1 Creating the Model)

  I’ve been working with a number of customers to implement SQL Server Master Data Services, and found some common requests from customers that require some custom code to implement.  Over the course of those engagements I wrote a set of utilities for MDS that are now published on CodePlex http://mdsutilities.codeplex.com.  The questions I kept…

4

Remote SSIS Package Execution with PowerShell 2.0

Here’s how to run SSIS packages on a remote server using PowerShell 2.0 and PowerShell remoting.  This post is short, since this is so incredibly easy. First install PowerShell 2.0.  For Windows Server 2008 R2 and Windows 7, these are in the box.  For previous version of Windows they are available here: Windows Management Framework…

2

How to Configure an SSIS Package to Access a Web Service using WCF

When you are connecting to a web service from an SSIS Script component or transform using a WCF client, the normal method of configuring the WCF client from the application configuration file doesn’t work well.  Your package will be running in some host, like dtexec.exe, and you would have to put your client config its…

14

Batch File to Grant Local Administrators a Sysadmin Login in SQL Server

In SQL Server members of the local administrators group are often configured to log in as sysadmins.  A sysadmin, of course, has complete control of the SQL Server instance.  Some people remove the local administrators login from SQL Server to prevent access by non-DBA Windows admins.  This is a reasonable configuration, but it doesn’t prevent…

12

using new TransactionScope() Considered Harmful

Hopefully you are familiar with the greatness of the TransactionScope class in .NET.  It provides implicit ambient transaction handling, which can greatly simplify transaction handling in your code. But this ease of use comes with a significant caveat.  The TransactionScope’s default constructor is, for the purposes of SQL Sever database programming, broken.  TransactionScope’s default constructor…

14

Indexes Supporting Foreign Keys

Most developers building an OLTP system know that you should declare and enforce relationships between tables using Foreign Keys.  But what physical database structures should you implement to support those relationships?  Every Foreign Key is a relationship from one or more columns on the table on which the Foreign Key is declared to a Key…

1

Oracle Database Connectivity for the Microsoft BI stack (Part 1 Oracle Client)

There’s lots of blog posts out there on how to get your SQL Server BI environment to talk to Oracle, but lot’s of it is a bit incomplete, so I’m going to take a crack an publishing a complete guide.  I’ll start with installing the Oracle client and getting it running with BIDS (Visual Studio)…

3