Getting Dynamic in SSIS Queries

When you start working with SQL Server and SSIS, it won't be long before you find yourself wishing you could change bits of SQL queries dynamically.

• By Eric Johnson
• 03/01/2010

When you start working with SQL Server and SSIS, it won't be long before you find yourself wishing you could change bits of SQL queries dynamically. Most commonly, I see people that want to change the date portion of a query so that you can limit your query to the last 30 days. Examples like this one can be done using a combination of expressions and variables. I will do this in two parts here, first by building a variable that will always contain the first day of the previous month and then dynamically appending this date to query and using it in my source.

So let's add a variable to a package called QueryDate to hold our first day of last month variable. First, set the EvaluateAsExpression property to True. Next, edit the expression to build our date. We are going to do this by using some date functions to rip apart a date and then concatenate it back together to get the 1st day. First we need to figure out what last month was and return just the month's number. We do that by using DATEPART and DATEADD:

DATEPART("mm",DATEADD("mm", -1, getdate()))

This starts with DATEADD to subtract a month from today, then DATEPART to extract the month number. So if this runs in March, this portion of our expression returns 2. We also need to do a similar thing to get the year for last month:

DATEPART("yyyy",DATEADD("mm", -1, getdate()))

In DATEADD we still only subtract one month, but now in DATEPART we grab the year. Both of these pieces of code return an integer value, but we need to build a string that can be converted back into a date. So we need to add a cast statement to each of these snippets of code to make them string compatible. This is pretty simple; just prepend this code to both date pieces we have already built:

(DT_STR, 50,1252)

Okay, the hard part is over because we want the first of last month; so that part we will hard code. Now all we need to do is concatenate some pieces together to get our final date:

(DT_STR, 50,1252)DATEPART("mm",DATEADD("mm", -1, getdate()))
+ "\\1\\"
+ (DT_STR, 50,1252)DATEPART("yyyy",DATEADD("mm", -1, getdate()))

The backslash is a string literal character in SSIS expressions telling it that the next character has some meaning, such as "\n" which means new line. That is why we have to use "\\" in our code to tell the expression we really want a single backslash in our output. Fig. 1 shows the expression in SSIS and the value you get from clicking Evaluate Expression.

Figure 1. Our SQL expression for extracting the date, using the Expression Builder. (Click image to view larger version.)

I ran this in March so the date returned was what I expected. Now all we have to do is put this into a T-SQL query. Just create a second variable (I called mine Query) and build a dynamic string to concatenate your new date variable into your T-SQL code. Here is an example that does just that:

"select * from dbo.Employee where active_date > '"
+ @[User::QueryDate] + "'"

Here, we have the T-SQL syntax with the QueryDate variable concatenated in the middle (see Fig. 2).

Figure 2. Creating the second variable via T-SQL syntax. (Click image to view larger version.)

There is one last step to use the query you built. Open your source in the Date Flow, set the Data Access Mode to SQL Command From Variable, and then select your query variable in the dropdown. If you did everything correctly, you should see your select statement in the Variable Value window (see Fig. 3). If you don't see your query, make sure both variables have the EvaluateAsExpression property set to True.

Figure 3. The select statement should appear in the Variable Value window if it's written correctly. (Click image to view larger version.)

Just a couple quick notes: This only works with sources that allow you to write a query to retrieve data, SQL Server Oracle, Access, and even Excel. This will not work with sources like text files that don't allow you to query their contents.

If you want to do something similar with text files, you could BCP the data to a SQL Server staging table first, and use that as your source -- you just need to be creative. Also, this whole process could be done in one variable; I just like separating the two for maintainability. This allows me to focus on getting the date correct in one variable and getting the query correct in the other.

The sky is the limit in how you build dynamic queries, so use your imagination.