I was looking over a package today that a colleague had built and it contained the following code in a Script Task that was being used to return today’s day name:
Public Sub Main()
'
Dim sWeekday As String
sWeekday = Now().DayOfWeek.ToString
Dts.Variables("User::DayName").Value = sWeekday
Dts.TaskResult = Dts.Results.Success
End Sub
Nothing wrong with that, perfectly valid code and it does a simple thing perfectly well. It did get me thinking though – is it really necessary? Can we achieve the same thing with variables? Well, yes we can; we need two of them but I don’t see that as a problem.
First we an expression to return the today as an ordinal number in the week. Here’s the expression:
DATEPART( "dw", GETDATE() )
That expression was used to dynamically generate the value to be returned for a variable called @[User::DayOfWeek]. I then used that variable in a second expression:
@[User::DayOfWeek] ==1 ? "Monday" : (@[User::DayOfWeek] ==2 ? "Tuesday" : (@[User::DayOfWeek] ==3 ? "Wednesday" : (@[User::DayOfWeek] ==4 ? "Thursday" : (@[User::DayOfWeek] ==5 ? "Friday" : (@[User::DayOfWeek] ==6 ? "Saturday" : "Sunday" ) ))))
which uses the day ordinal number in (what is effectively) a case statement to work out which day it is. That expression is used to return the value for a variable called @[User::DayName]. Here I show the results of those variables in the Variables pane:
So we have achieved the same as the Script Task using variables. I’m not saying that one way is correct and the other isn’t but its nice to have options. Personally I would prefer to use the variables approach because it means less executables in the package; thus we get a cleaner looking package and less useless reportage in our log file – you may have a different opinion of course.
If you want it I’ve made the package available on my Skydrive:
It was built on SSIS2005 so you’ll be able to open it in any version.
For similar tips head to SSIS Nuggets.
-Jamie