Working with SSIS Expressions

Expressions can work with you or against you. Here's an example that makes perfect sense.

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:

Adding variables

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:

Set EvaluateAsExpression property

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


  • Microsoft Nabs IoT Platform Provider Express Logic

    As part of its plan to invest $5 billion in IoT technologies, Microsoft this week acquired Express Logic, which provides real-time operating systems for industrial embedded and IoT devices.

  • Dealing with Broken Dependencies in SCVMM

    Brien shows you how to resolve some broken, template-related dependencies in Microsoft's System Center Virtual Machine Manager.

  • AzCopy Preview Adds AWS S3 Data Transfer Improvements

    Microsoft announced this week that it has improved the preview version of its AzCopy tool to better handle Amazon Web Services (AWS) S3 data.

  • Microsoft Adding Google G Suite Migration in Exchange Admin Center

    Microsoft's Exchange Admin Center will be getting the ability to move Google G Suite calendar, contacts and e-mail data over to the Office 365 service "in the coming weeks."

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.