SSIS Project Connection Managers

With the new Project Deployment Model in SSIS developers are gaining the benefit of a new design time only feature called Project Connection Managers.  If you’ve developed in SSIS previously Connection Managers require no further detailing, but for those new this is how SSIS connections to data that’s used as a source or destination.  This new feature is visible in the Solution Explorer of SSDT (SQL Server Data Tools). 

Below you will see the traditional method (Package Deployment Model) for developing package on the left, which has a similar option called Data Sources.  Data Sources were purely a design time feature available in SSIS 2005 and 2008 to help manage connections to multiple packages.  Basically you could change a Data Source created in the Solution Explorer and the next time you opened the package that used the connection it would update the metadata.  Sounds nice but the key there is the update doesn’t occur until the next time you open it.  So if you have hundreds of packages you would have to open all of them. 

The real solution to that problem is to use configurations but I’d like to focus on the differences between the old Data Source method of doing things and the new Project Connection Manager method.

Package Deployment Model Project Deployment Model
image image

The image on the right is of the new Project Deployment Model and you quickly notice the Data Sources folder is not available.  Instead you have Connection Managers which are similar in purpose but I think accomplish the job much better than Data Sources did.  They’ve actually been made less complex if that’s possible because all you have to do as a developer is create a Project Connection Manager and then it’s automatically created in every one of your SSIS packages.  As shown below you are able to clearly tell the difference between a Project Connection Manager and a regular Connection Manager that is only scoped to a single package.

image

If you have a regular Connection Manager that you would like to make a Project Connection Manager then you simply right-click on it and select to Convert it.

image

Another nice feature of Project Connection Managers is if you make a change to one the update applies to all the packages that use it without you having to open them each.

While this is a small new feature I thought it was pretty nice and worth spending some time discussing.

Converting to the SSIS 2012 Project Deployment Model

The new project deployment model in SSIS 2012 is the new standard for how packages are created, configured and deployed.  Any new packages that are created in SSIS will default to using the new deployment model. 

The most basic way of describing this change is now instead of deploying packages we now deploy projects.  Projects get deployed to the new Integration Services Catalog, which I’ll cover in a future post.  Also, you get the benefit of project parameters. which allow you to pass values very easy across multiple packages in a project.  Jamie Thomson has a great series on this topic as well I encourage you to read what he’s written here.

After upgrading to a project deployment model you also lose some capabilities you previously had.  For example, configurations that were a staple for how to configure you SSIS packages across multiple environments in previous versions of SSIS are no longer available.  The previously mentioned project parameters now replace configurations along with Environments, which live on the Integration Serves Catalog. 

Obviously these are a lot of new concepts and totally changes the way you think about deploying and configuring SSIS so I plan on explaining each of these in separate posts.  In a previous post I walked you through upgrading your SSIS packages to SSIS 2012.  As I described in that post, the upgrade wizard does not change the deployment model for you.  So after completing a package upgrade you will be running the legacy package deployment model as labeled below.  This means everything developed in the original packages is still available including configurations.  You can still run you packages using the package deployment model but I would recommend upgrading for the benefits detailed earlier and just like anything else new at Microsoft the old way of programming will likely be deprecated over time.

image

Once you are ready to convert from the package deployment model to the project deployment model right-click on the project name and select Convert to Project Deployment Model as shown below.

image

As soon as you select that you may get prompted with a warning telling you that Data Sources you have in the Solution Explorer will be removed with the project deployment model.  These are no longer supported with the project deployment model but are could be replaced with share connection managers.  Unfortunately, it does not automatically replace your data sources with shared connection managers.  Click OK on the warning.

image

The start of the wizard details the 7 step conversion process, which includes converting configurations to project parameters and changing execute package tasks.  Click Next when ready.

image

The first step is to select the packages you want to convert and provide and passwords that you may have used to encrypt them.  After making your selection click Next and the wizard will load the packages for making the conversion.

image

You will then be asked to assign a name to the project if different from the original name.  New in the new deployment model you can also assign a protection level to the project.  Once you’re done here click Next.

image

The wizard will also upgrade any Execute Package Tasks that it detects.  The task had a bit of an overhaul and now when using the project deployment model you now reference packages that are part of the project instead of a file or server that contains the package.  The old way of running packages is still available but it’s now referred to as an external reference as opposed to the new project reference.  The defaults here are generally acceptable but you should review them then click Next.

image

Next you will be asked to select configurations to be converted.  Remember configurations will be replaced with project parameters.  If a configuration is not found for some reason (maybe the file connection couldn’t be made) you can also add it manually here.  When ready click Next.

image

To fully replace the configurations the wizard will create parameters that will store the same information the the configuration did.  Ideally if these configured values are the same across multiple packages you would choose to scope these as project parameter instead of package parameters, which is the default.  Parameters scoped at the package level are only available within that package.  Leaving theses all as project parameters will result in only 2 parameters created even though it appears from my screenshot that I will have 10 parameters because several of these are duplicated.  Click Next.

image

The parameters  that you just created can now be configured to have new values if you choose.  In my case I have connection strings in my value and I should verify that the provider used is appropriate for the version of SQL Server these packages will run on.  Click Next.

image

You can review the steps that are about to occur if you would like and click Convert when ready.

image

Once the conversion is complete you should see a screen similar to below.  You will notice it also prompt you to save as soon as you close the wizard.

image

A couple things to note about the conversion.  You Solution Explorer will appear different.  Notice Data Sources are no longer here, but you do have a Connection managers folder.  You’ll find Connection Managers to be a lot more useful and I will detail those in a later blog.  You will also see a section called Project.params.  These are your project parameters and can be defined by double-clicking on the object in the Solution Explorer.

image

The parameters are basically variables that can be defined across the entire project.  Again are what the wizard used to replace any configurations you used previously.  Below you will see what the definition of these project parameter store and then to assign them to configure an object in the package you will use configurations.  Luckily the wizard automatically did that for any configurations used previously.

image

So when you look at any connections or other objects that used configurations you will now find an expression that references the project parameter.  Notice below in SSIS 2012 it’s much easier to find connections that have expressions on them because they’re labeled with a little fx icon next to them.

image

When looking at the expressions on these connection you will see it does indeed reference the project parameter.

image

The last thing to point out that the wizard did for us is a change to Execute Package Tasks that I had.  The change is simple enough but it’s a new concept to 2012.  Basically instead of referring to a package that lives in the files system or SQL Server you can now reference package within the project.  This is called a Project Reference and the change is done automatically for you using the wizard.

image

I hope you found this post helpful as you walk through through converting your SSIS packages to use the new project deployment wizard.  In my next post I’ll walk you through deploying these package to the Integration Services Catalog.

Upgrading Packages to SSIS 2012

Many of you may now or in the future have the need to upgrade your SSIS packages to the new SQL Server 2012 SSIS.  Luckily, upgrading from SSIS 2005 or 2008 isn’t nearly as painful as you may have experienced upgrading old DTS (SQL Server 2000) packages.  If you’re considering the upgrade to 2012 here are some of the new features you have to look forward to:

  • Usability fixes in the development environment (Resolve References, Undo, Zoom, etc…)
  • New/Changed Tasks and Transforms (CDC Control Task, Execute Package Tasks, DQS Cleansing, etc..)
  • Project Deployment Model and Integration Catalog
  • New native Logging and Execution Reports

There is far more than I could list here but you can do a search for “What’s new in SSIS 2012” just as easily as I can Smile.

The upgrade process from 2005 or 2008 to SSIS 2012 is actually very straight forward.  There are a few things to note that are not upgraded automatically through the native upgrade steps.  The following are not upgraded and may need extra attention:

  • Deployment model stays as a Package Deployment unless you do another conversion tool.
  • Configurations are not upgrades.  For example, a configuration file may reference an old driver that is not longer used in 2012 so you would have to manually upgrade these.
  • Execute Package changes are not upgraded

To get started open your old SSIS solution in SQL Server Data Tools (new name for BIDS), which will automatically launch a conversion wizard.  This conversion wizard actually does two conversions.  The first conversion is more of a Visual Studio upgrade and the second specific to SSIS.  The screenshot below shows the start of the VS upgrade, click Next past the welcome screen.

image

The Visual Studio conversion will ask if you would like to back up the original files.  I generally go ahead and create a backup just incase, then click Next.

image

Prior to starting the SSIS Conversion tool you’ll get a warning to ensure the SSIS project is coming from a trusted source so you don’t risk a security threat.  Keep in mind the Visual Studio wizard stays open in the background.

image

Click Next past the welcome screen of the SSIS upgrade wizard.

image

Select the packages you wish to upgrade and ensure to provide any package passwords then click Next.  These passwords came from the ProtectionLevel setting of the original packages.

image

There are few settings you can manipulate prior the the upgrade that are pretty straight forward.  Shown below are the default settings and I will mention that even though connection string provider names are upgraded it will not upgrade them in configurations.  So if a configuration overrides a connection it will still have the old provider.  Click Next.

image

Review the actions that will be taken by the wizard then click Finish.

image

A successful conversion should look similar to the below image.  Hit Close, which will end the SSIS upgrade wizard and return you back to the final step of the Visual Studio upgrade.

image

You are returned to the Visual Studio upgrade where you simply need to hit Close to complete the upgrade.

image

Your packages will now be upgrade and should run without problem.  As mentioned before you may still want to upgrade things like the package deployment model, configurations, and execute package tasks.

SQL Rally Wrap Up

Another SQL Rally has wrapped up in Dallas, TX and it was great event.  My favorite part about these larger events is being able to meet new people and see old friends that I haven’t seen for months. 

Unfortunately, I did not attend any of the precons this year but I did see a lot of great sessions.  I made it a point to at this event to reach outside my comfort zone with the types of sessions I attended.

Here’s a wrap up of my couple days in Dallas:

Thursday

I started my morning by attending Amy Lewis’ (@Amy_LewisAZ)  session called Got a Data Mess the Size of Texas? Data Cleansing Using the “Scrubbing Bubbles” of SSIS.  This session what not a stretch from my BI comfort zone, but with an ongoing bet with Amy about I SQL Saturday events that were both held on the same day I had to make sure Amy held up on her end of the bet.  She did a great job on her session and got very detailed about the different methods for doing data cleansing in SSIS including Fuzzy logic.  The session was made very fun by Amy because it was all baseball themed.

Next I watched Mike Fal (@Mike_Fal) speak on Eating the Elephant: SQL Server Table Partitioning.  I had never seen Mike speak and I found out quickly why, he’s fairly new to speaking.  That was surprising to me because he was an excellent speaker and new his stuff.  This was a session I was really interested in seeing and helped be supplement my Business Intelligence skills with something I don’t do everyday.  Mike had a great way of explaining the details without assuming you know the basics.

During lunch I attended the Women in Technology panel discussion, which was really interesting to hear.  The panel discussed issues women faced but could just as easily be relevant to anyone searching for a job or negotiating wage and benefits. 

At the end of the day I presented a session called Data Mining (It’s not the size of your data – it’s what you do with it) with Adam Jorgensen (AJBigData).  The session went great and you can download the slides at the hyperlink above.  We cover the basic concepts of data mining to building a solution in Excel.

I wrapped up my day with some karaoke (no I did not sing) with much of the SQL Rally crowd.

Friday

I started Friday by watching Jen Stirrup (@jenstirrup) present on Business Intelligence in Azure: SSIS & SSRS Focus.  As Azure becomes more and more dominate I really needed to know this stuff well!  Jen did an excellent job making the concepts easy to understand with a crowd that for the most part had never seen SQL Azure before. 

After Jen’s session I spent a lot of time vendor hoping to see what each offered before lunch.

I ended my day with my presentation called Adapting Your ETL Solution to use SSIS 2012.  This session focused on taking an existing SSIS project and upgrading it and changing it to using the new project deployment model.

With an earlier flight I headed home, but will miss my SQL friends until next time!

Choosing the Right Microsoft Reporting Technology Part 6: Making the Decision

We’ve completed reviewing the major Microsoft tools and now you have to make “The Decision”.  Which tool(s) is the best fit for the reporting needs?  Remember just picking one tool and using it for all reports may not be the best decision because you are missing out on a lot of the benefits of other tools. 

One tool I like to use that helps me make a decision like this is a decision matrix.  With a decision matrix you provide the possible options, which for us has been the following tools:

You also provide the factors that are important to you in making a decision.  You can see in the screenshot below I’m considering tools based on:

  • Ad Hoc Reporting
  • Content Sharing
  • User Flexibility
  • Developer Flexibility
  • Time to Develop

image

Within the matrix you rate each of the tools (0-100) in their capabilities of the factors you’re considering.  This rating should only be done once. 

Some factors to making a reporting decision may be more important than others depending on the need.  For example, for one report Ad Hoc Reporting may be the most important thing while another report Content Sharing may be the most important thing.  That’s why you sign a relative weight (0-10) to each decision you need to make.  That way if Ad Hoc Reporting is most important than rate it higher than the other factors.  Again, this weighting system would be changed for each report you need to make a decision on choosing a tool.

Download a sample Decision Matrix here:

http://devinknightsql.files.wordpress.com/2012/04/decision-matrix.xlsx

Using a tool like a Decision Matrix can help you make decisions quickly and with more confidence.  I hope this series has been helpful.  If you need to catch up on previous posts follow the links below.

Follow

Get every new post delivered to your Inbox.

Join 29 other followers