Skip to main content

Posts

Email Alerts are not received...

In one of application, Email alerts are sent using SQL sp_send_dbmail stored procedure. Email alerts were working properly, but suddenly noticed email alerts are not received.

To troubleshoot the issue, first I checked the msdb.dbo.sysmail_event_log table using the below query.

SELECT log_id, event_type, log_date, description, process_id, mailitem_id, account_id, last_mod_date, last_mod_user
FROM
msdb.dbo.sysmail_event_log 
ORDER BY
log_date DESC

If there is any error, you should see records with the event_type value as error.
In my case there are records with event_type value as error, and the description value for those records is same as below

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-02-01T10:41:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

As per the error descript…
Recent posts

Moving file from one folder to another in Azure Blob Storage using SSIS

In this post I'll explain how we can move a file from a folder in Azure Blob Storage Container to another folder in the same Container, and delete the original file.

Lets assume the Blob Container name is BlobContainer and the folder names are Folder1 and Folder2. Assume the file name as BlobFile.csv
File will be moved from one folder to another based on the file URL, by using WebClient in C#.

You can generate the File URL as below
BlobStorageURL=https://blobstorage.blob.core.windows.net
(blobstorage is the account name)

Then existing file URL is
https://blobstorage.blob.core.windows.net/BlobContainer/Folder1/BlobFile.csv

URL for the move file is
https://blobstorage.blob.core.windows.net/BlobContainer/Folder2/BlobFile.csv

As I mentioned in my previous post, you can get the file name of the file, available in the Azure Blob Storage using a Foreach Loop Container and possible to set those File URL values based on the file name.

Addition to above we need to know the Shared Access Signa…

Configuring SSIS Foreach Loop Container for Azure Blob Storage

Like we iterate through files in File System, we can iterate through files in Azure Blob Storage, using SSIS Foreach Loop Container.
If you consider about the Blob Storage structure, there will be Containers and inside those containers, you will find Folders.

Below Project Parameters are added to store those configurations information
BlobContainer - Stores the Blob Container name
BlobFolder - Stores the Folder name
ConnStr_AzureStorage - Stores the connection string value for the Azure Blob Storage
(Ex: DefaultEndpointsProtocol=https;AccountName=blobstorageaccountname;AccountKey=accountkey;EndpointSuffix=core.windows.net)

Addition to those, package variable is added to store the Blob File Name as BlobFileName.

To create a connection to the Azure Blob Storage, right click on the Connection Managers section in the Package and select New Connection

In the Add SSIS Connection Manager window, select AzureStorage type and click Add


Then you will get the Azure Storage Connection Manager Edito…

Dynamically Changing the SSRS Report Parameter Default Value...

I had a requirement to change the SSRS Report Parameter Default value based on some other report parameter values. Also the particular report parameter is set as Hidden.

For this requirement, I checked most of the forums, but could not find any solution. Most of the forums mentioned it is not possible since SSRS Report Viewer is not behaving like a Web Form and it is static.

But I was able to find an alternative approach for my requirement using the below steps.

Initially I set the default value for the report parameter using the Specify values option in the Default Values section. In the expression, I checked the relevant report parameter values and set the value.
But the issue I faced was, when I view the report by changing the parameter values, default value of the required parameter did not change and it was the initial default value.

To avoid this issue, what I did was, I created a data set and used the same condition inside the data set query and retrieved the value set.

Then a…

Get Period N data in MDX...

In some scenarios, we may need to get Period N data including the current period.
As an example if we select the Year as 2017, we may need to get the values for 2017, 2016, 2015, .., (2017-(n-1)).

I was able to achieve it using the below MDX Query

WITH
SET [Set_PeriodRange] AS
{STRTOMEMBER(@Param_Period+".lag(N-1))" : STRTOMEMBER(@Param_Period)}

SELECT NON EMPTY{[Measures].[Sales]} ON COLUMNS,
NON EMPTY([Set_PeriodRange]) ON ROWS 
FROM [SalesCube]

Here what I did was I created a set for the what ever the required period range, using the lag function.
Then use that Set for the Row axis in the MDX.
@Param_Period is the parameter with the Year value.

The use of the created Set is that if you pass the parameter value based on a hierarchy, it will return the members for the current level of the hierarchy.
If the current level is Quarter, it will return the N number of Quarters.

Hope this will be helpful...


Hide SSRS Report Items while exporting...

While exporting to PDF, EXCEL or any other format in SSRS report, we may need to hide some images or some contents of the report.
We can set the Visibility of that report content using the below expression for the "Show or hide based on an expression" option

=IIF(Globals!RenderFormat.Name="EXCEL" OR Globals!RenderFormat.Name="PDF", true, false)

Here I just set the visibility for EXCEL and PDF format only, but you can add other formats as well.

Also please note the if it is not working for render format value as "EXCEL", check it with Globals!RenderFormat.Name="EXCELOPENXML"

How to get the Last Month value of a Measure for a Period in MDX...

There was a requirement to show only the last month value for a particular measure, for the given period.
If the period is 2016, then it show the measure value of the 2016 Dec.
If the period is 2016 Q3, then the measure value has to be 2016 Sep.

I was able to achieve it, by creating a calculated measure using ClosingPeriod function.

Formula of the Calculated Measure is as below

(ClosingPeriod([DimDate].[Year-Quarter-Month].[Month], [DimDate].[Year-Quarter-Month].CurrentMember),[Measures].[Count])

You can test it using the below MDX Query as well.

WITH MEMBER [Measures].[LastMonthValue]
AS (ClosingPeriod([DimDate].[Year-Quarter-Month].[Month], [DimDate].[Year-Quarter-Month].CurrentMember),[Measures].[Count])

SELECT NON EMPTY { [Measures].[LastMonthValue] } ON COLUMNS
 FROM [CubeName]
WHERE STRTOSET(@Period,CONSTRAINED)

Hope this will be helpful...