Ohio Business Intelligence Road Trip 2013

imageNext month Mike Davis and myself will be making a epic road trip.  We will be flying into Columbus, OH to deliver a two day (7/16 – 7/17) workshop on advanced Reporting Services techniques.  We will then pack up and drive to Cleveland, OH for another two day (7/18-7/19) workshop on advance Integration Services techniques. 

Come join us for what should be a fun week!  I’ve already talked to a few folks that will be making the drive with us and attending both sessions.  Hope to see you there!

Columbus Master SSRS Workshop

Duration: 2 Days

Schedule: 7/16/2013 – 7/17/2013 8:30 AM – 5:00 PM

Price: $299

Location:

Microsoft Office
Polaris Center
800 Lyra Dr.
Suite 400
Columbus, OH 43240

Master Report Developers must hold unique traits to set themselves and their work apart from others. This two day class is designed to take you to the next level in your understanding of SQL Server Reporting Services (SSRS). We use a variety of lab exercises to ensure lecture sessions are backed up by hands on experience. With the hands-on labs, you will learn how to create complex reports that use Analysis Services as a data source and custom code assemblies. This course will cover all the advanced topics of Reporting Services like scaled-out deployment, .Net integration and monitoring Reporting Services usage.

Agenda

Cleveland Master SSIS Workshop

Duration: 2 Days

Schedule: 7/18/2013 – 7/19/2013 8:30 AM – 5:00 PM

Price: $299

Location:

Microsoft Office
Park Center III
6050 Oak Tree Blvd. S., Third Floor
Independence, OH 44131

During this 2 day advanced SSIS training course, you will learn how to use SSIS in your enterprise to solve common extract, transform and load (ETL) challenges. This class assumes you know the basics of SSIS and takes you from the novice level to a more effective, advanced ETL developer. Much of this class focuses on implementing common real-world SSIS patterns taught from the experts of SSIS. You’ll also learn how to performance tune SSIS. Be prepared for a very in-depth day of SSIS that gets advanced in many areas.

Agenda

New Book: Knight’s SSIS 2012 24-Hour Trainer

I’m very excited to announce that you can now purchase my latest release book: Knight’s Microsoft SQL Server 2012 Integration Services 24-Hour Trainer. The 24-Hour series of books offered by Wrox is a great way to get kick started into learning a new tool. Not only are the lessons very focused, making it easier to learn something new, but you also receive a DVD that accompanies the book. The DVD walks you through each of the lessons with the author showing you an example of solving a problem using the tool. You can get your copy of my new SSIS book here!

Using Excel Macro (.xlsm) Enabled Files in SSIS

Recently while working for a client that was running SQL Server 2008 R2 I was tasked with loading an Excel Macro (.xlsm) enabled workbook.  If you have ever tried this yourself you may have tried using the default Excel provider, which does not work. In fact, this is the error message you get after trying to close the Excel connection manager.

image

I thought I’d go ahead and document the solution in both 2008 and 2012 for you.  Both solutions require you have the Microsoft Access Database Engine 2010 Redistributable driver installed so make that your first step before reading on.  Don’t worry it doesn’t require a server restart.

SSIS 2008

Create an OLE DB Connection Manager and use the Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider (Microsoft.ACE.OLEDB.12.0).  Then provide the path for your macro enable workbook in the “Server or file name property”.

image

Next click on All to modify the Extended Properties by adding Excel 12.0 Macro;HDR=YES.  Most of this text is self explanatory except the HDR which stands for header.  If you don’t want the first row of data to be the column header than change this to NO.

image

Click OK and use an OLE DB Source in your data flow to either select a sheet or query the workbook.

SSIS 2012

Guess what?  With SSIS 2012 you don’t have to do anything extra!  The default Excel Connection that didn’t work in 2008 does work now.  As long as you have the before mentioned Access driver you’re ready to go.  Shown below is the 2012 Source Assistant that you’ll noticed took care of the Extended Properties setting you had to manually do in 2008.  In 2012 you will use the normal Excel Source instead of the OLE DB Source.

image

Hope this helps!

New SSIS 2012 Expressions

SSIS 2012 has added a few new expression functions and I thought I’d spend a short time detailing each.

LEFT

The LEFT function believe it or not is a new function.  In past versions of SSIS you had a RIGHT function available but no LEFT.  Well SSIS expression haters you now have what you want.  This function is described as:

Returns the left part of a character expression with the specified number of characters.

Template:

LEFT( «character_expression», «number» )

Example:

LEFT( [ProductName],3)

Result:

Nik

TOKEN

The new TOKEN expression is an interesting one.  It returns a string after a specified Token delimiter.  You can pass in multiple delimiters for you expression to parse and also specify the occurrence number you would like to return.  That means if you set the occurrence to 3 it would return the third instance of the token.  SSIS describes this function as:

Returns the specified occurrence of a token in a string. A token may be marked by a delimiter in a specified set of delimiters. The function returns an empty string if the occurrence is not found. The string parameter must evaluate to a character expression, and the occurrence parameter must evaluate to an integer.

Template:

TOKEN( «character_expression», «delimiter_expression», «occurrence» )

Example:

TOKEN("new expressions can be fun"," ",2)

Result:

expressions

TOKENCOUNT

TOKENCOUNT would likely be used in combination with the previously discussed TOKEN function.  The TOKENCOUNT function returns back the number of times a Token delimiter appears in a string value.  This would likely be plugged into the TOKEN expressions for the number of occurrences when trying to find the last occurrence.  SSIS describes this function as:

Returns the number of tokens in a string. A token may be marked by a delimiter in a specified set of delimiters. The string parameter must evaluate to a character expression.

Template:

TOKENCOUNT( «character_expression», «delimiter_expression» )

Example:

TOKENCOUNT("new expressions can be fun"," ")

Result:

5

REPLACENULL

Again this function answers the SSIS haters who don’t like the fact that there is an ISNULL function in the expression language but it doesn’t work like the T-SQL ISNULL. Currently if you wanted to accomplish the T-SQL ISNULL you would have do write an expression like this:

ISNULL(OrderDateSK) ? 19000101 : OrderDateSK

This uses the ISNULL function that returns back True or False if the field is NULL and also uses a conditional operator to determine how to react when it is NULL.  The REPLACENULL function will work much more like the T-SQL ISNULL function.  This function is described as:

Returns the value of the second expression parameter if the first expression parameter is null.

Template:

REPLACENULL( «expression», «expression» )

Example:

REPLACENULL(  [OrderDateSK] , 19000101 )

Result:

19000101 (if OrderDateSK is evaluated as NULL)

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.

Follow

Get every new post delivered to your Inbox.

Join 37 other followers