Dismantle Me

There have been several times recently where I have been stumped by SSIS’s inability to parse out expressions that are strings.

I assume most of the time that if it’s a Microsoft product, then its VB script. This is not the case in SSIS.

When evaluating strings, SSIS will evaluate the “\” as an escape character. So if you have a file path of \\server\relativepath, you will throw an error when you try and parse that as an expression.

In C# we would qualify the string as literal with the @ sign. This does not work in SSIS. You have to go the full distance and enter one escape character per escape character to be treated as a literal string.

In other words – \\server\relativepath becomes \\\\server\\relativepath.

I hope this saves somebody some time as it can be a real stumper.

EDIT
It has been brought to my attention that this post may encourage some poor practices in SQL Server coding. Please read Jeff’s blog on this same exact issue here…

http://weblogs.sqlteam.com/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspx

Decide which method suits your needs best. This method might seem easier, but may jump up and bite you in the long run.
END EDIT

I just wrapped up a project where I had some trouble with SQL Server DateTime values and how to properly get differences in time.

At first, I was pretty sure that I could just compare DateTime values. Since I was only concerned about times, I had to get rid of the date portion first. Here is a handy user function that will return just the time portion of a DateTime…

CREATE FUNCTION [dbo].[ufn_GetTime] ( @DateTime DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @RetDate VARCHAR(50)
SELECT @RetDate = RIGHT(CONVERT(VARCHAR(50), @DateTime, 100),7)
RETURN @RetDate
END

BTW – I didn’t write that function, I found it somewhere online. I can’t remember where but it was probably at Pinal Dave’s Blog.

So that will return you just the time portion of a datetime value. So if you pass it ’08/05/2008 8:50:00 AM’, you will get back ’8:50AM’. That is a valid time for SQL Server.

Now you need to cast that time to a date again so you can compare it with a different time…

CONVERT(DATETIME, dbo.ufn_GetTime(’08/05/2008 07:00:00 AM’))

That is going to give you back SQL Server’s “base” date – which is ’01/01/1900 7:00AM’. That is a valid DateTime. Now you can compare against your other DateTime value by running it through the same function and casting it back to a date. What you have essentially done is made the DATE part the same day (01/01/1900) and you have just the times for that one day.

Now you can compare the times…

DECLARE @StartTime DateTime
DECLARE @EndTime DateTime
DECLARE @TimeDifference int

SET @StartTime = CONVERT(DateTime, dbo.ufn_GetTime(’08/05/2008 07:00:00 AM’))
SET @EndTime = CONVERT(DateTime, dbo.ufn_GetTime(’08/05/2008 08:50:00 AM’))

SET @TimeDifference = DATEDIFF(HH, @StartTime, @EndTime)
PRINT @TimeDifference

The DATEDIFF function figures out how many hours apart you are. Likewise, you could substitute ‘MM’ for minute and so on and so forth.

This strategy is great when you care only about the time – not about the date. It would be great if SQL Server had a built-in TimeSpan type like .NET does, but currently you have jump through some small hoops to get there.