Presentations Galore!

I’m mentally preparing myself for a whole slew of upcoming conferences / speaking engagements over the next month and a half. I love talking about SQL Server technologies, and I’m honored that I’ll have so many chances to do it in the next few weeks!

Please come see me at one (or more) of the following events:

Can I Automate SSIS Project Deployment?

Yes, yes you can. Scripted or automated deployment can be done in a number of ways in SQL Server 2012.

Integration Services Deployment Wizard

Yes, this is the primary UI tool for SSIS project deployment, but it can also be run silently on the command line. When you run through the wizard, the Review page will actually list all of the parameters you need to do the same deployment from the command line.


Run ISDeploymentWizard.exe /? from a command prompt, and you’ll see the full list of arguments it supports.


The SSISDB [catalog] schema has a number of public stored procedures, including one that can be used for deployment. We even provide samples on how to use it in Books Online.


All SSIS Catalog operations can be automated through PowerShell. I previously blogged about a deployment script I use to setup my demos.

Custom Utility

The SSIS Catalog management object model (MOM) exposes a set of SMO classes you can use to code your own catalog utilities. You’ll want to use the CatalogFolder.DeployProject method to do the actual deployment. If SMO or .NET isn’t your thing, you can also code a custom utility which interacts directly with the T-SQL API.

Data Flow Optimization Checklist


I put this together for an SSIS performance presentation I did a while ago, and thought it warranted its own post.

In addition to looking for appropriate Design Patterns, I have a checklist of things to look for when trying to optimize an SSIS Data Flow. This list is by no means extensive, but it contains some of the more common causes of performance issues in the packages I’ve worked with.

OLE DB command operates on a row by row basis. Stage the data and perform the operation in a batch mode using the Execute SQL Task instead.

Learn MERGE, and love it.

Sometimes you do. Most of the time you don’t. If you do need them (i.e. you are loading from a flat file, and need to do a merge), considering maxing out the DefaultBufferMaxRows setting so you can sort more data in memory (sort will keep at most 5 active buffers in memory before it starts swapping to disk).

  • Lookup Transforms
    • Can the lookup be avoided?
    • Are you using the right cache mode?
    • Should you use a Cache Connection Manager?

Lots of related lookup posts can be found here.


Have fun optimizing!

Webcast: Developing Extensions for SSIS | 2013-05-22

Andy Leonard will be joining me to present a Developing Extensions for SSIS webcast on May 22nd. It’s been a long time since I’ve had the chance to present a code heavy session like this, and I’m really looking forward to it. I’ll be using this session as a dry run for a talk I’ll be giving at the DevTeach conference in Toronto the following week, so feedback will be much appreciated.

Andy has a great blog series about creating custom SSIS Tasks, and his company (Linchpin People) recently released an SSIS Event Task. I plan on expanding on the content in Andy’s blog posts a little bit, and talk about some of the key things you need to know when building an SSIS Data Flow component.

The abstract for the talk is available below, and registration page can be found here.


Join Matt Masson and Andy Leonard for a discussion and demonstrations on extending SSIS with custom tasks and data flow components. This code heavy session walks you through the creation of a custom SQL Server Integration Services (SSIS) Task, and a custom Data Flow component. If you’ve ever thought of customizing SSIS functionality beyond what you can do with scripting, this talk is for you! All examples will be in coded in C#, and made available following the session.

Windows Azure Application Gateway


I’m not sure when it started, but for a while now I’ve found it hard to just sit and watch TV or a movie with my family. I feel like it doesn’t occupy my mind enough, and I need to be doing something else at the same time. My Surface has come in really handy here – usually if I’m watching something, I’ll be doing something with it at the same time. I’m sure I’m not alone in this.

There’s a lot of stuff going on within my organization, and I like to have a general idea of what everyone is doing. Recently I’ve been spending a lot of time reviewing specs, wireframes and power point presentations from other teams. We make heavy use of SharePoint internally, both the corporate and cloud versions, and that’s generally where I go to find the docs I’m looking for.

I thought I could make good use of my “quality family time in front of the TV” by combining these two activities. However, when I’m at home, accessing the documents I need from my tablet can be tricky. When they are stored in SharePoint Online, I can get to them through my browser, but getting to documents in our internal SharePoint sites is harder because they are behind the corporate firewall. I started searching around for solutions, and came across a new service called the Windows Azure Application Gateway. Microsoft has ways for employees to connect to the corporate VPN on a Surface (using our smartcards), but this new service seemed like a light weight approach and looked interesting.

Windows Azure Application Gateway


The interoperability @ Microsoft blog had a great blog post about this service:

For obvious security reasons, connecting to resources that are behind a firewall usually requires a fairly complex infrastructure such as VPN (Virtual Private Network). The AppGateway demo app is designed to make the process simpler yet highly secured. The mobile app connects to a service on Windows Azure that acts as the proxy to an agent that is running inside the network behind the firewall. Using the Windows Azure Authentication service, the proxy can establish a trusted connection to the agent so that the application on the mobile device can browse web sites that would not be normally accessible outside the corporate network.

The site has an easy to understand explanation of what the service does, and how it works. It was actually what caught my eye… I really like the graphic they use. I call him Mr. Firewall.


The usage scenario they described fit what I was trying to do – there were corpnet resources I wanted to access, but couldn’t access them from home.


Many companies will provide a way to connect to their internal corporate network (for example, DirectAccess or connecting through a VPN). These solutions tend to work fine from a laptop, but might not always work so well for a tablet or phone. Surface RT supports VPN connections, but many corporate networks require some form of custom VPN client or smartcard solution that might make it harder (or more awkward) to use. If your company doesn’t offer a VPN solution, or you’re in a reverse scenario where you want to access your home network while you’re at work, then you might be out of luck.


Their walkthrough graphics got me hooked, and I decided to try it out.


To use the service, you need to create login with either Windows Azure Active Directory (WAAD) or Office 365. (You can sign up to WAAD for free).

Once you have a login, you can download the connector and install it on your corporate machine (or your home machine, if you’re trying to create a bridge from work to home).


After install, you’ll see an Application Gateway icon in your notification area, and a browser pops up asking you to login and register your connector.


After registration, you get a webpage dashboard on your connector machine, with live tiles.


I like the detailed status window that lets you test your connectivity:


Once your connector is setup, you can use it to access web sites in a few different ways. There are custom iOS and Android client apps available, and on a windows OS (Windows Phone, Surface, PC) you access the Web Client directly using your browser. Here is a screenshot from my Surface RT:

appgateway from surface

Click on the name of your connector, and it brings up a web browser interface. From here you can enter intranet URLs. The data from the site gets passed back through your connector machine and rendered on your device. It’s as if you were directly connected to your network using a VPN.


I’ve just started using the service, but it seems to work great so far. I’m able to browse internal SharePoint sites, and view (and edit) Word/PowerPoint files on my Surface. It hasn’t been out long (it’s still in Preview), so I expect there will be further improvements before it becomes generally available.

A more detailed explanation of how the service works can be found here, and it looks the source code for their Android demo app is available on Github.

Try it out and let the team know what you think!

How to Localize Your Custom Connection Manager

Similar to localizing a task, you can localize the display name and description of a custom connection manager by setting the LocalizationType property of the DtsConnection attribute. SSIS will look for two properties on the class:

  • ConnectionDescription
  • ConnectionDisplayName

These must be public, static string properties on your LocalizationType class.

The code looks like this: