Thursday, November 17, 2016

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
Note that this functions are not consider the holidays.

Cheers! Uma

Wednesday, October 19, 2016

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

2086-d4c4ddaa-5829-4661-af8b-940f458ee26

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

Thursday, October 13, 2016

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