How to skip Weekends in DATEADD

In many cases, DATEADD function might need to use skipping weekends (Saturday and Sunday). For example, you might need to find all the invoices that are pending more than 5 working days.

SELECT *
FROM dbo.Invoice
WHERE [DAYSADDNOWK](InvoiceCreatedDate,5) < GETDATE()

CREATE FUNCTION DAYSADDNOWK(@addDate AS DATE, @numDays AS INT)

RETURNS DATETIME

AS

BEGIN

    SET @addDate = DATEADD(d, @numDays, @addDate)

    IF DATENAME(DW, @addDate) = 'sunday'   SET @addDate = DATEADD(d, 1, @addDate)

    IF DATENAME(DW, @addDate) = 'saturday' SET @addDate = DATEADD(d, 2, @addDate)

  

    RETURN CAST(@addDate AS DATETIME)

END

GO

This function is work for up to add 7 days. To support more than 7 days, the following function is used.

CREATE FUNCTION DAYSADDNOWK(@addDate AS DATE, @numDays AS INT)

RETURNS DATETIME

AS

BEGIN

    WHILE @numDays>0

    BEGIN

       SET @addDate=DATEADD(d,1,@addDate)

       IF DATENAME(DW,@addDate)='saturday' SET@addDate=DATEADD(d,1,@addDate)

       IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,1,@addDate)

  

       SET @numDays=@numDays-1

    END

  

    RETURN CAST(@addDate AS DATETIME)

END

GO

The above functions are taken from below link. Please read this article for more deatils

https://social.technet.microsoft.com/wiki/contents/articles/30877.t-sql-extending-dateadd-function-to-skip-weekend-days.aspx

Note that this functions are not consider the holidays.

Cheers! Uma

What is .ispac file in SSIS and deploy using

This blog post will give clear idea for the following questions related to ispac file in SSIS

1. What is .ispac file in SSIS
2. How to create ispac file
3. How to deploy using ispac file
4. How to create solution in Visual studio using ispac file

What is .ispac file

Specifies the file format for the SQL Server Integration Services project deployment file, which is a file type that is used to represent the packaged metadata of a data integration project. At the centre of the project deployment model is the project deployment file (.ispac extension). The project deployment file is a self-contained unit of deployment that includes only the essential information about the packages and parameters in the project. 

How to crate .ispac file

The project deployment model was introduced in SQL Server 2012 Integration Services (SSIS). If you used this model, you were not able to deploy one or more packages without deploying the whole project prior to SQL Server 2016. For the purpose, isapac file format introduced. This is something similar to create SSISDeploymentManifest in previous SQL Server SSIS versions.

When you run a package, behind the scenes Visual Studio will first build/compile all the required project elements into a deployable quantum called an ispac. This will be found in the bin\Development subfolder for your project. Using this file, you can deploy the project without Data Tool or Visual Studio.

How to deploy SSIS packages using ispac

In SSISDB create a folder then right click and click on deploy project and follow the wizard

Create a visual studio project

You can import ispac file into visual studio

Open Visual Studio

File -> New -> Project (Business Intelligence -> Integrations Serices)

Select “Integration Services Import Project Wizard”

Cheers!

Uma

How to configure Database Mail using Gmail SMTP account

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.

I have created this step by step guide Installation Guide. Images are used to explain the process easier.

Make sure you have ticked on SSL for Gmail account

Once you configure an Email profile, you can edit using the same way

You also can query and find the Database Mail profile

How to find profile name

SELECT  [sa].[account_id]

, [sa].[name] as [Profile_Name]

, [sa].[description]

, [sa].[email_address]

, [sa].[display_name]

, [sa].[replyto_address]

, [ss].[servertype]

, [ss].[servername]

, [ss].[port]

, [ss].[username]

, [ss].[use_default_credentials]

, [ss].[enable_ssl]

FROM

msdb.dbo.sysmail_account sa

INNER JOIN msdb.dbo.sysmail_server ss

ON  sa.account_id = ss.account_id

Cheers! Uma

How to send email notification in SSIS package using Gmail SMTP Server

In many cases, email notification is mandatory for ETL development. However, most of the small business clients usually don’t have their own SMTP server, in this case you can use Gmail SMTP server. There are so many articles about this topic, but many of them having bugs in the code. Hope this would help to run bug free and easily understandable. Also this code works well in Azure virtual machines too.

Design SSIS job using with required parameters. In here, Email Body, Email Subject and Email To parameters/variables are used.

Create a Script task using below code, this code would help to send email with multiple recipients.

Actual C# Code:

public void Main()

{

SmtpClient smtp = new SmtpClient("smtp.gmail.com", 587);

smtp.EnableSsl = true;

smtp.UseDefaultCredentials = false;

smtp.Credentials = new NetworkCredential("uma@gmail.com", "*********");

MailMessage msg = new MailMessage();

msg.IsBodyHtml = true;

msg.From = new MailAddress("umashan@gmail.com");

string addresses = Dts.Variables["$Project::EmailTo"].Value.ToString();

foreach (var address in addresses.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries))

{

msg.To.Add(address);

}

msg.Subject = Dts.Variables["User::EmailSubject"].Value.ToString();

msg.Body = Dts.Variables["User::EmailBody"].Value.ToString();

smtp.Send(msg);

Dts.TaskResult = (int)ScriptResults.Success;

}

enum

ScriptResults

{ Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

};

Cheers!

Uma

Calculate Number of Business Days / Working Days between 2 given dates using Date Dimension

This article demonstrates ways to calculate the number of business days or working days between 2 given dates. Business days’ calculation is depending on country or sometime reign or may be specific for a company. If you need to ignore holiday then you don’t need to follow this approach, you can use SQL default functions.

Follow the below steps:

Step 1: Introduce 2 flag in Date dimensions as shown below:

2. Crete a function as shown below

Note: In this function, date key (primary key of date dimension) is used instead of date. The reason is to get best performance out of this function when we run on large query. Primary key would use index seek instead of index scan in where condition in a query.

3. Now you can use this function in a query, even in a huge data set this would give good performance. However, using function against large data set is not recommended practice.

SELECT dbo.getNoOfWokingDays('2016-10-10','2016-10-13')

Calculating weekdays

If you need to calculate number of weekdays, you can use SQL techniques, I mean without considering holidays.

Note: You have to modify the above script before use this code according to your requirements.

Code:

/***Calculate Business Days / Working Days ****/

CREATE FUNCTION dbo.getNoOfWokingDays(@startDate date, @endDate date)

RETURNS int

AS   

BEGIN  

DECLARE @startDateKey int = CONVERT( INT, CONVERT(VARCHAR(8), @startDate, 112))

DECLARE @endDateKey int = CONVERT( INT, CONVERT(VARCHAR(8), @endDate, 112))

DECLARE @days int;  

SELECT @days=(SUM(CASE WHEN [WeekEndFlag] = 'N' AND [HolidayFlag] = 'N' THEN 1 ELSE 0 END))

FROM [dbo].[DimDate]

WHERE [DateKey]>= @startDateKey AND [DateKey]<@endDateKey;

IF (@days IS NULL OR @startDateKey=@endDateKey)   

SET @days = 0;  

RETURN @days;  

END;

You can test the function as shown below:

SELECT dbo.getNoOfWokingDays('2016-10-10','2016-10-13')

/***Calculate Weekdays ****/

DECLARE @startDate date, @endDate date

SET @startDate='2016-10-10'

SET @endDate = '2016-10-13'

SELECT

DATEDIFF(day, @startDate, @endDate) AS [No of Days]

,DATEDIFF(dd, @startDate, @endDate)

- (DATEDIFF(wk, @startDate, @endDate) * 2) -

CASE

WHEN DATEPART(dw, @startDate) = 1 THEN 1 ELSE 0 END +

CASE

WHEN DATEPART(dw, @endDate) = 1 THEN 1 ELSE 0 END AS [No of Working Days]

Cheers!
Uma