We might be interested SSRS features as well. If it were to be implemented, then two distinct tasks would be ideal.
1.) Schedule SSRS report - This task would insert the to-be-executed report into the SSRS scheduler. SSRS has it's own load balancing for report execution but VisualCron has better scheduling, looping, reporting naming, and other capabilities.
2.) Execute SSRS report - This task would immediately execute a report using .NET and reflection.
Sample Xml/SQL Code for #1.)
<DataSets>
<DataSet Name="ReportSubscriptions">
<Query>
<DataSourceName>ReportServerDB</DataSourceName>
<CommandText>SELECT
S.[SubscriptionID]
,C.Name + ' (' + S.[Description] + ')' AS DisplayName
FROM [dbo].[Subscriptions] S
INNER JOIN [dbo].[Catalog] C ON S.Report_OID = C.ItemID
ORDER BY DisplayName</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="SubscriptionID">
<DataField>SubscriptionID</DataField>
<rd:TypeName>System.Guid</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="LaunchSubscription">
<Query>
<DataSourceName>ReportServerDB</DataSourceName>
<QueryParameters>
<QueryParameter Name="@EventData">
<Value>=Parameters!SubscriptionToLaunch.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>exec [dbo].[AddEvent] 'TimedSubscription', @EventData;
SELECT
@EventData AS [SubscriptionID]</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="SubscriptionID">
<DataField>SubscriptionID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="LaunchedSubscriptionDetails">
<Query>
<DataSourceName>ReportServerDB</DataSourceName>
<QueryParameters>
<QueryParameter Name="@LaunchedSubscription">
<Value>=Parameters!LaunchedSubscription.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>SELECT
S.[Description] AS [SubscriptionDescription]
,C.[Name] AS [ReportName]
,S.[DeliveryExtension]
FROM [dbo].[Subscriptions] S
INNER JOIN [dbo].[Catalog] C ON S.[Report_OID] = C.[ItemID]
WHERE
S.[SubscriptionID] = @LaunchedSubscription</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="SubscriptionDescription">
<DataField>SubscriptionDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ReportName">
<DataField>ReportName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DeliveryExtension">
<DataField>DeliveryExtension</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportParameters>
<ReportParameter Name="SubscriptionToLaunch">
<DataType>String</DataType>
<Prompt>Select Subscription To Launch</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>ReportSubscriptions</DataSetName>
<ValueField>SubscriptionID</ValueField>
<LabelField>DisplayName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="LaunchedSubscription">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>LaunchSubscription</DataSetName>
<ValueField>SubscriptionID</ValueField>
</DataSetReference>
</DefaultValue>
<Hidden>true</Hidden>
<ValidValues>
<DataSetReference>
<DataSetName>LaunchSubscription</DataSetName>
<ValueField>SubscriptionID</ValueField>
<LabelField>SubscriptionID</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
try
{
Console.WriteLine("Working On " + Dts.Variables["User::AnotherParameter"].Value.ToString());
ExportReport(Dts.Variables["User::SSRSWebService"].Value.ToString(),
Dts.Variables["User::SSRSReportPath"].Value.ToString(),
Dts.Variables["User::SomeParameter"].Value.ToString(),
Dts.Variables["User::AnotherParameter"].Value.ToString(),
Dts.Variables["User::OneMoreParameter"].Value.ToString(),
Dts.Variables["User::WouldYouBelieveAnotherParameter"].Value.ToString(),
Dts.Variables["User::TheLastParameterIPromise"].Value.ToString());
Console.WriteLine("Finished " + Dts.Variables["User::AnotherParameter"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex1)
{
// Try a second time
try
{
Console.WriteLine(ex1.Message);
Console.WriteLine("Retrying Attempt 2 of 3...");
ExportReport(Dts.Variables["User::SSRSWebService"].Value.ToString(),
Dts.Variables["User::SSRSReportPath"].Value.ToString(),
Dts.Variables["User::SomeParameter"].Value.ToString(),
Dts.Variables["User::AnotherParameter"].Value.ToString(),
Dts.Variables["User::OneMoreParameter"].Value.ToString(),
Dts.Variables["User::WouldYouBelieveAnotherParameter"].Value.ToString(),
Dts.Variables["User::TheLastParameterIPromise"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex2)
{
// Try a third and final time, if we still fail then return failure.
// These attempts are intended to deal with occasional glitches between PostGresSQL and our environment
try
{
Console.WriteLine(ex1.Message);
Console.WriteLine("Retrying ");
ExportReport(Dts.Variables["User::SSRSWebService"].Value.ToString(),
Dts.Variables["User::SSRSReportPath"].Value.ToString(),
Dts.Variables["User::SomeParameter"].Value.ToString(),
Dts.Variables["User::AnotherParameter"].Value.ToString(),
Dts.Variables["User::OneMoreParameter"].Value.ToString(),
Dts.Variables["User::WouldYouBelieveAnotherParameter"].Value.ToString(),
Dts.Variables["User::TheLastParameterIPromise"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (System.Exception ex3)
{
Console.WriteLine(ex3.Message);
Dts.TaskResult = (int)ScriptResults.Failure;
try
{
//Try to Create a File warning the user about the problem
string noResults = "Unable to produce results for the requested report. Please try again.";
byte[] bytesNoResults = new byte[noResults.Length * sizeof(char)];
System.Buffer.BlockCopy(noResults.ToCharArray(), 0, bytesNoResults, 0, bytesNoResults.Length);
var exportedFile = File.Create(Dts.Variables["User::FilePath"].Value.ToString().Replace(".","_WARNING_"), bytesNoResults.Length);
exportedFile.Write(bytesNoResults, 0, bytesNoResults.Length);
exportedFile.Flush();
exportedFile.Close();
}
catch(System.Exception ex4)
{
Console.WriteLine(ex4.Message);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}
}
private void ExportReport(string ssrsWebService
, string ssrsReportPath
, string SomeParameter
, string AnotherParameter
, string OneMoreParameter
, string WouldYouBelieveAnotherParameter
, string TheLastParameterIPromise
, string reportFormat = "Excel")
{
// Report Server Settings
var reportExecutionService = new ReportExecutionService();
reportExecutionService.Credentials = System.Net.CredentialCache.DefaultCredentials;
reportExecutionService.Url = ssrsWebService;
var reportPath = ssrsReportPath;
// Prepare Report Parameters
var parameters = new ParameterValue[5];
parameters[0] = new ParameterValue();
parameters[0].Name = "NameOfSomeParameter";
parameters[0].Value = SomeParameter;
parameters[1] = new ParameterValue();
parameters[1].Name = "NameOfAnotherParameter";
parameters[1].Value = AnotherParameter;
parameters[2] = new ParameterValue();
parameters[2].Name = "OneMoreParameter";
parameters[2].Value = OneMoreParameter;
parameters[3] = new ParameterValue();
parameters[3].Name = "WouldYouBelieveAnotherParameter";
parameters[3].Value = WouldYouBelieveAnotherParameter;
parameters[4] = new ParameterValue();
parameters[4].Name = "TheLastParameterIPromise";
parameters[4].Value = TheLastParameterIPromise;
reportExecutionService.LoadReport(reportPath, null);
reportExecutionService.SetExecutionParameters(parameters, "en-us");
//Set Rendering Parameters
var format = reportFormat;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
reportExecutionService.Timeout = 3600000;
byte[] result = reportExecutionService.Render(format, null, out extension, out mimeType, out encoding, out warnings, out streamIDs);
//Create File
var exportedFile = File.Create(Dts.Variables["User::FilePath"].Value.ToString(), result.Length);
exportedFile.Write(result, 0, result.Length);
exportedFile.Flush();
exportedFile.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}