If you’re even slightly familiar with SSIS you know that one of the incredible strengths of SSIS is the ability to make packages dynamic in countless different ways. One of the ways SSIS accomplishes this is through the use of SSIS expressions and the expression language.
BI xPress has a really useful feature called Expression Manager. The Expression Manager is a very cool feature that allows an SSIS developer to create, share, and manage custom SSIS expressions.
Creating your own SSIS expressions is very easy. Simply open BI xPress and click the Expression Manager button in the top right of the window.
Or if you’re already developing a package in BIDS, go to your variables window and click the Expression List icon.
Now that the Expression Manager is open, we can browse the canned SSIS expression or create our own. Let’s create our own custom SSIS expression for us to use later on or share with our development team.
First, click the Create New Expression icon.
Then give your expression a name and a description. My expression is going to append the user’s name and the date to create a file name. Here you can see the expression.
And in case you want to copy it for use:
"C:\\DataOut\\Archive\\" + RIGHT(@[System::UserName],FINDSTRING(REVERSE(@[System::UserName]), "\\", 1)-1)
+ (DT_WSTR,4)YEAR(GETDATE()) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".txt"
In my expression you can see that I leverage the UserName system variable to get the name of the user executing the package. I use FindString and Reverse to eliminate the domain and \ from the UserName, as well. Once you’re done, click Save and then click OK to close the Expression Manager.
Now that our expression is saved, we can use our expression in our SSIS packages. To utilize our expression in the package all we need to do is the use the BI xPress Expression Editor. We can clearly see our new expression in the My Expressions folder.
Besides allowing a user to manage their own custom SSIS expression, the Expression Manager features a host of canned, commonly used SSIS expressions. This drastically reduces the learning curve of the expression language. The Expression Manager comes with expressions that can create a file path and name with the date appended, create a dynamic SQL statement, or calculate the beginning of the previous month, just to name a few.
As you can see, the Expression Manager is a very powerful tool that allows you to fully realize the power of the SSIS expression language. To download a free trial version of BI xPress, check out PragmaticWorks.com.
Data and Analytics with Dustin Ryan 