SQL Saturday #146 | Nashua, New Hampshire


I’ve been invited to speak at SQL Saturday #146 in Nashua in October. I’ll be presenting two talks related to Data Movement:

Efficient On-Premise to Cloud Data Transfer

Thinking about moving some of your operations to Azure? Have multiple remote sites, and want to use the cloud to centralize and share data between them?? Just like hearing talks about data transfer performance?! Have we got the session for you! We’ll cover some common user scenarios, and describe when and how to use the latest Microsoft data transfer technologies, including SQL Server Integration Services (SSIS), SQL Data Sync (a capability of SQL Database), and more.

EIM – Bringing Together SSIS, DQS and MDS

Enterprise Information Management (EIM) is an industry term for managing your data for data integration, quality, and governance. This session revolves around a demo which brings together the EIM functionality in SQL Server, a key part of our Credible, Consistent Data story for the 2012 release. We will show you how SQL Server Integration Services (SSIS), Data Quality Services (DQS), Master Data Services (MDS) and other Microsoft technologies work together to provide a comprehensive EIM solution.

Both are intermediate (200-300) level talks, and will cover multiple technologies. I’ll be using this as a venue to preview of some of the new content I’ll be presenting at the PASS  Summit in November. I’ll most likely end up in SSIS Design Pattern book co-author Andy Leonard’s (Blog | Twitter) two SSIS sessions, as well – I always love to see other people present SSIS topics, and Andy is one of the best SSIS presenters around.

The full schedule is now available on the event site. If you’re at the event, please come by and say hello! I’ll be happy to sign your ebook edition of my two SQL 2012 books.

The timing is perfect, as I’ll be presenting to the New England SQL Server User Group a couple of days before… Hope to see you at both events!

Dynamic Execution of Child Packages with Different Parameters

SSIS 2012 introduces the concept of Parameters – a more explicit way of configuring your SSIS packages that you can use with the Project deployment model. The Execute Package Task was updated to support binding variables in the parent package to parameters in the child package (an alternative to the Parent Variable Configurations in SSIS 2005/2008).


Clicking the Add button on the Parameter bindings page will automatically populate the binding list with the next un-bound parameter for the child package you have selected to run. Note, however, that you can manually enter a parameter name in the Child package parameter field, even if it doesn’t actually exist on the child package.


At runtime, any parameters that don’t exist on the child package don’t cause any errors or warnings at runtime. There is a reason for this…

Dynamic Package Execution

You can change the which child package the Execute Package Task runs using an expression on the PackageName property.


Since not all packages will have the same set of parameters, the Execute Package Task allows you to define a superset of parameter bindings at design time. Parameters that don’t exist for the current child package will simply be ignored.

For example:

  • ChildPackage1 requires ParameterA and ParameterB
  • ChildPackage2 requires ParameterC
  • ParentPackage has logic that will dynamically call either ChildPackage1 or ChildPackage2 at runtime

To do this, you would add parameter bindings for all three parameters (ParameterA, ParameterB, ParameterC) in the Execute Package Task in your parent package.


More details and parent-child package execution design patterns can be found in Chapter 16 of the SQL Server 2012 Integration Services Design Patterns book available from Apress.

Book – Microsoft SQL Server 2012 Integration Services comes out September 17th

Update: Amazon is now reporting a release date of 9/17 although it appears you can order it immediately from the O’Reilly site.

My second SSIS book – Microsoft SQL Server 2012 Integration Services (MS Press) – comes out on September 17th. This book takes a different approach than my Design Patterns book that came out last week – it’s more of an in-depth look at the internals of SSIS in SQL Server 2012, rather a problem/solution type of guide. My co-authors (Wee Hyong Tok, Rakesh Parida, Xiaoning Ding, and Kaarthik Sivashanmugam) are all members of the SSIS product team (or at least were when we started writing the book… Rakesh and Kaarthik have recently moved over to the Data Quality Services team), which allowed us to take a deep look into how the product was designed.

Here a full list of chapters – a sample chapter is available from the O’Reilly website.

  • Chapter 1 : SSIS Overview
  • Chapter 2 : Understanding SSIS Concepts
  • Chapter 3 : Upgrading to SSIS 2012
  • Chapter 4 : New SSIS Designer Features
  • Chapter 5 : Team Development
  • Chapter 6 : Developing an SSIS Solution
  • Chapter 7 : Understanding SSIS Connectivity
  • Chapter 8 : Working with Change Data Capture in SSIS 2012
  • Chapter 9 : Data Cleansing Using SSIS
  • Chapter 10 : Configuration in SSIS
  • Chapter 11 : Running SSIS Packages
  • Chapter 12 : SSIS T-SQL Magic
  • Chapter 13 : SSIS PowerShell Magic
  • Chapter 14 : SSIS Reports
  • Chapter 15 : SSIS Engine Deep Dive
  • Chapter 16 : SSIS Catalog Deep Dive
  • Chapter 17 : SSIS Security
  • Chapter 18 : Understanding SSIS Logging
  • Chapter 19 : Automating SSIS
  • Chapter 20 : Troubleshooting SSIS Package Failures
  • Chapter 21 : SSIS Performance Best Practices
  • Chapter 22 : Troubleshooting SSIS Performance Issues
  • Chapter 23 : Troubleshooting Data Issues

Speaking at the New England SQL Server User Group

I’ve been invited to do a presentation about Data Quality Services for the New England SQL Server User Group on October 18th. I’ll be presenting an in depth look at DQS, what some of our customers are currently doing with the product, and might even have time to show how DQS fits into larger Enterprise Information Management solutions as well.

The abstract:

Microsoft’s SQL Server Data Quality Services (DQS) is a unique solution that is based on the creation and maintenance of Data Quality Knowledge Bases (DQKB) and the ability to use them efficiently for a variety of Data Quality improvements. In this session we’ll walk the creation of a DQS solution, discuss the main concepts behind the creation of the DQKB, and how to use DQS in various scenarios and activities.

For more details, please see the event posting on the NESQL site. I’m told that to attend, you must be on the group’s mailing list (the registration link is available on their website) so you can RSVP when the invitation email goes out.

Hope to see you there!

Learn more about SSIS Design Patterns at the 2012 PASS Summit

We managed to bring together the entire author team for the SSIS Design Patterns book to run a pre-conference event at the 2012 PASS Summit. I posted about this when it was first announced, but since our book came out today, I thought it would be a good time to post a reminder.

We’ve been meeting over the last couple of weeks, trying to decide on what content to include in the session. It’s a lot harder than I thought it would be – there is just so much we could talk about. We have all of that content from the book, of course, and there’s always the desire to include brand new material as well. On top of that, we could draw on content from the pre-con that Andy, Tim and I did last year, the Performance Design Patterns talks I put together, Andy’s framework courses, Tim’s upcoming Real World SSIS session (SQL Saturday 163), and the great stuff from Jessica and Michelle’s blogs… You see our dilemma. I have a feeling the eight hour session is going to fly by.

I’ve always felt that having too much content is better than not having enough, and that is definitely the case here. I think the audience will be happy with what we select, though, and we’ll make sure that the topics that didn’t make the cut will be available online (in one form or another).

The event will take place on Monday, November 5th, 2012. If you’d like to join us for the day, you can register here.