I came across the requirement of building a function that subtracts business days from a specific date value, the problem with this is you don’t know if a weekend will occur between those days, and if you are looking at more than 7 days then there could be more than one weekend.
The best way I could think of is to use a recursive CTE that keeps incrementing a counter as far as it’s not a weekend day until it reaches the specified number of days.
One thing you need to note about this method is because it uses a recursive CTE by default you will not be able to go over 70 days or so because the max recursion is 100 times so you will need to set the MAXRECURSION option to a number that is over the total of calendar days during that period, it should be added under the select statement like:
SELECT @ReturnDate = MIN(DayDate) FROM Dates
OPTION (MAXRECURSION 1000)
Here is the function:
CREATE FUNCTION fn_SubtractBusinessDays(@startDate date, @Days INT )
RETURNS DATE AS
DECLARE @ReturnDate DATE;
WITH Dates AS
SELECT @startDate AS BusinessDay, 0 AS DaysCount
SELECT DATEADD(d,-1,BusinessDay) AS BusinessDay ,
DaysCount + (CASE WHEN DATEPART(dw, DATEADD(d,-1,BusinessDay)) NOT IN (7,1) THEN 1 ELSE 0 END) DaysCount
WHERE DaysCount < @days
SELECT @ReturnDate = MIN(BusinessDay) FROM Dates
You heard that finally Microsoft provided an easy way to run an SSIS package from a stored procedure with SQL server 2012 release when you use the new project deployment model, you deploy your project and call the right stored procedures and as far as you are calling them from the same database server where they are deployed and with a windows account that has the needed permissions everything works great.
Then you decide to create a stored procedure and send it to the web developers or execute form anywhere outside of the database server and you are surprised that the package is throwing the error in the subject Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ or any other error implies that the user executing the package has insufficient permissions.
The problem is that integration services as of the 2012 release does not support credentials delegation which means even though the SSIs package is running under the right windows account it does not pass that account when it tries to access the database or file system as they say at the end of this topic.
This is really a problem because in most of the cases your application that needs to call SSIS will reside on a different server and this method is useless, the issue was reported to Microsoft and they said it will be fixed in the next release but you need a solution now! There are few workarounds but none of them is pretty but you might find them helpful in your case:
- SQL Authentication: If your problem is only with the database access then you can use SQL authentication instead of windows authentication – although it’s not recommended, and to secure the password you need to parameterize the connection manager and make the password a parameter and check the sensitive checkbox.
- Call the execution statement through a SQL job: the only command that matters when it comes to authentication in the call to the start_execution function, which is good because you don’t have to change the whole stored procedure you created and you only do it once and you use it for all packages, this is the option i ended up using as my package needs to access the file system, here is what you need to do:
- remove the call to start execution from you procedure, which is EXEC [SSISDB].[catalog].[start_execution] @execution_id
- Replace it with an insert statement to a queue table; the table will have the following fields:
- ExecutionID: bigint to hold theid for the execution instance you started.
- Processed: a bit field to indicate if the instance was executed.
- any other fields like table id or time stamp would be helpful for you.
- your stored procedure will look something like this:
Create Procedure [dbo].[usp_GenerateReport]
@ExecutionID bigint output
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'PackagName.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'Folder1', @project_name=N'Folder1', @use32bitruntime=True, @reference_id=2
--set parameters in case you have any
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'DateTo', @parameter_value=@DateTo
--EXEC [SSISDB].[catalog].[start_execution] @execution_id
--insert into my queue table
Insert into SSISProcessRequest values (@execution_id, 0)
- now create an SQL agent job that runs an SQL command under the windows account that you would like to execute the package, it will check for packages with processed flag equals false, execute them then set the flag to true, my service runs every 15 seconds and has the following SQL code:
Declare @ExecutionID bigint, @SSISProcessRequestID int
WHILE EXISTS (SELECT 1 FROM SSISProcessRequest WHERE Processed = 0)
SELECT TOP 1 @ExecutionID = ExecutionID FROM SSISProcessRequest WHERE Processed = 0 ORDER BY DateRequested;
EXEC [SSISDB].[catalog].[start_execution] @ExecutionID;
UPDATE SSISProcessRequest SET Processed = 1 WHERE ExecutionID = @ExecutionID;
- Web Service: create a web service on the SSIS server to call the package, I didn’t try this option myself and usually network admins do not prefer to open web ports on the database servers for security reasons, but it could be an option for you.