Working with SSIS Expressions
Expressions can work with you or against you. Here's an example that makes perfect sense.
- By Eric Johnson
- 10/26/2009
In SQL Server Integration Services (SSIS) Packages, expressions are everywhere. You may have worked with expressions in some of the transformation tasks, such as the Derive Column Transformation, but did you know expressions can also be used to set the properties of many of the tasks and transformations or even modify the values in your SSIS Variables?
Let's look at an example. What if you have to write a lookup query that pulls data from two databases on the same SQL Server but you're not sure what the databases will be named? Your query will run in the context of your lookup transformation, so the connection manager tied to your lookup will handle the first database. But, you need to get a bit tricky for the second database. Take this query as an example:
SELECT ContactID, EmailAddress
FROM Person.Employee
JOIN GlobalContacts.dbo.Email
ON Employee.GlobalID = Email.GlobalID
WHERE Email.EmailAddress IS NOT NULL
AND Email.EmailPromotion <> 0
The Person.Employee table will be found in the database defined by your Connection Manager. The dbo.Email table needs to be in the GlobalContacts database in order to be found; but in our case, the GlobalContacts database is often named differently. For example, it might be GlobalContactsQA and GlobalContactsVer2. So how do we dynamically build our query to handle the fluidity of this database names?
Here is one possible solution: Add a couple variables to our package, RefDatabase and LookupQuery, as this figure:
Here, we've set the RefDatabase variable to "GlobalContactsQA". This variable can be passed in from a parent package or set with an SSIS configuration. For LookupQuery, set the EvaluateAsExpression property of the LookupQuery variable to True:
Next, add this code to the Expression property:
"SELECT ContactID, EmailAddress
FROM Person.Employee
JOIN " + @[User::RefDatabase] + ".dbo.Email
ON Employee.GlobalID = Email.GlobalID
WHERE Email.EmailAddress IS NOT NULL
AND Email.EmailPromotion <> 0"
Be sure to include all the quotes shown in the example. This expression will use the value of the RefDatabase variable to dynamically modify the SQL query. Last but not least, you will need to change your lookup to use the SQL Command From a Variable option and point it to the LookupQuery variable. That's all there is to it.
The biggest mistake I have seen made -- and I have done this myself -- is setting the Expression property of the variable and forgetting to set the EvaluateAsExpression property to true. This will result in your variable having an empty value or your variable retaining the last value you hard coded.
Remember, expressions are everywhere in SSIS and can be used to save you a lot of time. Just make sure to do a little research before you use them, as there is often a way to accomplish your objective without an expression. Too many expressions can make a package difficult to maintain.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.