December 20, 2012 at 7:10 pm
I have a SSRS validation report that needs to run right after a ssis Job is complete in sql agent that loads the data for validation. How can I synchronize the two ? Thanks
December 20, 2012 at 9:04 pm
Do you actually need to physically render the report?
It is possible, but the method I know is rather "tricky" - it involves a little configuration, but can be accomplished.
First you need a config file (think of it as a rendering stylesheet), this needs to be saved to a drive somewhere (can be on the network or local to the report server, preferred). In this example I'll call it RenderingFileSetup.rss and save it to the C:\ of the Reporting Server. In the example, I am passing 2 parameters to the report itself (the RDL file), this would need to change to accommodate your report.
/*
Name of file on harddisk: RenderingFileSetup.rss
This example renders as a PDF, but can be changed to a flat file, etc.
*/
'Report Config Parameters
Dim format as string = "pdf"
Dim fileName as String = vFilename
Dim reportPath as String = vReportPath
'report parameters -->> the dimmed variables refer to the ACTUAL PARAMETERS IN YOUR RDL FILE
Dim ExampleParam1 as String = vParam1 '--> These vParam's refer to the TSQL parameters you are passing into the report from the code above
Dim ExampleParam2 as String = vParam2 '--> These vParam's refer to the TSQL parameters you are passing into the report from the code above
Public Sub Main()
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string = Nothing
Dim mimeType as string = "application/pdf"
Dim extension as string = "pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "ExampleParam1"
parameters(0).Value = ExampleParam1
parameters(1) = New ParameterValue()
parameters(1).Name = "ExampleParam2"
parameters(1).Value = ExampleParam2
Dim execHeader AS New ExecutionHeader()
Dim rpt AS New ExecutionInfo
rpt = rs.LoadReport(reportPath, Nothing)
rs.SetExecutionParameters(parameters, "en-us")
rs.ExecutionHeaderValue = execHeader
rs.ExecutionHeaderValue.ExecutionID = rpt.ExecutionID
results = rs.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
Next is the code to actually call and generate the report
/* DECLARE VARS NEEDED */
DECLARE @ErrNo varchar(255), @ErrMsg varchar(255), @Body varchar(max), @Subject varchar(255)
DECLARE @cmdRun varchar(1500), @Result int
DECLARE @FileName varchar(max), @ReportPath varchar(max)
DECLARE @To varchar(255), @Bcc varchar(255)
/* Assign default Values */
SELECT @To = 'you@yourcompany.com', @Bcc = 'dba_alerts@yourcompany.com'
/* GENERATE REPORT */
BEGIN TRY
SET @FileName = '\\UNCPath\TOWhereReportShouldRender\ReportName.pdf'
SET @ReportPath = '/ReportFolder/ReportName' --<< This refers to the REPORT SERVER PATH i.e. (http://MyServer/reportserver/ReportFolder)
SET @cmdRun = 'rs.exe -e Exec2005 -s http://YourReportServer/reportserver/ -i ' + '"C:\RenderingFileSetup.rss"'
+ ' -v vFilename=' + @FileName + ' -v vReportPath=' + @ReportPath + ' -v vParam1="0" -v vParam2="0" -t -l0'
EXEC @Result = xp_cmdshell @cmdRun
IF (@Result = 1)
SELECT 1/0
--PRINT @cmdRun
SET @Body = 'A report you requested is ready for viewing' + CHAR(13) + @FileName
SET @Subject = 'Your Subject - Name of Report'
EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc,
@subject = @Subject, @body = @Body, @importance = 'High'
END TRY
BEGIN CATCH
SELECT
@ErrNo = ERROR_NUMBER(), @ErrMsg = ERROR_MESSAGE()
SET @ErrMsg = 'ERROR :: ' + @ErrNo + CHAR(13) + 'There was a problem generating the report!'
SET @Body = @ErrMsg + CHAR(13) + CHAR(13) + 'Command attempted: ' + CHAR(13) + @cmdRun + CHAR(13) + CHAR(13)
+ 'The DBA''s have been notified about the cause of this error and can assure you that the public flogging of the developer responsible for this error will be severe.'
SET @Subject = 'ERROR :: Error creating ' + RTRIM(@FileName)
EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc,
@subject = @Subject, @body = @Body, @importance = 'High'
END CATCH
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 20, 2012 at 9:05 pm
Do you actually need to physically render the report?
It is possible, but the method I know is rather "tricky" - it involves a little configuration, but can be accomplished.
First you need a config file (think of it as a rendering stylesheet), this needs to be saved to a drive somewhere (can be on the network or local to the report server, preferred). In this example I'll call it RenderingFileSetup.rss and save it to the C:\ of the Reporting Server. In the example, I am passing 2 parameters to the report itself (the RDL file), this would need to change to accommodate your report.
/*
Name of file on harddisk: RenderingFileSetup.rss
This example renders as a PDF, but can be changed to a flat file, etc.
*/
'Report Config Parameters
Dim format as string = "pdf"
Dim fileName as String = vFilename
Dim reportPath as String = vReportPath
'report parameters -->> the dimmed variables refer to the ACTUAL PARAMETERS IN YOUR RDL FILE
Dim ExampleParam1 as String = vParam1 '--> These vParam's refer to the TSQL parameters you are passing into the report from the code above
Dim ExampleParam2 as String = vParam2 '--> These vParam's refer to the TSQL parameters you are passing into the report from the code above
Public Sub Main()
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string = Nothing
Dim mimeType as string = "application/pdf"
Dim extension as string = "pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "ExampleParam1"
parameters(0).Value = ExampleParam1
parameters(1) = New ParameterValue()
parameters(1).Name = "ExampleParam2"
parameters(1).Value = ExampleParam2
Dim execHeader AS New ExecutionHeader()
Dim rpt AS New ExecutionInfo
rpt = rs.LoadReport(reportPath, Nothing)
rs.SetExecutionParameters(parameters, "en-us")
rs.ExecutionHeaderValue = execHeader
rs.ExecutionHeaderValue.ExecutionID = rpt.ExecutionID
results = rs.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
Next is the code to actually call and generate the report, this is what you would place in the SQL Agent job as the next step you want to run to kick off the report...
/* DECLARE VARS NEEDED */
DECLARE @ErrNo varchar(255), @ErrMsg varchar(255), @Body varchar(max), @Subject varchar(255)
DECLARE @cmdRun varchar(1500), @Result int
DECLARE @FileName varchar(max), @ReportPath varchar(max)
DECLARE @To varchar(255), @Bcc varchar(255)
/* Assign default Values */
SELECT @To = 'you@yourcompany.com', @Bcc = 'dba_alerts@yourcompany.com'
/* GENERATE REPORT */
BEGIN TRY
SET @FileName = '\\UNCPath\TOWhereReportShouldRender\ReportName.pdf'
SET @ReportPath = '/ReportFolder/ReportName' --<< This refers to the REPORT SERVER PATH i.e. (http://MyServer/reportserver/ReportFolder)
SET @cmdRun = 'rs.exe -e Exec2005 -s http://YourReportServer/reportserver/ -i ' + '"C:\RenderingFileSetup.rss"'
+ ' -v vFilename=' + @FileName + ' -v vReportPath=' + @ReportPath + ' -v vParam1="0" -v vParam2="0" -t -l0'
EXEC @Result = xp_cmdshell @cmdRun
IF (@Result = 1)
SELECT 1/0
--PRINT @cmdRun
SET @Body = 'A report you requested is ready for viewing' + CHAR(13) + @FileName
SET @Subject = 'Your Subject - Name of Report'
EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc,
@subject = @Subject, @body = @Body, @importance = 'High'
END TRY
BEGIN CATCH
SELECT
@ErrNo = ERROR_NUMBER(), @ErrMsg = ERROR_MESSAGE()
SET @ErrMsg = 'ERROR :: ' + @ErrNo + CHAR(13) + 'There was a problem generating the report!'
SET @Body = @ErrMsg + CHAR(13) + CHAR(13) + 'Command attempted: ' + CHAR(13) + @cmdRun + CHAR(13) + CHAR(13)
+ 'The DBA''s have been notified about the cause of this error and can assure you that the public flogging of the developer responsible for this error will be severe.'
SET @Subject = 'ERROR :: Error creating ' + RTRIM(@FileName)
EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc,
@subject = @Subject, @body = @Body, @importance = 'High'
END CATCH
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply