Develop Packages Faster using BIxPress Package Templates: Creating a Template

The current project I am involved with has required me to develop packages as quickly as possible while also adhering design standards. The packages had a number of things in common with each other, including data sources, variables and components used to load the packages.  This is the perfect scenario for creating a template to speed up development of SSIS packages.  To create a template you can right click on the SSIS packages folder and select the BIxPress Package Builder Wizard

 

From the wizard specify the New Template from Package option. Give the template a meaningful name for example DimensionLoadTemplate.  Note that the templates are saved as .flow file.  Select the folder you want the template to be saved in.  A great tip I’ve picked up from the other consultants here at Pragmatic Works is to select a shared folder on the network to store any user created templates so that way any developer can have access to the templates.

On the next screen select the package you want to create the template from and then click next.

The next screen allows you to select which options for every  object in the package you want to set up as configurable by default. The list of configurable objects is on the left

Once you select an object from the object list the configurable properties pane is populated with all of the properties for that object.  Place a check mark next to the properties you want to be able to configure when using the template to create a new package. For the OLEDB Source Component I am choosing to make the Name of the component and the SQL Command default configurable.

Repeat this for every object you want to configure.  Once you are done with this selection process click next and then start.  Just like that you have now created a Template that you can use over and over again to speed up your SSIS development time significantly.  Try BIxPress yourself and tell me what you think.

Document your SSIS Packages with BI Documenter Part5: Scheduling your Documentation

The last entry in this introductory series to BI Documenter will cover how you can automate the documentation process.  The automation works by taking the existing solutions that have been created and in conjunction with Windows Scheduled Tasks sets up the documentation to be run on a schedule that you define. So lets get to it.

First select the solution you want to schedule.

Next select the Schedule snapshot link from the tools menu on the right hand side of the screen.

This will launch a new window where we will build the command line to schedule the snapshot. There are several options as well as an explanation on how to add the command line to the  Window Scheduled tasks.

1. Here you can again select the solution you would like to automate.
2. Set the server name, username (if needed), password(if needed)
3. The name of the metabase for the solution (the default is BIDocumenter)
4. Select the output file type (CHM, or HTML)
5. Set the Output file path
6. Here you have the option to append a date time stamp to the end of the snapshot.
7. You can choose to enable logging which will create a log for the creation of the snapshot and output to a file.
8. For the executable you need to supply the file location for the BI Documenter executable file. The default location is supplied for you, but if you changed any of the defaults when installing the program you may have to navigate to that folder on your own.
9. The Arguments section is the fully constructed command line that you will then use to schedule your job in the Windows Scheduled Tasks. You can also test the command line here which will run a complete snapshot of you solution from the command line.
10. This section walks you through using the Windows Scheduled Tasks to schedule your BI Documenter Snapshots.

When I ran through this wizard myself I was able to leave the defaults for just about everything.  The only thing I changed was the timestamp for my output file.

Now lets walk through adding this to Windows Scheduled Tasks.

I’m using Windows7 so to find the Task Scheduler I had to go to the control panel and under administrative tools I found the Task Scheduler. Once there select create Basic Task.

This will open a new window where you will give the task a name and a description(optional)

After clicking next you are presented with a list of options for when to start the task.

Click next and set the frequency and day of the week(if you choose weekly) to run the task.

Next specify what the task is to do. Select Start a Program

On the next screen is where the BI Documenter wizard we ran through earlier will be a huge help.

In box 1 enter the location of the executable for BI Documenter, and if you remember the wizard helped us find that.

In box 2 we add the arguments, which the wizard build for us after we selected all of the options.

Click next and Finish.  Now navigate to the task inside the Task Scheduler Library, right click and select run. Below you can see the newly created document.

This might seem a little bit too daunting if you are new to using the Task Scheduler, but trust me if I can do this then just about anyone can.

I hope you found this series useful and you’ve had a chance to download and test drive BI Documenter.  Please feel free to leave any comments and let me know what you think of BI Documenter.

Document your SSIS Packages with BI Documenter Part4: Object Lineage and Impact Analysis

Sorry for the long layoff since my last post in this series but the holiday season and client commitments demanded a lot of me.  I am back now and ready to continue to share with you all.

In some of the more complex SSIS packages it can be a challenge to identify what objects in your environment will either impact your SSIS package of be impacted by your SSIS package.  This is doubly true if you didn’t design the package your self.  BI Documenter has a feature called Impact Analysis/Object Lineage that helps you get a better sense of these objects. 

Lets continue with the package we’ve been using throughout these tutorials.  Select the solution from the list.

Then click on the link to launch the Impact Analysis/Object Lineage wizard.

Choose the Packages Tab and then right click on the package you want to investigate. You’ll have two options at this point.  You can render it as a diagram or render it as a list.

If you select the first option the diagram will look something like this.

The diagram shows that the package accessing two different tables.  On top of that is clearly shows you which tables are providing the data for the package and which table is consuming the data.

The list gives a slightly different view of the tables but still just as useful.

It lists them by objects used and objects using the package. Once you have select the version that suits you, you can choose to save the image or print it.  For my money I would choose to save it as part of the solution and have it available in the complete document.  It can also be saved as a separate file if you choose.

I suggest you test out BI Documenter for you self and test this and any of the other features I’ve talked about in my last few blogs.

SSIS Error Trying to insert Null Values

I was running into an issue today that was driving me a bit crazy.  I was trying to load a table that had about 35 columns, some set as NULL, others as NOT NULL.  On all of the NOT NULL columns there were default values set for them. So when I mapped all of the columns from the source to the destination, there were a few of the NOT NULL Columns that didn’t have a source, but since they had default values I wasn’t worried about them at all.  Now this was on a client machine, so I can’t show you exactly what happened, but here is an example I came up with.

 

It’s a simple name table from a flat file source.

I went and added the Sex column to the destination table, set it to not allow nulls and made the default value ‘F’.  In the source there is no column for Sex.  Now at this point I believed that I had left all other values at their default setting, and that was my mistake.  But before we get to that lets see what was happening to me and see if you can figure it out.

[OLEDB_DST Name List [55]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot insert the value NULL into column 'Sex', table 'Demo.dbo.NameList'; column does not allow nulls. INSERT fails.".

This made no sense to me at all.  I knew the column wasn’t allowing nulls, but I wasn’t mapping anything directly to that column and it had a default value set, so why were nulls still trying to ruin my day.  To the forums I went, and I found this topic http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/4554075c-5f68-4e59-9f7c-8d46c05e8ec6  and it a little reading but a reply by Hunchback hit the nail on the head.  Somehow, for reasons I can’t remember I checked the box next to keep nulls and that was causing all of my issues.

 

 

As soon as I unchecked that box, my problem was solved and the table loaded just fine.  I hope this helps somebody out there.  At least now I know I won’t take as long to correct that mistake the next time I make it.  I can’t promise my self I won’t do it again, because I am notorious, at least in my own mind, for accidentally checking boxes I didn’t mean to check.

Document your SSIS Packages with BI Documenter Part3: Comparing Snapshots

In the last two posts we?ve discussed how to use BI Documenter to document your SSIS packages and reviewed the documents that is generated.  Another one of the very useful features of BI Documenter is the Snapshot Comparison. Let us suppose that you have scheduled your snapshots to run on a regular basis, and now you want to compare the different snapshots to view the changes that have been made to your document.  Doing this with BI Documenter is quick and easy.

First thing is to make sure you have 2 snapshots of the same object to compare.  I covered creating snapshots in the first blog of this series check it out here.  For this first comparison I actually didn?t change anything inside the package because I wanted to see what it looked like without any change.  To compare the snapshots launch BI Documenter and choose the appropriate solution.  From inside the solution click on the hyperlink "Compare Snapshots?

 

 

The new screen will pop up and you will tell the wizard which snapshots you want to compare and simply click the start button and your comparison will begin.

 

 

BI Documenter makes the comparison very easy to read.  As you can see above the color coding on the compared objects makes it easy to quickly identify which objects are New, Modified, have been deleted etc. . . Even though I didn?t change anything inside the package I still received a Modified indicator between the two snapshots on my StartTIme Variable.  Lets take a closer look at that.

 

The first thing item indicated as modified is the variable objects, and if we drill down into the variables we see that only one variable has been modified, the strStartTime variable.  We can drill down into the variable and see that the Variable value has been changed.  Lets take a look at why the variable value has been modified.  I didn?t change the expression at all, but the expression uses a system variable that captures the start date of the package, and since that is evaluated when the snapshot was taken the actual value was different for the two snapshots.

Now lets see what happens when I intentionally modify the package.  Lets delete one of the connection managers.

Just like that the new snapshot comparison makes it easy to differentiate the objects that have been changed or deleted from the ones that have gone untouched.  Go ahead and test out this feature by downloading the software here.  Tell me what you think of the software.  Be sure to check out my next blog entry when I review the impact analysis/object lineage features of BI Documenter.

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.

Quickly Develop Packages with BIxPress Snippets Wizard

 

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.

 

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

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.

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

and selected the language you want to use for the code

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.

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.

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

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.

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.

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

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

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