Setting an Alert on a Stored Procedure

Recently I came across an issue where I needed to set an alert that when a report is populated it needs to send out an alert. Unfortunately in SSRS you can only do subscriptions and I have not found a way to do those kinds of alerts on an SSRS report.

A workaround I found for this was to set an alert on the stored procedure the report is using. (Hoping you are using stored procedures for SSRS reports and not a large query in the report dataset) You can do this by applying the IF statement with the Execute dbmail proc. See below:

CREATE PROCEDURE dbo.DepartmentAlert
AS
SELECT *
FROM HumanResources.EmployeeDepartmentHistory

--If there are more than or equal to 1 record pulled back it will fire the email.	
IF
(
	SELECT	COUNT(*)
	FROM HumanResources.EmployeeDepartmentHistory
) >= 1
BEGIN
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Alerting'
								,@recipients = 'jeanne.c17@gmail.com'
								,@body = 'Department history available'
								,@subject = 'Department History Availability'
								,@body_format = 'HTML'
	;
END
;

Next I created an SSIS package that uses a SQL execute task to execute my Stored Procedure. Then scheduled this to run every few hours and when it meets the IF statement validity then the mail is sent out to alert whoever is in the @recipients list.

Hope this helps you as much as it helped me.

Run a PowerShell Script from SSIS

It can be confusing to know which tool in the SSIS toolbox to use when trying to execute a PowerShell script from within SSIS.

The best task to use is an Execute Process Task. Under General you can give it a name and a description.

On the process section you fill in all the necessary details. Such as Executable, Arguments and Working Directory.

Once this is complete you can then execute your PowerShell script from within SSIS.

SSIS Save to XML Output

There isn’t much online about saving an output of data to an XML file. When trying with a file system task it throws errors. There is a workaround for this.

You can use a data flow task in SSIS to export to a flat file that is an XML file. You simply need to have a query that outputs in XML format, for example you can execute a stored procedure that returns the data in XML format:

SELECT [<?xml version="1.0"?> <batch>] = X.XMLString
FROM
(
SELECT XMLString = '<Messages xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.net/Wonga.Payments.Csapi.Commands"><CreateTransaction><ApplicationGuid>' + CONVERT(VARCHAR(36), ApplicationID) + '</ApplicationGuid><PostedOn>' + CONVERT(VARCHAR(19), X.EffectiveDate, 126) + '</PostedOn><Scope>' + CASE
WHEN AmountToPost < 0 THEN '2'
WHEN AmountToPost > 0 THEN '1'
ELSE '0'
END + '</Scope><Type>' + '18' + '</Type><Amount>' + CONVERT(VARCHAR(10), AmountToPost) + '</Amount><Mir>' + '0' + '</Mir><Currency>710</Currency><Reference>' + Reference + '</Reference><SalesforceUsername>User</SalesforceUsername></CreateTransaction></Messages>'
FROM
(
SELECT *
FROM #DataSet
) X
UNION
SELECT '</batch>'
) X
ORDER BY CASE
WHEN X.XMLString LIKE '<?%' THEN 1
WHEN X.XMLString LIKE '<M%' THEN 2
ELSE 3
END

EXEC dbo.GenerateXML WITH RESULT SETS
((
[<?xml version="1.0"?> <batch>] VARCHAR(MAX)
))

You use the Execute XML as the source from the database and then you use a flat file as your destination with .xml as the extension.

This should solve your problem of outputting to an XML file.

SSRS Cancel Report Still Running In The Background

There are some people who cancel a SSRS report because its taking to long by just closing visual studio or their browser. Then they reopen SSRS/browser and try and view the report again. In the mean time the previous report they were trying to run the query is still running on the database. This takes up quite a bit of resources and people think that there query has been cancelled, but this in not the case.

This is a common problem with users accessing reports. There is a way to stop the query from running in the background by using the KILL command, however you will need a DBA to do this for you unless you have permission to do this your self.

Hope this helps other who are struggling with the same issue 🙂

SSIS Email Task Delay Validation

Recently I was trying to send a file via Email in SSIS.

For the connection to the file I was using a variable as the file name changes everyday.

This was causing an issue because it validates the whole package before the file gets generated and the package would fail.

The simplest solution was to delay the validation on the Send Mail Task. You can do this in the properties by setting Delay Validation to True.

Hope this helps you!

MDS “Service Unavailable” Error

After restarting a server that has MDS on, I could no longer access MDS via the browser. This seemed very strange as I have restarted the server multiple times and it had never done this before.

Turns out the Application Pool had stopped for MDS. So I started it up and then it would stop again, frustrating me further.

Then is occurred to me that it was using my domain details and my password had changed. Then I went into the advanced settings of my MDS Application Pool to update my details under Identity.

And there we go, it works and I can access MDS again via the browser!

Hope this helps someone else with this error.

Limiting SSIS to a Single File Type

A while ago I was having some issues with SSIS and moving files. There was a hidden file called thumbs.db

When SSIS was running the move file task it would fail because it can’t place duplicate files in the same location. The thumbs.db file was the problem. Even though it was hidden as a windows file, SSIS still saw it and wanted to move it.

This is where limiting SSIS to a specific file type saves you from this problem.

To do this you filter on the Foreach Loop Container. You select the container and then you click on Properties.

In the Properties right at the top you shall see FileSpec. There you can set it to the file type you would like to filter/limit for.

And that’s how you limit SSIS to a specific file type!

SSIS Error – the path for isserverexec.exe cannot be found

Recently been working with getting multiple SQL Instances on one machine to work. When I uninstalled an old one it affected the latest one. Then all of a sudden my ETL jobs failed. It did not make sense at all seeing as I removed a different instance.

Got the following error when trying to run my ETL jobs or when trying to deploy the jobs:
“the path for isserverexec.exe cannot be found”

Looking for solutions online, they recommend going into the registry. Which didn’t work for me.

I ended up using the install file and running it to add features to an existing connection. And there is was Integration services was not installed for the instance that I was getting the error with.

Installed SSIS on the current instance that needed it and all worked perfectly after that and the jobs ran as perusal.

Hope this helps someone else in the future.

PowerBI Campaign/Brand Management for Twitter

PowerBI has released a fantastic template for Twitter called Campaign/Brand Management for Twitter. After following the “wizard” you will have a complete report filled with sophisticated analytics.

In order to set this up, all you need is a twitter account and an Azure account. If you would like to play with this you can set up a trial Azure account.

To get started you can go to the following link https://powerbi.microsoft.com/en-us/solution-templates/brand-management-twitter/
This will open the following page. Select install to start the “wizard”.

Next you will get a summary of what is needed to complete this Twitter template.

On the Login page you will connect to Azure. If you do not have a subscription there is a link to set up a free Azure trial. You will then select whether you have a personal or work Azure account.

If you are not sure what to put in the Azure Tenant section you can find this in the azure portal. If you hover over your icon on the top right hand corner it shall show you some details. It is the Domain details that you shall place in the Azure Tenant. It will have the “.onmicrosoft.com”

Once you click Connect to Azure you will need to accept that the Business Platform Solution Template can access your Azure account.

Once accepted you shall get a confirmation page that all was successfully validated and you can click on next to progress to the Source page.

On the Source page you will specify the database name to store the Twitter data, a username and a password. Make sure the password contains upper case, lower case and a special character.

Next you will need to check for availability, sometimes the database name you choose is already taken on another azure server.

Once validated you shall get the below screen and you can select next to go onto the Twitter page.

On this page you shall connect to Twitter. This will connect to your actual Twitter account. You can get data about any Twitter account using this. It is not limited to your own just because you are logging in. In this demo I take a look at the @MSPowerBI twitter account.

Complete your Twitter login details and authorize the Azure app to have access.

Once authorised you will get a screen to show you that your Twitter account has been validated and you can move onto the next page.

On the Search Terms page you can place in the twitter handles you would like to take a look at or the keywords you are keen on. You can do both by separating the two with the word OR.

Once validated you move onto the Twitter Handles page. This is where you can search all the Twitter handles you are interested in by separating them with a space. For this I took a look at @MSPowerBI and @Azure

After this you reach the Summary page. Once you click on run it starts to process all the data and build the report.

You then move onto the Deploy page where you can see the progress of the data being extracted and compiled in a report.

Once complete you shall get a notification and you can download your pbix file to view on your desktop.

Windows Update KB2919355 Not Applicable

When trying to install SQL, Windows Update KB2919355 is a prerequisite, and on a new server this is not always installed and the SQL install stops until it is rectified.

Occasionally when you try and install the update you get the error KB2919355 is not applicable, yet the update is not installed. After searching the internet high and low a solution was found.

If you install Windows Update KB2975061 first and restart the machine and then install KB2919355 and restart the machine again, the SQL install prerequisites will be successful and you can continue with your install.

Hope this solution helps you and you get less grey hair and frustration trying to figure it out.