posted 6/5/2012 by RCharCox - Views: [1210]
In the 2012 version of Integration Services, there is a new task called the “Expression Task.” Now, I could go into detail about what this task does, and tout its functionality, screaming its praises from the mountain-top, however I’ll just quote what MSDN has on this new task: “The Expression Task creates and evaluates expressions that set variable values at runtime, using the Expression Builder.” That’s pretty much it.
So, this would be a great opportunity to talk about expressions! That being said, my SQL Sensei, Devin Knight, has just put out a blog detailing some of the new expressions in 2012, so I will instead go over some expression basics.
Expressions, as we all know, are a great way to make your SSIS packages dynamic and easily deployable to almost any environment. Also, they can be used to append dates to file names, edit connection managers, and customize your outputs to almost no end. There are numerous expression terms, and they can be combined in the Expression Builder to define user variables, different transforms (like the Conditional Split and the Derived Column transforms), precedence constraints, and containers.
For the purpose of our discussion today, I will focus on the different structures of expressions. Obviously, this will differ depending on the purpose of your expression, but there will be some similarities. For example, to create a filename with the date at the end, you would start with a literal string:
“C:\FileName”
Then define your expression, in our case as a string:
“C:\FileName”+(DT_WSTR,30)
And then set the value to input and append a file extension:
“C:\FileName”+(DT_WSTR,30)(DT_DBDate)(GetDate())+”.txt”
When evaluated, this should produce the following: C:\FileName2012-06-05.txt This same method can be used to Replace blanks with NULL values (TRIM(ColumnName)==””?(DT_STR,4,1252) NULL (DT_STR,4,1252) : ColumnName), change the data in a column to uppercase (UPPER(ColumnName)), and more. Pragmatic Works has a cheat sheet of some of these features as well, but, like anything, practice makes perfect. Good luck!