Skip to main content


Showing posts from August, 2012

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
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…

SSIS Row Count Component...

Recently I used the Row Count component in SSIS toolbox. I used it in a Data Flow to set the row count of a Lookup match output. When I tried to get that set value in a Conditional Split component in the same Data Flow, I found that the variable has the default value and the row count was not set for that variable.

But when I get the variable value using Script Task, I found that the variable has the row count.
Actually the issue is the row count value is available after the completion of the Data Flow task. We cannot access the variable inside the same data flow task which sets the row count value.

Due to that I have to use two Data Flow tasks. In the first data flow task I set the row count value to a variable using Row Count component and in the other data flow task, the variable value is checked and do the required processing.

Replace double quotes in SSIS...

Recently I had a requirement to load csv file data which is generate from FoxPro.
But when I checked the csv file, found that it has additional double quotation mark at the start and the end of the value. As an example if the actual branch value is Branch1, the csv file has that as "Branch1".

Therefore to get the actual value I had to remove those double quotation mark and able to do it using REPLACE function.

I added a Derived Column component to the package and in that replace the branch column value after removing double quotes. For that I used the below expression.
1: LTRIM(RTRIM(REPLACE([branch],"\"","")))
That is it, It will give you the actual branch value correctly as Branch1.

How to show the All Member hierarchy value in a SSRS Filter...

For some filters we may need to select all the values at once. As an example if the SSRS parameter is a single valued one and user should be able to filter all the values if needed, then we need to provide All member value in the filter as well. We can achieve this easily by retrieving the all member value to the data set of the filter.

For an example we can write the query for the Branch data set, which loads the all the branches available in the [Sales-Branch] hierarchy as

1:WITH MEMBER [Measures].[ParameterCaption] AS [Sales-Branch].CURRENTMEMBER.MEMBER_CAPTION 2: MEMBER [Measures].[ParameterValue] AS [Sales-Branch].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Sales-Branch].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , {[Sales-Branch].[All],[Sales-Branch].[Branch].MEMBERS} ONROWSFROM [Sales]
In that you can see the ParameterLevel for the top record as 0 and indicates …