Along the way I will also comment the writing experience from using OLW.
– this rules will always return : a report name/url , an email address and the name of the email recipient.
– the ability to copy paste some MDX or SQL queries that generates the alert rules.
For example once they have more than 10 unpaid bills for a given customer they to alert the Key Account Manager for that customer with a link to a report in an email.
1 – data alerts require a data feed from a report, which in turn will require them to build SSRS reports for every of their Excel reports.
a SSIS package reading from the SharePoint alert definition list, executing the queries and saving the result into the second list.
The whole point about building SSIS packages is to get the metadata of the source for your query before you can run the query. When the query source and text changes the metadata changes and the metadata needs to be recompiled. This cannot be done at runtime. The only way to do it was to build one data flow per possible query source, which is not terrbly dynamic and will require IT intervention each time Business build a new report. Not a great solution.
So armed with a NFR license of Cozyroc component I went on building the solution. I will start by saying that I’m in no way paid or endorsed by Cozyroc for writing this post. And the following are my findings goods and bads.
The use of the component is not very well documented on the internet. There are some videos by Jamie Thompson and some people from Cozyroc out there, but none covering my exact situation. Luckily the developers at Cozyroc are really ready to give a hand. So I got in touch with Ivan Peev who helped me a lot with the errors I was encountering and the way to solve them. Thanks for your patience Ivan !
After installing cozyroc SSIS components (which was a painless experience) chosing the SSIS version to install for I created a new Integration Services Solution and had immediately access to all of Cozyroc SSIS components.
First thing first, I need a data flow task to get my data for the alerts out of SharePoint. My original idea was to have all the alert definition items into recordset to be able to loop over them, but unfortunately the components didn’t allow me to save the recordset to a variable. So I had to create a table for storing the data. This is this table I start by truncating in the Clean up Execute SQL task.
the CAML query to pass to the SharePoint list – great so I can filter columns and rows.
the view to use when querying the list.
As expected the columns I get back a list of columns and have to pick the ones I want to go on with.
I could have written a more specific CAML query to restrict the columns returned but due to the fact that it was a POC with only 10 hours for presenting a working prototype I chose not to. Lazy me !
So for each alert in my alert definition list I will get an iteration of the loop with these variables set to the right values.
Now keep in mind that what I want to do is to be able to execute a query against a datasource that I don’t have some metadata about. I still want to keep track of my AlertDefinitionID, AlertName and ReportLink. So I will need to add them with a derived column.
Now it took me some time to figure that out but I think that the Cozyroc’s data flow task plus uses a user variable to be able to dynamically populate the derived column component.
In the next blob post I will try to go into the details about the Data flow plus and how to configure the different components of the task.
Stay tuned for part II coming really soon!!