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.

Variable Expressions With BIxPress

The expression language in SSIS is a powerful but sometimes frustrating tool.  At times it resembles T-SQL at other times some of your typical programming languages which can make implementing them in SSIS packages a daunting task.   There are plenty of samples out on the web spread across blogs and forums, however, BIxPress actually has a library full of some of the most commonly used SSIS expressions already built in. The Expression Manager organizes all your common SSIS expressions for use anywhere you can use SSIS expressions, such as variables and data flow pipelines.  I have found this most useful when trying to build variable expressions.  To launch the Expression Manager when working with variables select Expression Library Icon from the Variable Toolbar Extension

.

Now that the Expression Manager window is open you should see two tabs across the top.  In the My Expressions tab you add your own expressions to the library which you will then have access to without having to open up another document where you might have stored it, or another package where you might have used the expression before.

In the Standard Expressions Tab there are a ton of pre built expressions covering date expressions, string manipulation expressions and file/folder expressions among others. 

The Expression Manager has saved me a ton of time by not having to search and search for the right syntax.  If you get a chance to try out BIxPress you won’t regret it.

Remove last row from file with SSIS Script Component

The other day I had a unique request from a client.  I had been working on a package that would combine a number of smaller files into one larger file and then copy it to a new directory.  An easy enough task using SSIS and some staging tables to insure that the data was sorted in the correct order.  One of the issues that came up was how they were importing the file.  For some reason the last CR/LF at the end of the file, used to start a new line, was showing up as a bad character and causing the process to fail, so naturally they wanted me to remove it.  I didn’t have a clue at fist how I was going to accomplish this, but after asking around the office one of the guys said that it could be done by using a script component. 

Well now I at least had a starting off point.  So to Bing and Google I went looking for the most uncomplicated method I could find, since VB and C# are not exactly in my wheel house.  After searching several forums and asking my question in a multitude of ways I finally found what I was looking for. It’s a pretty short piece of script which deletes the last to characters of the end of a file if they match CR/LF.

' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task.

Imports System.IO

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

' ' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Sub Main()

Dim fileStream As FileStream

fileStream = File.Open("myfile.txt", FileMode.Open, FileAccess.ReadWrite)

If fileStream.Length > 1

Then fileStream.Seek(-2, SeekOrigin.End)

Dim bytes(1) As Byte

fileStream.Read(bytes, 0, 2)

If bytes(0) = 13 And bytes(1) = 10 Then

fileStream.SetLength(fileStream.Length - 2)

End If

End If

fileStream.Close()

End Sub

End Class

 

I added the script and changed the file name and it worked perfectly.  I hope this helps you guys avoid the time wasted searching for the same thing.

Change Variable Scope with BIxPress

One of the biggest annoyances I have when building packages is creating variables in the wrong scope of the package, for instance needing the variable at the package level but mistakenly creating it in the data flow task.  Its not a huge deal to recreate it, but it can be time consuming if you created several variables in the wrong scope.  BIxPress makes it possible to actually change the scope of a variable with a few quick clicks.

The highlighted variable is in the wrong scope, it should be in the package scope (LoopLoadArchive).  To change the scope using BIxPress simply click on the button to start the wizard.

Here you must choose the variable you want to move, if you want to copy or move the variable, and finally what scope you want it moved to.

Click on the green arrow pointing to the right and the variable will be moved.

This has been a huge time saver for me on the times where I have had my head down for hours creating SSIS packages only to realized that I now have a dozen variables set to the wrong scope.  This is just one of many of the hidden advantages of developing SSIS packages with BIxPress.

BIxPress Package Builder Templates: Loop Load Archive

When I was in the Navy working as a Airplane Mechanic (AM2 if anyone is interested) we were always told never to rely on our memories, but to take the repair manual with us.  Even if it was something we’d done a hundred times or more, the one time you forgot something could put the people who relied on us to fix things correctly in danger.  This mentality has stuck with me even as I’ve changed careers, and that is why BIxPressis such a big help to me even when developing the simple SSIS Packages.  Whether you are using the existing templates in BIxPress or creating your own, it eliminates the guess work and the time spent wracking your brain or scouring the forums and the blogs to find the correct SSIS construction.  In the last few weeks I have had to build a number of packages that loop over files in a folder, load them into a table, and then archive the files.  Not a terribly complex package, but between variables and containers and file system tasks I always tend to screw up one of them and have to spend some time troubleshooting the package to realize I forgot to set the variable as an expression or some other simple thing to overlook. 

BIxPress comes with a template already built in to handle such a process and some test files to load and archive as well.  Inside your project add a new package using the BIxPress Package Builder Wizard, either right clicking on the SSIS package folder or from the BIxPress tab on the menu bar.

From the options select the Create SSIS package from existing package template, also rename the package as well and click next.

On the next screen choose the appropriate template.

On the next screen choose the configurable properties (the defaults are fine for the test files) and click next

On the final screen you see a summary and then click start to begin building the package.

Once the wizard is complete there are just a few things left to configure.  The first thing we need to change is the Destination Transform.  I’m loading the files into a table on my local machine.

Next we need to configure the Flat File connection manager.

Click OK.  Now lets just verify the test files are where they need to be

Execute the package and once its complete the folder where the files were should now be empty of files.

and the archive folder should now have three files in it.

This is just one of the many package templates that come with BIxPress.  For me it’s a great way to insure that I don’t forget any of the necessary components of a typical package.