GUID Fix When Copying and Pasting SSIS Packages

In a previous post I discussed how to create and use SSIS templates. One issue that can arise when doing this is that duplicated GUIDs will be assigned to each copied package. This creates identification issues if you utilize SSIS logging.

There are three simple solutions to solve this problem:

1. Manually select the generate new GUID option in the ID drop down box in your package properties.

2. Right click the package name and choose the option to reset all GUIDs within the project. This requires the BIDS Helper add on.

3. Rename your package and a new GUID will be automatically generated.


Pros and Cons of SSIS Templates

My previous post covered how to create and use a SQL Server Integration Services (SSIS) template. Now you might be wondering why would I use these templates and what are the dangers of using them?

The Rain (cons): SSIS templates need to be downloaded to every local machine that you will be developing on and, as addressed in my previous post, it must be stored in the assigned template folder that was chosen in your SSIS install.  I recommend saving the original template on a shared network drive to allow for easy access. Also, when you are using your template for a new package you must generate a new GUID, otherwise SQL Server will be unable to differentiate between your new package and other packages with this same GUID.

In my opinion the most dangerous aspect of using SSIS templates is the potential for losing consistency. It is inevitable that your template will require alterations before the completion of a project. In this event you would of course save the altered template on the share drive and instruct your team members to download the new template and update their local copies. Some team members may fail to do this (maybe they are on vacation, or the timing is inconvenient) and continue developing on the old template. As the project manager your perception is that all packages are consistent, but as the project comes together some missing pieces may come to light.

This scenario results in inefficiency due to extra work and package validation – and of course frustration from all angles.

The Rainbow (Pros): Standardization is extremely important in IT projects because (as we all know) the project will continue to grow – Instead of “scope creep” should we say “scope sprint”? Consistency of design helps keep the project extensible and easier to maintain. In addition, if a key team member gets hit by a bus, standardized designs will be easier to document and communicate when other team members need to be trained.

Other than standardization, SSIS templates are highly efficient. Once the initial template is created your developers will be able to start with a package that already has standardized logging mechanisms, control flow tools, and indirect package configurations. As a developer of SSIS packages I’m sure it is clear how much time would be saved if you didn’t have to set these components up with each new package you create.

The Pot of Gold (overcoming the cons): You have the option of taking a precautionary step using a Team Foundation Server (TFS). Your lead developer can utilize TFS source code control and set team standards for downloading updated templates. This product offers the capability to track old versions of the template while allowing a developer to “check out” the package for editing and saving.

This additional step in utilizing templates has the potential to save you and your team from a lot of headaches. Keep in mind that using the TFS product will not solve the problem is team standards are not set and adhered to.

Thanks for reading; I hope this has been insightful. Have a great day!

How to Create and Use SSIS Templates

SQL Server Integration Services templates can be a great time saver when creating multiple ETL packages for your business intelligence solution. In this video I will demonstrate how to create and use these templates.

First you will start a new project. Open SQL Server Business Development Studio, navigate to File > New > Project.

A New Project window will open. Navigate to Business Intelligence Projects > Integration Services Project, name your project and select OK. The name of your project should correspond to your intended use of the template.

Next you will build your template by adding the items from the toolbox that you wish to include. These should be items that will be needed for each use of the template.

You will  now need to save your project in two different ways. First click File > Save Selected Items. Next click File > Save Copy of “project name” As. A Save Copy of Package dialog box will open. Select File System in the Package location box and navigate to the Package Path field. You will want to save your package in this location: (assuming that you have installed SSIS with default settings) C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\Template Tutorial.dtsx

Now you will be able to access and use your SSIS template. Open a new SSIS project and navigate to the solution explorer pane. Right click on the project and select Add > New Item. This will pop up a box that allows you to select your newly created SSIS template and add it to your package.

There are many great benefits to using SSIS templates as well as some dangers. I will explore each of these areas in my next post. I hope this has been helpful, have a great day!





Exactly what is a Slowly Changing Dimension (SCD)?

Just the sound of the phrase “slowly changing dimension“ can sound quite intimidating when uttered in a technical meeting, but you might be shocked at how simple this concept really is. In fact, it has nothing to do with shifts or alterations in the universe at all. J

If you are involved in data warehousing you should have a firm grasp of SCDs as they will be necessary to structure your data in a meaningful way that takes advantage of the capabilities of a data warehouse; specifically when you wish to store historical data rather than simply overwriting a record. So, let’s break it down:

Slowly Changing Dimensions: Dimensions in your DW that include attributes that will gradually change over a period of time.

There are three types:

Type I – Overwrite old values: The old record is completely over written by the new record. Type I is used when you have no desire to store the historical attributes of the record.

Type II – Create an additional record: The old record will be kept and a new record will be inserted. Type II is used when you wish to store the historical attributes of the record.

Type III – Create new fields: This type creates new fields in the record to maintain the history and the current values of the specific attributes in the record.

Below are examples of how to use each type. In this example the price of a product is changing.

Type I: The old values are over written

Product price in 2011

Product price in 2012

Type II: A new record is created.

Product price in 2011

Product price in 2012

As you can see in the above example there are a few problems with the structure of the table. When using a type II SCD you will need to add a couple of basic fields to effectively track the historical records. There are several possibilities, but the idea is to be able to differentiate between the two records. Also you may notice that there are two identical IDs for this record. That is OK. This is still the same product so a common identifier is necessary. We will be able to narrow down our selection criteria by choosing the active record. A more realistic type II record would look like the below.

Product price in 2012

Type III: New fields are inserted

Product price in 2011

Product price in 2012

There are many scenarios in business where each of these SCD types will be useful. The most common are types I and II because type III can only store a limited amount of historical data.

I hope that this explanation has helped to simplify your understanding of how to use slowly changing dimensions in data warehousing.