Category Archives: SQL Server 2012

Learn to Design Tabular Models at SQL Saturday #298 Jacksonville, FL on 5/10/2014

I’m excited to announce that on Saturday May 10th in a couple Saturdays I’ll be speaking at SQL Saturday 298 in Jacksonville, FL! SQL Saturday in Jacksonville always has an awesome turnout and I love speaking at this event! There will be tons of great free training sessions from the likes of Devin Knight, Robert Cain, Andy Warren, Adam Jorgensen, Max Trinidad, and tons of other great experts! If you’re in the North Florida area on May 10, you need to get registered for this event!

The session I’ll be delivering is called What the Tabular??? and will start at 1:15 PM EST on Saturday, May 10th! If you’re interested in learning how to build a Tabular Model, how to follow Tabular Model design best practices, and how to decide if a Tabular Model is the right solution to your business problem, then my session What the Tabular??? is for you! It’s a great beginner sessions and will feature tons of demos and walkthroughs of the technology!

So if you’re nearby, definitely get signed-up for this awesome event! You won’t regret it!

Notes From SQL Saturday 168: Introduction To SSIS 2012

Thank you to everyone who attended my session, “SSIS 2012 In Yo’ Face: Introduction to SSIS”! I had a great time presenting and it was a whole lot of fun. Also, a big thank you to my co-presenter, Mitchell Pearson (blog | twitter)!

Also, thanks to everyone who attended my second bonus session, “What’s New In SSIS 2012”.

For those of you interested, here is the package we created in “SSIS 2012 In Yo’ Face”. Simple place the content of the zip file in the C:\Projects\SQLSaturday168 folder and unzip the SSIS project to the directory of your choice. If you have any questions, please feel free to post them here or send me a message on Twitter (@SQLDusty).

Again, thanks to everyone who came to my sessions! I hope you enjoyed the sessions and maybe even learned a little something.

I’m Speaking at SQL Saturday #168: BI Edition!

imageSQL Saturday #168: BI Edition is coming up on November 17th in Tampa, Florida and yours truly will be presenting! This SQL Saturday event will be all Business Intelligence focused! All the sessions will either be ETL, analytics, or reporting related with a miscellaneous track focusing on professional development and DBA topics. Get registered ASAP for this awesome BI focused SQL Saturday!

I’ll be co-presenting with Mitchell Pearson (blog | twitter) on an introduction to SSIS 2012 called SSIS 2012 In Yo’ Face: Introduction to SSIS. We’re going to cover commonly used SSIS tasks and transforms, the SSIS expression language, and how to create powerfully dynamic packages.

So head over to the SQL Saturday #168 site and get registered. I look forward to seeing everyone in our session! It’s going to be a blast!

Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!

With the release of SQL Server 2012 comes a whole host of improvements to Integration Services that makes development and administration of your SSIS packages much easier. And it also looks new and shiny, so you can’t beat that.

After you’ve upgraded to SQL Server 2012, you’ll have to start converting your SSIS packages to 2012, which isn’t all that difficult. There are, however, some fundamental changes to the way packages are built, configured, and managed that are important to know. In this article, we’re going to walk through upgrading an SSIS 2008 project to 2012.

First, lets open SQL Server Data Tools.

0 open SQL Server Data Tools

Then we’ll need to open our existing SQL Server 2008 Integration Services project.

1 open project

Once the navigate to the project and open it, the Visual Studio Conversion Wizard will open. This will walk you through converting the 2008 IS Project to 2012.

2 VS conversion wizard

After clicking Next, you’ll be asked if you want to create a Backup. I opted to create the Backup in case something went horribly wrong. Hopefully you’re packages are in source control so you have something to fall back to, but I didn’t convert the original packages anyways. I created a copy of the project and then converted the copy in order to preserve the original project.

3 backup packages

Click Next a couple times and surprisingly, or not, another wizard will open. The SSIS Package Upgrade Wizard is where we’ll select which packages we want to upgrade to 2012.

4 ssis package upgrade wizard

Place a check mark next to the packages you wish to upgrade. In my case, I only have one package in this project, but if I have multiple packages I would see those packages displayed in the window below.

Also, this is where you will specify a password if the packages are encrypted with one.

5 select packages give pw

On the next screen of the wizard, there are a couple of important things to point out here.

6 select ssis package mngmt options

The first option, “Update connection strings to use new provider names”, allows the upgrade wizard to update any connection strings to use the SQL Server 2012 Native Client Library. This will upgrade any connections stored in your package. What this won’t upgrade are package configurations or any expressions that may be altering a connection manager. Those will need to updated manually.

Also, the last option, “Ignore configurations”, basically allows you to tell the wizard to not warn you about potential problems with package configurations on connection managers. I left this option unchecked because I want to know which packages have configurations on the connections that could potentially cause problems.

In the Upgrade Report, you’ll notice the first informational message indicates that the provider was upgraded to SQLNCLI11. Also, the upgrade wizard detected a package configuration attempting to configure the connection. We either need to upgrade the package configuration to use the new provider name or remove the configuration.

7 view upgrade report

If I open the package in Data Tools, you’ll see the following error due to the lack of support for the old provider.

8 package config provider error

So at this point we have two options. The first option is to update the package configuration and continue using our package configuration with our 2012 packages. Or we can take advantage of a brand new feature in SSIS 2012: Parameters! With your package open in Data Tools, you’ll notice the Parameters tab. This is where you’ll manage your parameters that are scoped to the package. Parameters that are scoped to the project will appear in a seperate .params file that can be seen in the Solution Explorer. Parameters are designed to replace package configurations and they function like you’d imagine a parameter would. Parameters can be assigned values and the parameters can be shared between packages in the project.

9 create parameter for package config

Before we can start using parameters in our packages, we must first convert our entire SSIS project to use the Project Deployment Model. The Project Deployment Model is also new to SSIS. Basically, with the Project Deployment Model, an SSIS project is the unit of deployment for the project, whereas with the legacy deployment model, Package Deployment Model, a package was the unit of deployment. The Project Deployment Model is how we will be able to share parameters between packages. Read this article to learn more about the Project Deployment Model. If your SSIS project is currently set to the legacy Package Deployment Model, the project icon should look like this:

16 package deployment model icon

Once you’ve converted to the Project Deployment Model, the icon should look like this:

17 project deployment model icon

To upgrade your convert to the Project Deployment Model, right-click the project in the Solution Explorer and click Convert to Project Deployment Model.

10 convert project to project deployment model

This will open, you guess it, another wizard. You’ll first select the packages to include in the project. You’ll also need to specify a few project properties, like the name of the project and the encryption level. If you are upgrading any packages with Execute Package Tasks in them, you should upgrade those to use a project based reference.

The important part of the Project Conversion Wizard is converting the package configurations to Project Parameters. On the Select Configurations screen, check the package configurations that you wish to convert to parameters.

11 convert package configs to parameters

Next, you’ll need to create the parameter that will replace the configuration. Any configurations properties in the configuration file(s) will be shown here. Specify the Parameter Name and the Scope.

The Scope can either be at the Project or Package level. If you create the Parameter at the Project level, the parameter will be viewable in the Project.params file, which you can find in the Solution Explorer. If the scope is the Package level, the parameter will be found in the Parameters tab of the package.

12 create parameter

On the Configure Parameters screen, you can alter the details of the parameter. If you click the ellipses button next to the parameter, you can view the parameter details. At the bottom of this window is a check box that says “Required”. A parameter that is Required does not have a default value and must be specified at run. If the parameter is not Required, the parameter does not need to be set for the package to execute.

13 set parameter details

Because I select the Project as the scope of my parameter, I now have a .params file that houses all my project level parameters.

14 project params

If I open this file, I can see the new parameter that was created based on my package configuration.

15 parameter

And if I look at the expression on my connection, I can see the reference to the parameter.

18 parameter referenced by expression

One other really cool thing about 2012 is that objects that have an expression on them now have a little icon indicating the expression on a property of the object. Its about time.

19 expression flag

I hope you found this helpful and that you now have a basic understanding of converting your legacy SSIS packages to 2012. Check out this white paper that has some great information on upgrading to 2012. And if you have any questions or comments, please post them!