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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: