Getting Started with Parameters, Variables & Configurations in SSIS 2012

Overview:  This is a high level review of the terminology for configurable items like parameters and variables in SQL Server Integration Services 2012.  This discussion is applicable to the Project Deployment Model only.

Following is a high level flowchart of how values can be passed to parameters, variables and connection managers in SSIS 2012.  The left side represents the SQL Server Data Tools environment in Visual Studio (i.e., during development before the project has been deployed); the right side represents the SSIS Catalog in the Management Studio environment (i.e., after deployment).


The remainder of this blog entry will discuss individual components of the above flowchart.

SSIS Project & Package Parameters

imageProject parameters are new with SSIS 2012.  A project parameter can be shared among all of the packages in an SSIS project.  You want to use a project parameter when, at run-time, the value is the same for all packages. 



imagePackage parameters are also new with SSIS 2012.  A package parameter is exactly the same as a project parameter – except that the scope of a package parameter is the individual package it resides in.  You want to use a package parameter when, at run-time, the value is different for each package. 


Note that project parameters do *not* have an expressions property to define their value.  They are intended to hold a literal value which does not change while the package executes.

See in the image at the top of the page that project parameters can pass a value to variables?  Parameters can also pass values to all kinds of objects in SSIS – basically any property that allows an expression.

You can think of parameters are the replacement for package configurations which were used in earlier versions of SSIS.

More details on defining parameters:

SSIS Variables

imageThere’s actually not too much new here with *package* variables in SSIS 2012 (other than you can move them now which is great).  What can get confusing is that sometimes the environment variables are just referred to as variables – so you need to be aware of the context in which variables are being discussed.  (Environment variables are discussed in the next section below.) 

Within a package, SSIS variables have an expression property.  The expression property, and the ability to change values during the execution of a package if needed, are two fundamental differences variables have from parameters.  A variable can be scoped to the package or an object within the package (there’s no such thing as a project variable though). 

Variables often consume values from parameters within their expressions (an example of this is in the next screen shot).  Variables can also communicate values to objects like Execute SQL tasks, For Each containers, Send Mail tasks, and so on.


More details on SSIS variables:

More details on the differences between parameters and variables:

More details on SSIS variables:

SSIS Environment Variables

imageSSIS environment variables are new with SSIS 2012.  You actually interact with these in Management Studio after the project has been deployed to the SSIS Catalog.  Don’t confuse these with Windows environment variables – although named the same, SSIS environment variables are different than Windows environment variables.  Also don’t confuse these with “regular” variables used within SSIS packages (which are discussed in the previous section above).

An environment variable provides the flexibility to configure values for parameters and connection managers in Management Studio which are different from what was originally specified when the package was deployed.  This is great functionality for the administrator of the SSIS and ETL processes.

An environment(s) and its variables are set up in Management Studio under Integration Services Catalogs.  Once set up under the Environment Properties, these variables can be associated to projects and/or packages.  Since their purpose is to override parameters or connection managers, I propose a similar name to the value it’s intended to replace – but with a prefix (such as EV) that makes it clear where the value is coming from.


If you wish to override the value for a project parameter with an SSIS environment variable, you do this on the “Configure” menu for the project.


Overriding the value for a package parameter with an SSIS environment variable is very similar – it’s just done on the Package’s “Configure” menu instead.

Note that SSIS environment variables can specifically provide values for parameters and connection managers.  SSIS environment variables do not interact directly with the variables contained inside of SSIS packages.

More details on SSIS environment variables:

More details on SSIS environment variables:

SSIS Project Configurations

imageWith the new project deployment model, the concept of configurations is mostly gone.  There is one exception, however.  While in SQL Server Data Tools (Visual Studio), you can specify if any parameters are dependent upon a particular deployment configuration being selected.



This reference to “deployment configurations” are not the configurations you might be thinking of from previous versions of SSIS – rather, these are the deployment configurations available in the project properties.  This deployment Configuration Manager has been available to manage different deployment scenarios for a long while now.  The piece that is new is the ability to associate parameters to these configurations.


This functionality is only available in the Visual Studio development environment, and only applies to project & package parameters.

Hope that helps clarify some of the terminology in SSIS 2012!



PASS Summit Meeting Rooms at the Charlotte Convention Center

imageThe schedule for the PASS Summit in Charlotte has been released.  Since I’m lucky enough to have a session on the schedule, I looked up a map of the Charlotte Convention Center to get the lay of the land.  Having an idea of the room size ahead of time helps me to feel mentally prepared. 

The schedule for the 2013 PASS Summit can be found here:

The layout for the Charlotte Convention Center can be found here:

The rooms highlighted in yellow are the convention center rooms used per the Summit schedule.  I’m guessing the rooms will be set up theatre-style, but that's just a guess.






By the way, we would love to see you at our SQL Saturday the day after Summit concludes.  It’ll be a fun day with 50 sessions on BI, Data Warehousing, Big Data, and Database Administration -- so please join us!  Info can be found here:



Power Query Data–Should it be Loaded to the Worksheet?

Overview:  This post discusses situations when you may *not* want to load Power Query data to the worksheet, but instead to the data model only.  One reason relates to file size limits in Power BI for Office 365; the other reason is to simplifying & de-duplicate the fields displayed in the Pivot Table Field List.

Power Query Settings

When you bring data into Excel via the Power Query add-in, you have some options on the Query Settings pane. 

Enable download:  Needs to be set to On in order to retrieve or download the data from the source.

Load to worksheet:  Specifies whether the data downloaded will be stored in an Excel table within the worksheet.  Default = Yes.

Load to data model:  Specifies whether the data downloaded will be stored in the data model (i.e., Power Pivot).  Default = No.


There’s a couple of reasons why you might want to flip-flop the above settings.  Specifically:

Load to worksheet:  Change to No.

Load to data model:  Set to Yes.

Following are a couple of reasons why you may want to consider loading to the data model only.

File Size Limits for Browser Display in Power BI for Office 365

The default file size limit in Office 365 for browser display is 10MB.  That limit for browser display has been increased to 250MB if all 3 of the following are true:

  • The workbook is enabled for Power BI (which requires SharePoint Online Enterprise E3 or E4 plan), and
  • The workbook contains a data model (i.e., an embedded PowerPivot model), and
  • Data in the workbook outside of the data model doesn’t exceed 10MB.  <--includes Power Query data residing in an Excel table

This means you want to be very careful when working with Power Query to not necessarily import its data to the workbook, particularly if the volume of data is high.  The two-step process to import only to the data model would be:

  1. Move the slider “Load to worksheet” to Off.  <--Important to click this one 1st before it becomes greyed out when you click #2.
  2. Click the “Load to data model” option.


The reason this distinction makes a big difference in the file size is because the data model uses xVelocity compression.  This is a much higher compression level than standard xlsx data.

Duplicates in the Pivot Table Field List

Loading the data to only the data model and not the accompanying workbook has another benefit.  You won’t see the set of data twice in the pivot table field list.


It’s much cleaner having one clean set of fields to choose from.  You can still use the “Filter & Shape” functionality of Power Query without loading the data to the worksheet.

Also, be aware that there’s some situations where Power Pivot will implicitly convert data to the data model, such as:

  • When Power Map is used within an Excel workbook.  Power Map has a dependency on the data model for its functionality, so if you have not explicitly created a Power Pivot data model, it will do so on the fly when you insert a Power Map worksheet.
  • When you import two or more tables at once.
  • When you insert a Power View worksheet using a Table range of cells.

Finding More Information

Office – File Size Limits for SharePoint Online



Navigating the Power BI Site in Office 365

Overview:  This post includes links for getting around Office 365, specifically as it pertains to the use of Office 365 for the Power BI Preview (i.e., not all O365 menus will be explored here).  Intended audience is the system administrator.  Purpose of this blog post is not to discuss functionality, just links and locations within the O365 portal.

Please note that this was published on 9/2/2013 – the look and feel of the menus and page structure will undoubtedly change as O365 progresses.  All screen shots below presume you have administrator privileges to the site, so you may see less things based on your O365 plan and your permissions.

Office 365 Contextual Menus and Links

Power BI is delivered using SharePointOnline within Office 365.  You can think of it as SharePoint in the web using HTML5.  A lot of SharePoint concepts will be very familiar.  However, navigating around is a bit different.

Depending on where you are at in the site, all menus are not visible.  The menus are contextual (i.e., they change based on current context - where you are located in the portal).  Also, breadcrumbs are only minimally used.  So until you have figured out the site and bookmarked some key pages, you might feel like you are hunting around for where you were or where you need to get.  The remainder of this blog entry outlines some of the key places I’ve located while working with the Power BI Preview in O365.

All links below need “YourDomainName” replaced with your valid name for them to work properly.

Admin Centers and O365 Site Settings Links

The Power BI preview uses the E3 (Enterprise 3) plan, which has nearly all features.


Since I have the E3 business plan, I find the following Admin & Settings links to be relevant for my purposes (i.e., testing out the Power BI Preview):

Power BI Admin Center
SharePoint Admin Center
Site Settings
Site Contents
Office 365 Admin Center
Office 365 Settings

(Note that I omitted the Exchange and Lync Admin pages because they aren’t relevant for what I’m doing with the Power BI Preview.  They are of course very important for an Enterprise implementation of Office 365.)

Power BI Admin Center


SharePoint Admin Center


Site Settings


Site Contents


O365 Admin Center


Office 365 Settings


Accessing Sites, Document Libraries, and Documents

The previous section covered accessing various pages for setting up the system.  Now let’s focus on where we will store our content.

Use the “Sites” menu option located in the top toolbar of some higher level pages.


All Sites

The launch page displays sites you have permission to see. 


Power BI Site Home Page

I created a new site called “PowerBISite” because for me it’s just for learning and doing a bit of playing around.  In reality you’ll want to think of it like any other site and give it a good name that suits its purpose.


Power BI Documents

Documents within a Power BI-enabled site are displayed with a preview of the report which looks like this:


Within the site, the contextual menus have additional choices.


Tracking Usage Analytics and Queries

Within the Power BI-enabled site, there is a menu option called “Manage Data.”


Manage Data


Additional Resources for Getting Started with the Power BI Preview

Office 365 – Getting Started with Office 365

Office 365 – Community (Blog, Forums, Wiki, Troubleshooting) 

TechNet – Power BI Forums

Office – Power BI Sites on Power BI for Office 365

Office – Power BI Admin Center Help



Schedule for SQL Saturday 237 in Charlotte

imageThe schedule is posted for SQL Saturday 237 in Charlotte to be held on 10/19/2013 (the day after the PASS Summit concludes).  This BI Edition will have a primary focus on Business Intelligence topics, with some DBA topics as well.  There will be 10 tracks in 5 time slots for a total of 50 sessions (we actually added a track while finalizing the session schedule because we wanted to be able to select more speakers, have more content, and make sure we have enough classroom capacity to hold > 300 attendees).

The topics on the schedule break down into these categories:


What?  You caught that there’s only 48 sessions listed in the chart when I said at the beginning there would be 50?  Got me there.  That just means we might have a surprise or two coming later. 

The levels break down as follows:


The types of speakers breaks down as follows:


All session details can be found here: – Please keep in mind this is the first draft of the schedule, so a few of the time slots are likely to move around a bit before it’s finalized.

If you are able to attend, please register soon!  We already have more than 200 registrations, which is really exciting this far out…so be sure to reserve your spot soon.  If you’re attending PASS Summit, we’d love to see you one more day!  And if you’re near Charlotte and not able to attend Summit, well then, all the more reason to come and join us at SQL Saturday 237!

Any questions?  Send them to SQLSaturday237 at SQLSaturday dot com.



The Role of Power Users in a Self-Service BI Initiative

From time to time I'll be contributing to the BlueGranite blog which has lots of great BI information published by my coworkers.

This week I published a blog entry titled "The Role of Power Users in a Self-Service BI Initiative" on the BlueGranite blog.  It provides a brief overview of Self-Service BI and discusses how business users possess varying needs, abilities, and preferences for working with self-service BI tools and techniques.  Please check it out!