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.

Getting Dynamic in SSIS Queries

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.

(DT_STR, 50,1252)

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()))
+ "\\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. Below is a screen shot of this expression in SSIS and the value you get from clicking Evaluate Expression.

clip_image002

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.

clip_image004

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.

clip_image006

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.

Published Monday, March 22, 2010 10:00 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

 

da-zero said:

First of all, I think it is worth mentioning that this method only works if the length of the query is below 4000 characters. If it is longer than 4000 characters, you could create a temporary stored procedure in an Execute SQL Task that acceps QueryDate as an input parameter. At the end of the package, drop the stored procedure. (although this is more for update statemets).

I use the same method as the one you describe above, but with a slight variation. I write my query as:

REPLACE("

select * from dbo.Employee where active_date > '<QueryDate,,>'

",

"<QueryDate,,>",  (DT_WSTR,20) @QueryDate)

This has less string concatenation (which is good for me, I type errors all the time :)). The placeholder <QueryDate,,> has this form so that you can copy paste the query in SSMS and use the "Specify Values for Template Parameters" option.

March 24, 2010 7:10 AM
 

Razia said:

Thanks a lot. Solved my one of the problems.

August 15, 2010 1:42 AM

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