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

Its 10 PM, Do You Know Where Your Configurations And Expressions In SSIS Are? BIxPress Does And It Will Tell You Too.

With some of the more complex SSIS packages that we develop the number of expressions we use as well as the number of package configurations can become quite large.  That can make remembering which component has an expression and what that expression is rather difficult, and the same goes for configurations too.  BIxPress has a couple of features that can really help you manage this quite easily.

The Expression Highlighter in BIxPress helps you visually identify which components have expressions and which connections have configurations. Here is how it works.  In this package the Data Flow Task currently has no expressions associated with it.

Now I’ll quickly add an expression on the description of the data flow task

Once that is complete and you hit OK the appearance of the Data Flow task has now changed slightly. There is now a little red circle on the Data Flow Task, which is how BIxPress identifies components with expressions associated with them.

The process is pretty much the same for identifying package configurations.  Here are the before and after views of a connection manager with and without the package configuration.

As you can see the package configuration shows up as a blue circle instead of a red.  Well what happens if you have an expression and a configuration on the same component?  Lets add a expression on the same connection manager we just added configurations to.

The connection manager now looks like this.

This will make identifying the components so much easier.  What if you don’t want to go through each component one at a time to see which expression each one has.  BIxPress has another feature that lets you see all of the expressions in a list, and not surprisingly its called the Expression List.  Here is how it works.

From the BIxPress menu select the Expression List and a new window will open up.

As you can see this lists all the components that have expressions.  It gives you each object type, object name, the expression property, and the expression.  You also have the option to edit the expression right from this window using BIxPress’ Expression Editor as well.  With BIxPress knowing where your expressions are and what they are doing is much easier.