T-SQL: Get Next/Previous Business Day

In a previous role, we used a service from a company who provided data from the last working day of each month. The application to retrieve the data was an SSIS package which was scheduled via SQL Agent. Unfortunately, the SQL Agent scheduler doesn't (yet) have the abililty to provide custom schedules and there is no inbuilt function to "run on the last working day of the month".

Our solution to this was to implement a schedule table which held a RunDate and a TradeDate, include a task which checked to see if the current day was a valid day and then run the package daily. Nice and simple. The final step was to populate the schedule table with data for the next year and this is where I had to address the T-SQL challenge.

Fortunately, there are many solutions on the web for this sort of thing, but i'm going to post my objects here for reference.

NB: I like to keep all these type of generic functions in a Utils schema so that they can managed in a database project and deployed to a database as required.


CREATE FUNCTION Utils.IsWeekDay(@Dt DATE)
RETURNS BIT
AS
BEGIN
DECLARE @IsWeekDay BIT = 1

IF DATENAME(DW,@Dt) IN ('SATURDAY', 'SUNDAY')
SET @IsWeekDay = 0

RETURN @IsWeekDay
END
GO

CREATE FUNCTION Utils.GetNextWeekDay(@Dt DATE)
RETURNS DATE
AS
BEGIN
DECLARE @NxtDt DATE

SET @NxtDt = DATEADD(D,1,@Dt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
BEGIN
SET @NxtDt = DATEADD(D,1,@NxtDt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
SET @NxtDt = DATEADD(D,1,@NxtDt)
END

RETURN @NxtDt
END
GO

CREATE FUNCTION Utils.GetPreviousWeekDay(@Dt DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @NxtDt DATE

SET @NxtDt = DATEADD(D,-1,@Dt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
BEGIN
SET @NxtDt = DATEADD(D,-1,@NxtDt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
SET @NxtDt = DATEADD(D,-1,@NxtDt)
END

RETURN @NxtDt
END
GO


-- and this is how we use the functions to get the schedule table for 2011

DECLARE @Year CHAR(8) = '20110101';

WITH Months
AS
(SELECT 0 AS Mth, CONVERT(DATETIME, @Year) AS Dt
UNION ALL
SELECT 1 + Mth, DATEADD(M, 1+Mth, @Year) AS Dt
FROM Months
WHERE Mth < 11
)
SELECT CASE Utils.IsWeekDay(Dt) WHEN 1 THEN Dt ELSE Utils.GetNextWeekDay(Dt) END AS RunDate,
Utils.GetPreviousWeekDay(Dt) AS TradeDate
FROM Months
GO



SSIS: Precedence Constraint with XML

I was using an expression for a precedence constraint in a package recently and came across an issue where, although the expression was valid (and correct, honest!!) the package would not proceed.

Essentially, i was testing that a string variable which contained XML from an XML (XSLT) task was not blank/empty:

LEN(@[User::ErrorXML]) > 0
@[User::ErrorXML]) != ""

I can see the variable in the watch list as but neither of these expression options was working. I guess this is down to the fact that you can't execute these functions on XML but i think its a bit confusing. The variable is of type string, so no matter if the SSIS Task outputs XML, i would have thought its type would have been strongly bound to that whats set in the variable. This doesn't appear to be the case as I precedence constraints on other "standard" string variables evaluate successfully.

Just something to keep any eye out for.

SSIS: Orphaned Configuration Connections

Although this issue seems to have been around a while, there doesn't seem to be stack loads of information on the web.

If you use configurations in SSIS package, in SQL 2008 an error is thrown if a connection property exists in the configuration, but not in the package. In other words, your package is expecting to find every connection that is referenced in the configuration and if it can't find it, it will throw an error.

The connection "YOURCONNECTIONNAME" is not found. This error is thrown by Connections collection when the specific connection element is not found. (Microsoft.DataTransformationServices.VsIntegration)

So, a typical scenario may be that you have a single configuration file which holds all your connection strings. It stands to reason that not all your packages will use all connections but SQL2008 packages will still expect them to be defined in your package.

This is a breaking change from SQL2005 where such a scenario would throw a warning not an error. To me, the SQL2005 behaviour makes more sense and i'll be interested to see if they revert this.

The workaround to the problem is to increase your configurations, and perhaps have a single configuration for each connection. This is probably workable in many scenarios but I can see this being quite tiresome if you have many connections and many packages.

SQL Database Project in Juneau!!

I've just watched the following video and it seems that MS are finally going to add a database project type to the default development environment.

http://www.msteched.com/2010/Europe/DAT314

Although there appear to be plenty of extra features which are impressive, just having the ability to store your database schema as a project is a massive step forward. Previously, this option was only available in Visual Studio Database Edition (aka datadude) and came at a fairly significant cost. I always thought that MS was selling DBAs/Database Developers a little short by not providing this out of the box in SSMS or BIDS but now it appears that they rectifying the situation.

It'll be interesting to see what this means for SSMS. Perhaps this will be streamlined to be more akin to the old Enterprise Manager and used as an Administration tool rather than the dev/admin hybrid that it currently exists as. Personally, although I have VS2008DE, i tend to do my development using SSMS and then really just use VS2008DE to manage the project from a source control perspective. Its certainly not ideal but SSMS is where i feel most comfortable and I think this just highlights the need for a more "joined up" approach to the SQL tool suite. Fingers crossed this is the solution.

SQLCLR: SQLChars or SqlString

Recently, i came across a post on the forums which focused on a strange error coming from a CLR function. Essentially, the OP was attempting to execute a dynamic sql string in a function (which isn't possible in a regular UDF) so he went down the CLR route. All sounds simple enough, but when calling his CLR UDF, he kept receiving the following error:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "DynamicSQL":
System.Data.SqlClient.SqlException: Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at UserDefinedFunctions.DynamicSQL(SqlChars Query, SqlInt32 OperationId, SqlSingle ConstantValue)

Here is a sample of the source code of the CLR function:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static string DynamicSQL(SqlChars Query)
{
string value;
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Sandbox;Integrated Security=True;");
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(Query.ToString(), conn);
value = cmd.ExecuteScalar().ToString();
conn.Close();
}

return value;
}

SELECT dbo.DynamicSql('SELECT 1 AS t', 1, 1)

Nothing in this looked particularly terrible to start but the error wasn't that cryptic - it was parsing the query with 4 part naming. Hardcoding the Query in the CLR resolved the issue indicating there was obviously something amiss with the Query input parameter.

A bit of quick debugging allowed me to establish what the problem was. When casting Query.ToString, the value that was being returned was System.Data.SqlTypes.SqlChars. And this value happens to be in the same format a 4 part object name in SQL Server. Voila!! There is the issue. A bit of digging online, and i found that casting SqlChars.ToString returns a String that represents the current Object. (Inherited from Object.).

A method of resolving this would either be to change the input parameter to be of type SqlString or to change the casting to:

string value = Query.ToSqlString().ToString();