Monday, November 5, 2012

PASS Summit 2012

For the fifth year in a row, I am excited to present at the PASS Summit in Seattle, WA.  The PASS Summit is the premier SQL Server conference in the United States for anyone interested in SQL Server, SQL Server Business Intelligence, and Microsoft data technologies.  I always learn something new when I attend, and I enjoy catching up with old friends.  If you’re a new friend that I haven’t met yet, please introduce yourself to me at one of the following locations:

11/5/2012 8:30am-4:30pm SSIS Design Patterns pre-conference session

11/5/2012 6:00pm-9:00pm Networking Dinner at Gordon Biersch

11/6/2012 6:30pm-8:00pm PASS Summit 2012 Welcome Reception

11/7/2012 11:30am-12:30pm Book Signing – PASS Bookstore

11/8/2012 1:00pm-2:00pm & 3:00pm-4:00pm Book Signing – Apress Booth

11/9/2012 9:45am-11:00am Jessica’s Session: “Getting Reports on Your Schedule”

11/9/2012 11:30am-12:30pm Birds of a Feather table: Data Warehousing

Hope to see you then!

Monday, October 22, 2012

SSIS 2012 Design Patterns 24HOP Wrap-up

Thank you to everyone who attended Michelle Ufford's and my presentation on SSIS Design Patterns for the 24 Hours of PASS session.  Michelle's demo materials can be found here:  My demo materials for a 2012 template and some reporting can be found here:

If you missed it, the session recording is available here:

And if you want to see more design patterns, come see us in Seattle for a full day training session:


Thursday, September 20, 2012

SQL Server 2012 Integration Services Design Patterns

A book, a pre-conference training session, and a webinar - oh my!

The Book

Over the past two years, I have been lucky enough to work with some of the great SSIS-gurus, Andy Leonard, Matt Masson, Tim Mitchell, and Michelle Ufford, on a book project that has finally been realized.  I am pleased to announce the publication of SQL Server 2012 Integration Services Design Patterns (amazon | apress).  Thank you to everyone who helped with the writing, editing, and reviewing of the book.

The Pre-conference Training Session
The PASS Summit has invited the ENTIRE author team to give a pre-conference training session on Monday, November 5, 2012.  This training session gives you the chance to ask anything you want from the author team and take home advanced knowledge that can immediately be implemented.  SSIS Design Patterns description:
In this full-day session, the "SSIS Design Patterns" (Apress, 2012) author team – Matt Masson, Tim Mitchell, Jessica M. Moss, Michelle Ufford, and Andy Leonard – 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.

The Webinar
As a preview of our pre-conference session, Michelle and I will be discussing a few design patterns during the 24 Hours of PASS webinar schedule for September 20, 2012 at 10:00PM EST.  I've had a blast working with Michelle, and I believe this session will give you something to use today, as well as provide an idea of what you can learn in a whole day session.  Tune into SSIS Design Patterns for Fun and Profit:
As a preview of the PASS Summit 2012 pre-conference session "SSIS Design Patterns" by Andy Leonard, Matt Masson, Tim Mitchell, Michelle Ufford, and Jessica Moss, Michelle and Jessica will present design patterns to assist in the day-to-day day activities of SSIS 2012 development. Learn how best to set up a template package, to utilize the framework for your SSIS development, to review the metadata of executions, and more. This online session will provide you information you can use today as well as provide a preview of the types of things you can learn in this fall's pre-conference session.

I look forward to hearing your thoughts on our book, seeing you at the training session, or answering your questions on the webinar.  Happy ETL-ing!

Friday, September 7, 2012

Upgrading and Overhauling SSIS Q&A

Thank you to everyone who attended my School of Wrox presentation "Upgrading and Overhauling Your SSIS Packages for 2012" last month.  There were many remaining questions at the end of the session that we weren't able to fit.  The questions below are the unaddressed ones asked during the session that are specific to upgrading to 2012.  Enjoy!

Will SSIS 2008 package run AS IS in 2012?

Yes, the SSIS 2012 service will temporarily convert the package in its 2005 or 2008 format to the 2012 format when the package runs.  The package may not execute properly if it references providers or assemblies that are not available, so upgrading the package before deploying is highly recommended.  For more information, see:

Can we integrate file based execution in catalog in DB and generate reports about the executions?

Storing packages for execution on the file system is only possible in the Package Deployment Model.  To utilize automatic logging, you need to deploy your SSIS packages to the SSIS Catalog.

Is it mandatory to create a Project deployment even if there is only one package in the Project?

You can run one package using the Package Deployment Model, but you will not have access to the 2012 features, such as automatic logging and parameterization.  To use the new 2012 features, you will need to use the Project Deployment Model, no matter how many packages the project includes.

If you need to change one package within the Project, do you redeploy the whole Project or you can select to redeploy the changed package only?

You will need to redeploy the whole project.  For additional information on this, please see this blog post:

Is there a manifest file created during the deployment (just like in 2005) which a DBA uses to deploy the packages?

Yes, in the Project Deployment Model, an .ispac file is created which can be used to deploy packages.

Can we have more info on the that stored proc that is used to call the SSIS jobs?

To execute an SSIS package, you can use the create_execution stored procedure.  For more information, please see:

Is there a way to have a "global" set of parameters that would apply to all projects?

Environments are applied at a project level, but can be programmatically created.  To mimic a global set of parameters, I would try a programmatic environment creation script that could be applied to each project, keeping in mind that it would be copied multiple times.

Can we still use dtexec to run the packages from the cmd prompt?

Yes, you can still use the dtexec executable.  Note that behind the scenes, the executable is still calling the stored procedure referenced above.  For more information, see:

Will the upgrade tool have issues with a package that has third party data flow tasks?

To use custom components, you can update configuration files, as described here:  In the long run, it is recommended to upgrade your components to 2012.

Tuesday, February 28, 2012

Learn Reporting Services in a Day!

I am excited to be presenting a pre-conference session at the Atlanta SQLSaturday on April 13, 2012.  The session is entitled Learn Reporting Services in a Day! and is a full day introduction to SSRS 2008 R2.  It costs $99.00 through March 15, and $109.00 after that.  There are only 16 seats left, so don’t hesitate to sign up here:

In addition, there is a full slate of sessions on April 14, 2012, including my session: Report Parts: Increasing Productivity Since 2008R2.

Here is the information about the pre-conference session:

SQL Server MVP, Jessica M. Moss, presents an exciting, introductory, full day training session on SQL Server Reporting Services 2008 R2. In the three-part class, Jessica will teach you how to build reports from the ground up. In Part 1, learn the basics of report development, including picking a report development tool and creating your first report. Part 2 delves into visualizations, groupings, and drill-down functionality. Finally, Part 3 highlights core administration tasks in Reporting Services. In addition, Jessica will point out industry-wide best practices for report development and show numerous live demos using a variety of data sources.


101: The Basics

  • Introduction
  • Report Builder vs. BIDS
  • Data Sources & Datasets
  • Set Reports
  • Additional Toolbox Items
  • Interactive Exercise A


201: Intermediate

  • Visual Reports
  • Dashboard Reports
  • Grouping/Sorting
  • Parameters/Filters
  • Calculated Expressions
  • Drill-Down and Drill-Through
  • Interactive Exercise B



  • Configuration Modes
  • Report Manager
  • Deployment
  • Delivery
  • Security

I look forward to seeing you there!

Wednesday, February 22, 2012

Displaying Images from Analysis Services

One of the things we love in business intelligence is pretty pictures. We want to be able to show graphs and KPIs that really highlight information and direct people to an accurate conclusion. We also want to be able to show images that help tell a story, such as a picture of a certain location or the logo for a store. Analysis Services provides the functionality to show images, but it’s a little buried. Let’s walk through the steps of making it easier for you to access! Our final product will look like this:


Setting up Analysis Services to show the image

Let’s work with the AdventureWorks 2008 R2 sample database and Analysis Services cube. In the product dimension, there is already an attribute in the database and Analysis Services cube called LargePhoto with a data type of varbinary(max). This contains an image that is associated with each product in the dimension. In fact, in the Analysis Services dimension, we can see that it has been set up so that the ValueColumn for that attribute uses that value, while the key (and by default, the name) column uses the surrogate key of the product dimension.

To make the logo as accessible as possible, we want to include the value as a measure. This will allow report developers to drag and drop the value directly onto their query pane in Reporting Services.

Let’s create a measure called [Product Photo] with the expression:

[Product].[Large Photo].MEMBER_VALUE

Using the image in Reporting Services

After building and deploying the new cube, we can set up our SSRS dataset.


We use the dataset in a table that contains Products in the first column’s detail row, and an image in the second column’s detail row. Set up the image properties as shown here:


This will use the photo associated with that product to display as an image for each row, and you get the final product as shown above!

Version used: SSAS 2008 R2, SSRS 2008 R2, Report Builder 3.0

Saturday, January 28, 2012

SQL Server Reporting Services is Free!

You may not be aware of this great side of SQL Server: free Reporting Services. When I say free, I don’t even mean “comes with the SQL Server that you already paid for, so is sort of free”, I mean “any random person off the street can use it for free, as in beer” I know, the first time I heard it, I was a little skeptical too. Fortunately, it is absolutely true!

SQL Server Express Edition with Advanced Services comes with the ability to create your own reports. There are some limitations of this version; for a full list see this site. A few of the larger limitations are listed here:

  • You can only display data from the SQL Server Express databases on the same server
  • The Reporting Services metadata will be stored in the SQL Server Express database on the same server
  • You can only run on-demand reports, which means no subscriptions

This version is a great way to try out the capabilities of Reporting Services and learn how to develop reports. And on top of that, everything you create can also be deployed to another edition of Reporting Services. You can download the Express version here:

If you’re interested in learning about general reporting or the capabilities of Reporting Services, I highly recommend you check it out!