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
--If there are more than or equal to 1 record pulled back it will fire the email.
) >= 1
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Alerting'
,@recipients = 'firstname.lastname@example.org'
,@body = 'Department history available'
,@subject = 'Department History Availability'
,@body_format = 'HTML'
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.
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.
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
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'
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>'
ORDER BY CASE
WHEN X.XMLString LIKE '<?%' THEN 1
WHEN X.XMLString LIKE '<M%' THEN 2
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.
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 🙂
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!
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.
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!
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.
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.