Archive for November, 2011

h1

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

November 30, 2011

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. image

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

image

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.

image

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.

image

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)

image

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.

image

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.

image

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

image

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

image

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.

h1

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

November 28, 2011

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.

image

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

image

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.

image

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.

image

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

image

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.

image

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.

image

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.

image

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

image

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.

image

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.

image

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.

image

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.

h1

Quickly Develop Packages with BIxPress Snippets Wizard

November 14, 2011

 

BIxPress has a lot of features designed to help you develop SSIS packages faster, while still maintaining best practices, such as Package Templates, and Expression Manager.  Another feature that will help you quickly develop packages is the Snippets Wizard

One of the hardest things for me when it comes to creating packages is using the script component.  I am not extremely comfortable writing code.  I can usually decipher it, but creating it is a cold sweat inducing nightmare.  I can spend hours sifting through the wide world of blog posts and forum responses, trying every little bit of code until I find the on that will work for me.  The Snippets Wizard can eliminate most of that time consuming searching.  It has a large library of reusable code in its library to start, and you can add your own snippets to save for reuse later. 

Like any wizard should be it is very simple to use.  Lets create a new SSIS package and call it CheckFile.dtsx.  In this package we are going to check to see if the file exists and then use SSIS expressions and constraints to determine what happens if the file does or doesn’t exist.

image

To launch the Snippets Wizard simply right click on the design pane and select Add Snippet

image

This will bring up the BIxPress Snippets Library.  This library is pretty extensive, and as I mentioned you can add any code snippets of you own to the library for reuse as well.

image

Once you’ve found the snippet you want to use,

image

and selected the language you want to use for the code

image

click next.  On this screen you will see the parameters that will be needed for this snippet.  Some of the snippets might not have parameter, in which case you can just skip to the next screen.  Our check if file exists snippet does have parameters that we can edit and preview, so lets take a look.

image

The first parameter will be the file path of the file we are checking.  In this example we will use the file Text.text on the C:\ Drive.  The parameter name is FilePath,  Once you are done editing the variable click on the Create Variable button and then OK.  Do the same for the File Exists Flag, making sure the default is set to false.

image

Click next and then start.  The script task will now be entered into you SSIS package.

image

Now lets set up a test to see how well the script has worked. I’m going to add another couple of Script Tasks because they won’t cause the package to fail if I don’t configure them.  I’m going to name them File Exists and File Doesn’t Exist and connect them to the Check if File Exists script task.  The next step is to put an expression and constraint on each connection.  For the File Exists task we want the  process to continue only if the file exists.  Double click on the connection, change the Evaluation operation to Expression and Constraint, leave the value as Success and add this code to the Expression  @FileExistsFlag == True.  Test the expression to make sure you have the syntax right then click ok.

image

Do the same thing for the connection on the File Doesn’t Exist script task, except change the expression to @FileExistsFlag == False. With that done we are ready to test the package.  Here you can see that I don’t have a file by the name of Text.txt on my C:\ Drive.

image

So when we run the package the File Doesn’t Exist task should turn green.

image

So, lest just double check and create the file and run the package again.

image

image

Before I had access to BIxPress this would have taken me a pretty long time, and this isn’t even the most complex bit you will come across.  So for all of you SSIS developers that are Novice VB,et or C# developers at best, BIxPress can really save you a whole lot of time and frustration, and you can get back to doing things that really matter faster