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 www.consortioservices.com.

Featured

  • RAMBleed Side-Channel Attack Method Disclosed by Researchers

    Academic researchers this week published information about another side-channel attack method, called "RAMBleed," that can expose information from memory chips, including encryption key information.

  • Penguin

    Windows 10 Preview Build 18917 Shows Off New Linux Integration

    Microsoft's latest Windows 10 "fast-ring" preview release is showcasing a coming Delivery Optimization enhancement, along with the ability to try the newly emerged Windows Subsystem for Linux version 2.

  • Customizing Microsoft Office 365

    While the overall look and feel of Office 365 is pretty standard across organizations, there are several ways to personalize it and make it fit better with your company's specific needs.

  • Microsoft 365 Business Tenants Getting Conditional Access and Trouble-Ticket Features

    Microsoft added its conditional access security service to Microsoft 365 Business subscriptions, according to a Wednesday announcement, and it also added new trouble-ticket features for Microsoft 365 administrators.

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.