Setting the SQL Command Property using Expressions in SSIS 2005

Posted on March 12, 2010 by Luke Hayler

OLE DB Source Adapter

The OLE DB source connection has several Access Modes:

Openrowset
Openrowset From Variable
SQL Command
SQL Command From Variable

For now, I am going to focus on the SQL Command options. Using the SQL Command Access Mode requires you to set the SqlCommand property (typing in your SQL query directly), effectively ‘hard coding’ the sql statement into the package. Using the SQL Command From Variable option allows you to set the SqlCommand property using a variable and subsequently by using an expression.

Data Reader Source

The Data Reader Source connection has only one Access Mode, and that is SQL Command. Don’t ask me why. At this level you can only set the SqlCommand property by typing in your query, or indeed, copy & paste.

Using an Expression to set the SqlCommand Property

“So then,” you ask, “how might I set the SqlCommand property for each of these Data Sources when it seems that I can’t?”. Here’s how. Let’s first concentrate on the OLE DB Source:

Create a new Variable of type string and set your SQL statement
Open up the OLE DB Source and select the ‘SQL Command from Variable’ option
Now, select the the variable created in step 1 as the variable that contains the SQL statement

At this point your OLE DB source is defined by the query that you have set in the Variable. We now need to create the expression that will define the query at runtime. We do this by navigating to the variable’s properties, setting the EvaluateAsExpression property to True and then configuring the expression in the Expression property.

Moving on to the Data Reader Source. With this adapter we only have the option of setting the SqlCommand directly. Or so I thought until recently. If you switch to the Control Flow design surface and select the Data Flow task that contains the Data Reader Source adapter, you will be able to set the SqlCommand using an expression.

Select the Data Flow task to bring up its properties
Click the ellipsis (…) to bring up the Expression Editor
Select the [DataReader Source].[SqlCommand] Property
Set your expression.

Using variables & expressions to set your queries enables you to centralise your queries, use configuration files to set database connections and capture the queries you are running in logging operations.

The DatePart() Function in SSRS

Posted on November 16, 2009 by Luke Hayler

Using the DatePart() Function in SSRS can be tricky, even at the best of times. I have pulled my hair out too often trying to remember what syntax should be used, so have resorted to writing down all my findings. This is so that you don’t have to go through the same agony & frustration as I did, as well as for my own benefit (my memory is shot…)

What it does: Returns an Integer containing the specified part of a given date

Syntax: DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

There are two ways to use this function:

Using the DateInterval collection
Using a string expression

Using the DateInterval Collection

Let’s start with an example:

To return the current month: =DatePart(DateInterval.Month, Today())

Now, in the expression builder (in BIDS), it will look like there’s something the matter with what we have just written:

You can ignore this, the expression will work. An important point to note when using the DateInterval collection, is that you must use a date that is of type Datetime. If you use a string representation of a date (“2009/11/13”), then you’ll need to convert this to Datetime using CDate(“2009/11/13”) before the expression will work. Thus:

This will work: =DatePart(DateInterval.Month, CDate(“2009/11/13”))

This won’t: = DatePart(DateInterval.Month, “2009/11/13”)

When you use the DatePart function to identify the day of the week you can specify what the First Day Of The Week is (defaults to Sunday if this is not specified). You can do this by accessing the FirstDayOfWeek collection. Example – To Return the day of the week, specifying Monday as the first day of the week:

DatePart(DateInterval.Weekday, CDate(“2009/11/13”), FirstDayOfWeek.Monday) returns 5 (Friday)

All the possibilities for using the DatePart() function with the DateInterval Collection:

The following table shows all the possibilities for the DatePart Function using the DateInterval collection. All expressions use a Parameter called MyDate (type = Datetime), which has a value of “2009/11/13 12:34:23”

Expression	Result
DatePart(DateInterval.Second, Parameters!MyDate.Value)	23
DatePart(DateInterval.Minute, Parameters!MyDate.Value)	34
DatePart(DateInterval.Hour, Parameters!MyDate.Value)	12
DatePart(DateInterval.Weekday, Parameters!MyDate.Value, FirstDayOfWeek.Monday)	5
DatePart(DateInterval.Day, Parameters!MyDate.Value)	13
DatePart(DateInterval.DayOfYear, Parameters!MyDate.Value)	317
DatePart(DateInterval.WeekOfYear, Parameters!MyDate.Value)	46
DatePart(DateInterval.Month, Parameters!MyDate.Value)	11
DatePart(DateInterval.Quarter, Parameters!MyDate.Value)	4
DatePart(DateInterval.Year, Parameters!MyDate.Value)	2009

Using a String Expression

Again, let’s start with an example:

To return the current month: =DatePart(“m”, Today())

Using a String expression for your interval enables you to use a date that is of datatype String:

=DatePart(“m”, “2009/11/13”)

All the possibilities for using the DatePart() function with a String Expression:

As noted previously, using a string expression for the interval allows you to use a date value that is of the String datatype. You can of course use a Datetime value if you wish to.

This table shows all the possibilities for the DatePart function using a String Expression for the interval. All expressions use a Parameter called MyDate (type = String), which has a value of “2009/11/13 12:34:23”

Date Part	Expression	Result
Second	DatePart(“s”, Parameters!MyDate.Value)	23
Minute	DatePart(“n”, Parameters!MyDate.Value)	34
Hours	DatePart(“h”, Parameters!MyDate.Value)	12
Day of Week	DatePart(“w”, Parameters!MyDate.Value, FirstDayOfWeek.Monday)	5
Day of Month	DatePart(“d”, Parameters!MyDate.Value)	13
Day of Year	DatePart(“y”, Parameters!MyDate.Value)	317
Week of Year	DatePart(“ww”, Parameters!MyDate.Value)	46
Month	DatePart(“m”, Parameters!MyDate.Value)	11
Quarter	DatePart(“q”, Parameters!MyDate.Value)	4
Year	DatePart(“yyyy”, Parameters!MyDate.Value)	2009

Is there a preferred method?

Ul
timately, no. At the end of the day, all the results are the same. What is my preference? I use the DateInterval collection, mainly becuase I like that fact that you can’t get confused about which date part you are using (w/ww, y/yyyy, n….). It’s very clear to anyone who is reading the code that (for example) DateInterval.WeekOfYear refers to the week of the year and nothing else. It also forces you to use a Datetime Value, which safeguards against invalid dates (sort of).

I hope this clears things up a little!

The CHOOSE() Function in SSRS

Posted on July 29, 2009 by Luke Hayler

BOL’s description of the CHOOSE() Function: “Selects and returns a value from a list or arguments”. The syntax of the function is as follows:

=CHOOSE(index, expression_1 [, expression_2] [, expression_3] [, expression_4] …[, expression_n])

So, the Choose() function uses an Index (of type Double) to ‘choose’ one of a list of supplied values/expressions. For example;

=CHOOSE(Index, “Red”, “Yellow”, “Green”, “Grey”, “Black”, “White”)

When Index = 3, then the result is “Green”. When Index = 5, then the result is “Black”

Let’s look at a practical example:

I have a report (a simple chart at this stage) that relies on any one of a number of values to decide which metric to display – these values are provided to the user via a parameterized drop down list in the report, they simply choose which metric they want and the appropriate data is displayed. In the chart, I use an expression to decide on which metric is to be used based on the value returned by the parameter. Previously, I would have used an IIF() statement to do this, although I now have 7 values and this makes the IIF() statement far too ‘involved’.

Figure 1. shows the Metrics parameter and some of the values used:

Fig 1. Setting the Metrics Parameter Values

The expression that is used in the chart (to get the data) is as follows:

=CHOOSE(Parameters!Metrics.Value, Fields!TotalItems.Value, Fields!OpenItems.Value, Fields!ClosedItems.Value, Fields!AveragePrice.Value, Fields!GrossProfit.Value)

Fig 2. Setting the Expression for the Data Values

When the user selects Total Items from the Parameter drop-down, a value of ‘1’ is passed to the CHOOSE() function which in turn selects Fields!TotalItems.Value as the data to use in the Chart Object. I have also used this expression to set the appropriate Series Label value.

The DateAdd() Function in SSRS

Posted on July 20, 2009 by Luke Hayler

Getting acquainted

The DateAdd() function. According to BOL this “Returns a date to which a specified time interval has been added”. The structure of the function is as follows:

DateAdd(interval, number, date)

It’s a Date!

Like most first dates, getting to grips with this function and understanding its ‘needs’ is a tricky process. Initially, you may have attempted to use the tried-and-trusted methods (aka ‘syntax’), DateAdd(mm, 1, Now()). No. Ok, let’s try DateAdd(‘mm’, 1, now()). Nope. What about DateAdd(month, 1, Now()). Good luck with that…Yes. It’s a little mystifying.

Score!

There are a couple of ways to achieve the desired result with this function. 1.) use the DateInterval collection, or 2.) use the date part abbreviations. My recommendation? Use the DateInterval collection. Using the abbreviations, there is a chance that something will be calculated differently to what you’d expect (I explain a little further on). The DateInterval collection can be found in the Object Browser and looks like this:

Fig 1. A list of all the Intervals in the DateInterval collection

“So how do you use this in the function?” I hear you ask.

=DateAdd(DateInterval.Month, 1, Today()) which gives you a date 1 month from today.

This will work for all the intervals listed in Fig 1. above.

If you don’t want to use this method and instead want to use the abbreviations then there are a few things to be weary of. For instance, using DateAdd(“w”, 1, Today()) does not yield a date 1 week from today, but rather 1 day from today. This happens with a couple of other abbreviations as well. The reason is that “w” represents “weekday” not “week”. Here is an shortlist of the abbreviations that do work:

Days ["dd"] – DateAdd(“dd”, 1, today())
Weeks ["ww"] – DateAdd(“ww”, 1, today())
Months ["mm"] – DateAdd(“mm”, 1, today())
Years ["yyyy"] – DateAdd(“yyyy”, 1, today())

For a full list of abbreviations and further explanation, msdn delivers here.

Hopefully this clears up a little of the confusion surrounding the DateAdd() function and the intervals that need to be specified. Of course, if there are other methods that can be used or I’m all ears.

I like keep an open mind. When working with windows, one must.

Luke Hayler – SQL Server Developer

A blog about Business Intelligence and other personal interests

Tag Archives: expressions