How to Retrieve and mail OLAP data in SSIS

Question

I have a mdx query that returns valid results in Sql Server Management Studio and would like to automate the execution of this query and put the result into an email.

SSIS seems to be the natural fit for this. I have been able to run the mdx in an "Execute SQL Task" and populate an object variable with the result, but I am unsure how to parse out that object into string or xml data that I can use in a Send Mail task. Postings I have seen elsewhere on this type of thing end up going to using a "Script Task" Which would require me to change my C# thoughts into VB.NET and seem to be outside the spirit and utility of SSIS.

If there is another approach entirely that could work as well I am not married to SSIs but would like to stay within the MS SQL Server and .NET toolset.

Answer 1

You can execute C# in SSIS 2008 - when you first create the script task, select C# as the language before opening the code editor (I think it's default anyway).

If it's an object, that's your best bet for extracting data to variables for an email.

Alternatively, you could create a stored proc in SQL that performs your query and returns the data you want as output parameters. These output parameters can be mapped to variables on the execute SQL task.

Lastly, if it's a data flow type scenario (e.g. you're retrieving a result set and sending an email per row) have a look at using a data flow, perhaps with an OLEDB source or a custom script source (again, populated by a C# script)