THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Working with SSIS Expressions

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 they can also be used to set the properties of many of the tasks and transformations, or even used to 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 the following 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 GlobalContactsQA and GlobalContactsVer2. So how do we dynamically build our query to handle the fluidity of this database name? Here is one possible solution: We are going to add a couple variables to our package, RefDatabase and LookupQuery.

clip_image002

As you can see, we have 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.

clip_image004

Next, add the following 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 and too many expressions can make a package difficult to maintain.

Published Monday, October 12, 2009 11:47 AM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement