Slowly Changing Dimension Type 1 with Upsert Destination from Pragmatic Works’ Task Factory

Over my last 3 blogs I’ve shown you a variety of ways to handle Type 1 changes in a Slowly Changing Dimension.  All of those design patterns can be created with components that come standard with SSIS.  This time I’m going to show you a 3rd party tool from Pragmatic Works, that replaces every component that we’ve used before except the Source Component, with just one component.

SELECT
    [UserAK]
    ,[ScreenName]
    ,[AccountCreateDate]
    ,[TimeZone]
    ,[Language]
    ,[GeographySK]
    ,CHECKSUM( 
        [ScreenName]
        ,[AccountCreateDate]
        ,[TimeZone]
        ,[Language]
        ,[GeographySK] 
    ) as Hash_CD
FROM
    DimUserUpdate

Now instead of adding the lookup and conditional split or the SCD component all we need to do is add the Upsert Destination and configure it and we are done.

The first thing we have to do is create a new connection manager.  The Upsert Destination requires an ADO.net Connection to the database instead of the standard OLEDB connection. Once you’ve set the connection and selected the Destination Table, you must identify the key column for the destination by placing a check in the box next to the correct column.  Once that is done, go to the Update Method Tab.

On this tab we can set several different ways to handle the update.  The first is the Bulk Update Method. This method is the fastest, but if you plan on doing any kind of row count logging you will not be able to track updated rows. The next option is the Column Compare Method.  This method will compare the values in the columns from the source with that of the columns in the destination and if a change exists it will update the column.  This method is slower but allows you to log row counts for both updates and inserts.  The next option is the Timestamp compare which will compare two timestamp values to see if a record has been updated.  Similarly the Last Update Compare uses a Last update column value.  For this package we are going to use the Column Compare method. Simply select the columns you want to use for the comparison using the check boxes down the left hand side.  Now you can click OK and be done, or move on to the Advanced Tab.  I’m going to set up a Row Count for updates so I’m going to the Advanced Tab next.

Simply map each row count variable to the correct drop down and then click OK.  I’m going to add a message box using a Script Task to the Control Flow just to show that the update row count works properly.

You can add this little snippet to get the row count to show in a message box:

Public Sub Main()
       '
       MsgBox(Dts.Variables("UpdateRowCount").Value)

       '
       Dts.TaskResult = ScriptResults.Success
   End Sub

Now I’m just going to update the Update table so we actually get some updates going through.

UPDATE DimUserUpdate
 SET
 GeographySK = GeographySK + 1

 WHERE [UserAK] in ('98939115','97454425','97202071','9567382','87627850')

Now Execute the package

As you can see although 2,056 rows came through the source query, only 5 records were actually updated.  The Upsert Destination makes designing packages for handling SCD type 1 changes much faster and in my opinion easier.  If you get a chance I would suggest trying it out, as well as the multitude of other components that Task Factory has to offer.

Document your SSIS Packages with BI Documenter Part2: Reading the Documentation

In my previous post we walked thorough the process of creating SSIS documentation using BI Documenter.  In this post we will reap the rewards of our work on from the last post and review the documentation(not much of a reward, I know, but its better than a sharp stick in the eye).

When we last saw our documentation we had created an .CHM file as our document type.  Remember that BI Documenter can create two types of documents, .CHM or an HTML document.  Inside the .CHM file you can see on the left side the navigation pane. This pane should look familiar to anyone who has ever opened a Help file before.  The tabs include a Contents tab that lets you navigate through the documentation in a logical hierarchical manner, the index tab and a search tab.

To the right is the information or details pane which gives you the details of which ever object you have navigated to on the left.

Lets take a closer look at the contents of our package documentation

The top level of the contents is the name of the snapshot that we took.  What BI Documenter does in this section is give you the information pertaining to the snapshot as you can see below.

This is helpful because you can get an understanding for how new or out of date this documentation is.  As we will discuss in a later blog, this information will also come in handy when we do a snapshot comparison, another great feature of BI Documenter.

If we navigate down to the name of our package on the left we can see summary of our SSIS Package.

The first thing you will notice on this page is the work flow diagram.  This diagram shows you the following things

• Tasks
• Variables
• Connections
• Precedence Constraints
• Expression on Variables (Red circle on variable icon)
• Expression on Tasks (Red circle on task icon)
• Expression on Connection (Red circle on connection icon)

In our diagram you can see the variables (with the red circle expression indicator on it), the connection managers as well as and image of the control flow.  After the work flow is the properties.  The properties are too numerous to mention here but some of the settings that are documented, Creation Date, Creator Name, Enable Configurations, and Protection Level.

After the properties comes the Executables, which is a list or the tasks inside the control flow.  The nice thing here is that there are hyperlinks that will take you directly to the section of the document for each executable.  The same thing is true for the next section, Connection Managers.  Next is log providers which will be tracked if you have set them up, or the section will be blank if you have not. Then comes the user defined variable list, which will only be populated if you have created any.  As you can see below it will display the name of the variable, the value and any expressions on the variable.  Finally the system variables are listed in the last section.

That gives us a pretty good high level over view of what is going on in our package.  Lets drill in a little deeper in to the documentation and take a look at one of the executables, in this case our dataflow.

Again the first thing you will see is a screenshot of the components inside the data flow.  I really appreciate this inside the documentation because I am a very visual person.  Just by looking at the data flow diagram I have a good sense of what is going on inside the package.

Just like in the package properties, the data flow properties are just to numerous to mention here.  However if you open up your package in BIDS, and inside the dataflow click on the properties tab you will have a pretty good idea of what this section of you document now looks like.

After properties comes the components list.  This will list the names, with hyperlinks to their section in the document, of all of the components inside the data flow

Next in line is the column mappings.  The column mappings allows you to see how each column is mapped or even unmapped through every component inside the dataflow

Then finally a list of any variables, and their properties if you have used them inside of the data flow.

The documentation also includes section on the connection managers, user variables and any configurations you have set on the package.  I know that creating this kind of detailed technical documentation manually would be nearly impossible, or at least so impractical from a time stand point as to not make much difference.  With BI Documenter this document literally took less than 5 minutes to make.  In my opinion you just can’t beat that kind of time saving. But please don’t take my word for it, download a copy of BI Documenter and give it a try.

In my next blog we’ll cover the snapshot comparison feature in BI Documenter.

Document your SSIS Packages with BI Documenter Part1: Creating the Document.

Have you ever inherited an ETL environment and tied to make heads or tails of what was going on?  The previous developer didn’t leave any documentation. You have deadlines to meet and you need to know which packages do what. Does this sound familiar?  The worst is when you do this to yourself, a few years ago you created a package at someone’s request and now they’ve come back to you asking about that package and you can’t remember anything about it.  Proper technical documentation is so important in today’s world.  It can mean a few hours updating some packages for a downstream application when done right, or several days of searching and deciphering or maybe starting at square one in the worst case when documentation is lacking.  I have come across these problems working with clients on occasion and having access to a documentation tool has been a life saver.

BI Documenter is a great tool for documenting your SQL Server environment.  This does more than just document SQL Server Databases and Server Instance, though it does this very well for SQLServer 2000, 2005, 2008 and R2.  It will also document SSIS Packages (SQL Server Integration Services 2005, 2008), document SSRS Reports (SQL Server Reporting Services 2005, 2008), as well as document SSAS Cubes and Databases (SQL Server Analysis Services 2005, 2008).  Since I have been doing a lot of SSIS work lately I thought I’d show you how BI Documenter can help you document existing SSIS Packages.

BI Documenter has a very streamlined and easy to use interface. Launch BI Documenter from the start menu. When it has launched click on add new solution.  A solution here is very similar to a solution inside the BIDS environment.  The solution can contain documentation for any and all parts of the SQL Server stack you want to document.

Give the solution a name, and a description if you like and Save.

As you can see in the screenshot below I now have two solutions to choose from.  To select the solution you want to work with you can either double click on the solution or make sure the solution you want to select is highlighted and then click next.

On the next screen you can add what object types you want to document.   For this we will just choose an SSIS package, though we could choose multiple packages, a SQL Server Database and SSRS report all in one solution.

Clicking on Add Integration Services Package will bring up a new window to specify the location of the package you want to add.

Here you can specify the storage of the package, there are six choices, three each for SQL Server 2005 and 2008.  For each one you can specify File System, SQL Server or Package Store.  You can then set the authentication user name and password if you need to, and then specify the package path. Since we are using the file system in this example we will specify the containing folder and click next.

While there is only one package in this folder at the moment, BI Documenter does allow you to multi select packages here. Also please note that you can add descriptions as well as supply the package password if you have set the appropriate protection level on the package.  Once you have the packages that you wish to document selected click finish and this will bring you back to the solution page.

As you can see now there is now a package underneath the Integration Services Packages folder.

If you want to add any additional objects to the solution you could do that here, or if you’re done click Next.    On the next screen we will create the snapshot.  There are three tabs on this screen.  The first is the General tab. Here you can name the snapshot and give it a description.

The next tab it the filter tab.  Here you can select which object inside the solution you want to document.

As you can see you can choose to document individual components inside the  package, not just the package itself.  The third tab has more advanced configuration options that will be left at the default settings for now.  Lets click next and take the snapshot.  From this snapshot we will be able to create our documentation.

Choose the name of you snapshot from the dropdown, and if there are multiple objects in the snapshot you can select them as well.  When your snapshot is selected click next.  On the next screen you will finalize your documentation.  There are two choices for the document type.  It can be either a Microsoft Help File (.CHM) or an HTML file.  Choose the .CHM file here and specify a file location for the documentation.

The tabs across the top allow for further customization to our documentation.  The Template tab allows you to customize things like font color and size as well as add a custom logo to the document if you’d like.  The Diagram tab will allow you to create work flow diagrams to the documentation.  The Files tab allows you to add other documents and images to the document.  Of the next four tabs we only care about the Integration Objects.

When you’ve selected the options for the document that you want click next and once the document has been created click finish.  Your newly created documentation for the SSIS Package should now be open in front of you. 

To cover the extent of documentation that BI Documenter produces would make this blog post unbearably long, and I’m pretty sure that if I don’t want to write that much in one sitting, you probably won’t want to read that much.  Over the next few days I’ll post additional parts to this series where we can take a look at each section of the documentation that you have just created.  In the mean time please download BI Documenter and give it a try yourself.