Publish to SSIS Catalog using PowerShell

Here is a PowerShell script that I use to provision and setup SSIS Catalogs when I’m demoing SQL Server 2012. It does the following:

  1. Connects to localhost (default instance)
  2. Drops the existing SSIS Catalog
  3. Creates a new SSIS Catalog
  4. Creates a Folder
  5. Deploys a Project
  6. Creates an Environment
  7. Creates three server varaibles
  8. Creates an environment reference for the project

Be careful of step #2 above – this script is great for resetting a demo environment, but you’ll want to modify it if you’re using it for real deployments!


TechEd and the SQL Server 2012 Virtual Labs

I had a fantastic time at TechEd North America this year. I didn’t get a chance to attend many sessions, but thankfully they are all recorded – I usually spend a couple of weeks after TechEd crawling through all of the content I didn’t have time to see when I was there. While at the conference, I mostly split my time between the Microsoft product pavilion, and the Hands on Labs (HOL) area. The HOL area is a fantastic way to try out and learn about different Microsoft products. I was very happy to see that many of those labs are available on the SQL Server Virtual Labs site. Here are some of the labs I went through last week:

I also stopped by the certification area at TechEd and took my first ever Microsoft exam… I am happy to announce that I passed the new Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463) exam!

I now officially know how to use SSIS… Smile

PASS Summit 2012 – SSIS Pre-Conference Event

Well, I guess Andy, Tim & I did a good job at PASS last year, as we’ve been invited back to present another SSIS pre-conference event at the 2012 PASS Summit. This time we’ll be joined by the co-authors of our SSIS Design Patterns book, Jessica Moss and Michelle Ufford.


SSIS Design Patterns
Speaker(s): Andy Leonard, Jessica Moss, Matt Masson, Michelle Ufford, Tim Mitchell
In this full-day session, the “SSIS Design Patterns” (Apress, 2012) author team will describe and demonstrate patterns for package execution, package logging, loading flat file and XML sources, loading the cloud, dynamic package generation, SSIS Frameworks, data warehouse ETL, and data flow performance.


We had a great audience and turnout last year (around 120 people if I recall correctly…). This year should be even better…

Should I use the SQL Server Destination?

Short answer:


Long answer:



I was told that some people would like an explanation instead of blindly trusting my advice (weird), so here goes:

In SQL 2005 RTM, the SQL Server Destination was the fastest way to load data into SQL Server with SSIS. Somewhere along the way (in the 2005 SP2 / 2008 timeframe), the performance of the OLE DB Destination (using Fast Load) surpassed the SQL Server Destination in many scenarios. This was due to a couple of different reasons:

  • Multiple improvements were made to the SQL Native Client OLE DB provider
  • Additional checks and constraints were added to the shared memory based loading mechanism used by the SQL Server Destination

I saw results from some performance tests we ran shortly after the 2008 release and was surprised to see that the SQL Server Destination could be 10% faster to 10% slower than loading the same data with the OLE DB Destination. The performance would vary based on the system the tests were run on, the data types involved, and the overall row width. While it does appear that the SQL Server Destination can be faster, it’s just as likely that it will be slower. In most cases, the performance is about the same.

My recommendation is that if you need every bit of performance (a 10% perf increase on a 10 hour load can be significant), try out the SQL Server Destination to see how it works for you. However – keep in mind the following limitations of the SQL Server Destination:

Given these limitations, I recommend using the OLE DB Destination even if you are seeing a performance increase with the SQL Server Destination.

SSIS & EIM Talks From TechEd North America 2012 Now Available

The recordings of my the SSIS sessions I presented at TechEd North America 2012 are now available online.

Incremental ETL Using CDC for SQL and Oracle with SQL Server Integration Services (SSIS) 2012

Enterprise Information Management (EIM): Bringing Together SSIS, DQS, and MDS