In one of my many SSIS blog posts, I said that if you are using SSIS, sooner or later, you are going to create variables.
In this blog, I’ll say that, if you are using SSIS, sooner or later, you are going to be frustrated by the crazy syntax in Expressions.
If you are like me, who has been in the database world for awhile, SQL-like syntax becomes intuitive to us over the time, but the VB-like syntax used in the SSIS expressions can be very foreign to us. Logical AND needs to be &&, logical OR needs to be ||, concatenation needs to be &, and == is not the same as =.
OK, here is the official link to the Integration Services Expression Reference on MSDN.
So, what are Expressions? To me, Expressions in SSIS are SISS developers’ “programming” language. Developers who have mastered tools like C# can write programs that can meet any (almost) user requirements with the ease of conditionally and dynamically controlling logic flows and creating logic branches. Compared to those application programmers, we, database developers, often look very awkward, and inadequate sometimes, in terms of being in control.
Fortunately, in SSIS, we can make friends with Expressions. From the above link in MSDN:
Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.
Did you notice that it says expressions are complex frequently?
There are 4 topics on the MSDN reference site.
Describes expression evaluator syntax, the data types that the Data Transformation Pipeline uses, data type conversion, and expression elements.
Describes the operators that the expression evaluator provides.
Describes the functions that the expression evaluator provides.
Provides expressions that use multiple operators and functions.
The Functions section can be very familiar to many database developers. To be able to feel comfortable with using Expressions in SISS, everybody needs to read the Operators section and the Advanced section at lease once.
So, next time, when you cannot get your simple Precedence Constraint working as the way you wanted it to, check out the above Integration Services Expression Reference first.
Just a side note. This blog is inspired by my late night change to a SSIS package. I wanted to add a Precedence Constraint so that my package will not run if it’s a holiday or weekend.
Here is a “simple” expression ( simple only after I remembered AND needed to be &&):
@varHolidayInd == "N" && @varWeekendInd == "N"
I haven’t blogged for more than a week now. That’s a little unusual for me. It was not because I had too much to do at work, it’s because I took on too much responsibilities (trust me there is difference between these two statements). I say yes to almost everything that business users asked me, co-workers asked me, and my boss asked me.
Anyway, now I am back. I checked some stats on my blog, and see that I blogged the most in these two categories: ETL Best Practices and SSIS Best Practices. I guess there is no accident here. ETL and SSIS are the areas that I always felt strongly and wanted to articulate my ideas or understandings or mistakes.
Putting the ETL and SSIS blogs together, I have over 150 posts in these two categories. To make it easier for me to refer back to my own posts, and for my regular blog fans to find them, I’ve decided to include a sequential number for each blog post. So here it is, the number 95 in the SSIS category.
This blog post is about the infamous configuration file again in SSIS. I had privilege recently communicating with David about the trouble he had with his configuration file. David is a very experienced database professional and has done everything he knew about not hard-coding any user id, password, file path/name, or any other configurable parameters that might change in the future. David is a consultant, so this is a very important decision to make. When he is no longer with the team, other developers will know how to change those parameters without breaking the process.
Scenario (from David)
I am developing a SSIS package or three (on my local machine) for a client and I wanted it to be as dynamic as possible so when I leave they will only have to modify the configuration file and all will be well. So I set up checkpoints and logging and email and expressions on most everything. So I set up the expression on the checkpoint file and it evaluates fine; the expression is “@[User::RootLocation] + @[User::CheckPointsFile] + @[User::CheckPointExtension]”.
But when I try to save the package the error message below comes. (Note: All worked fine on David’s local machine. The error came up only when trying to deploy to the server.)
Here is the message when I try to save package:
TITLE: Microsoft Visual Studio
Nonfatal errors occurred while saving the package:
Error at DEBNetPlusEmailProcess: The file name is not valid. The file name is a device or contains invalid characters.
Error at DEBNetPlusEmailProcess: The result of the expression "@[User::RootLocation] + @[User::CheckPointsFile] + @[User::CheckPointExtension]" on property "CheckpointFileName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Solution (Also from David)
Ok…OK… I found the answer I was looking for and it came to me by way of a developer who was asking questions about the variables inside the package. His questions started me thinking and what I discovered was this:
When you deploy the package the configuration file must have the EXACT same fully qualified PATH as when you created it on the development environment.
C:\Folder1\Configuration\myConfigFile.Configdts (Development Environment)
C:\Folder1\Configuration\myConfigFile.Configdts (Production/Test Environment)
I had this earlier:
Y:\Folder1\Configuration\myConfigFile.Configdts (Development Environment) ON deployment
The server could not reach “Y” path so validation failed….
Now, ALL of this might have been intuitive to many but I can/could found NO reference to it anywhere.
Another more “dynamic” solution (this one is from me)
Congratulations to David! He has just solved another mystery about using configuration files in SSIS. But here is some additional conversation between me and David. The point of this conversation is that, we could use an Environment Variable to point to a configuration file, instead of hard-coding the name and path of the configuration file.
Your solution would work by using a fully qualified path, which must be the same in development as in production.
In a previous blog, I recommended that instead of using a fully qualified path for your configuration file (essentially you are hard-coding the configuration file path and name), you will use an environment variable to point to the configuration file on a particular machine.
Thanks for letting me know about your resolution.
Yes, I agree completely that a environment variable on the production machine would be the ideal place to store the path to the configuration file. I have read your blog post and will implement that in our move to prod on our next project.
Your time and help have been most gratifying and I hope to communicate with you in the near future.
I hope this is not a long and boring post to read. Have fun with configuration files!
I recently had a need to show three different view in a employee productivity report with the same data set, two summary views and one account level detail view.
For the two summary views, employees are listed regardless they have data or not in the date range. This will allow the report to do “forced ranking. I’ll blog more about “forced ranking” later. Because I had to show all the FTEs in different grouping, I had to create some dummy rows. I’ve written a post, SSRS – Drill down or Drill through 2? discussing the approach.
Now if I use the same data set to show the data in account level details, there is no need for forced ranking any more. Now I want to hide those dummy rows.
This is a simple task in SSRS. There is no need for a new data set. Create a new table in SSRS, using the same data set, with a filter.
The Expression for the table filter is this:
Note: IsNothing is VB’s equivalent to IsNull in T-SQL.
With the above filter, only rows that have a DECISION_DT will be shown in the account level detail view, and all the dummy rows will not.