When you start working with SQL Server and SSIS, it isn’t 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, for example. This can be done using a combination of expressions and variables. I will do this in two parts, first I will build a variable that will always contain the 1st day of the previous month and then I will dynamically append this date to query and use it in my source.
So let’s add a variable to a package called QueryDate to hold our 1st day of last month variable. First, set the EvaluateAsExpression property to True. Next we will edit the expression to build our date. We are going to do this by using some date functions to rip a date apart and then concatenate them 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 as shown below.
DATEPART("mm",DATEADD("mm", -1, getdate()))
This starts with DATEADD to subtract a month from today and 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, as shown.
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 the code as shown below to both date pieces we have already built.
Okay, the hard part is over because we want the 1st 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, as shown.
(DT_STR, 50,1252)DATEPART("mm",DATEADD("mm", -1, getdate()))
+ (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. Below is a screen shot of this expression in SSIS and the value you get from clicking Evaluate Expression.
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] + "'"
As you can see, we have the T-SQL syntax with the QueryDate variable concatenated in the middle. Once again, here is a screenshot of the expression built with the expression evaluated on the bottom.
There is one last step to use the query you built. Open your source in the Date Flow and 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, as shown below. If you don’t see your query, make sure both variables have the EvaluateAsExpression property set to True.
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.