Tuesday, December 11, 2012 2:19 PM
I want to create a couple of PDF reports from SSIS - from an SQL Server table. This table contains a list of errors (validation errors). So when the SSIS error package is called it will create a couple of reports and email them to specific users.
Should this all be done within SSIS in a script task? Or do you need to use SSRS?
Any pointers in the right direction would be greatly appreciated.
Tuesday, December 11, 2012 2:24 PM
I will suggest you create tables and log your errors in there and use ssrs to report.
Tuesday, December 11, 2012 2:29 PMModerator
Since exporting to PDF programmatically is not natively supported by Windows or SSIS you can use SSRS, or a third-party component (library) that make this happen. You will of course need to have your SSRS configured. Then you would need to incorporate code similar to what is in http://beyondrelational.com/modules/2/blogs/109/posts/11174/generating-and-exporting-ssrs-reports-programatically-using-report-viewer-control.aspx into a Script Task (like you have said).
Besides, in my opinion, having a PDF report, unless it is a contractual obligation of some sort, is not worth the effort. For example producing the report in HTML, Excel, etc is a much shorter development.
Arthur My Blog
- Edited by ArthurZMVP, Moderator Tuesday, December 11, 2012 2:31 PM
Tuesday, December 11, 2012 2:46 PM
Thanks for this - it doesn't have to be PDF. It could be HTML, word, excel.
I am just looking to find the easiest and best way to do this.
The error data is stored in an error table. So what is the best way to export to HTML, Excel etc? Can this be done within SSIS without using SSRS? Is a script task the best way?
Tuesday, December 11, 2012 2:54 PM
If you want to output to different format easily, the best way is to do it with SSRS in SSIS.
Randy Aldrich Paulo
MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog
BizTalk Message Archiving - SQL and File
Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
Sending IDOCs using SSIS
Tuesday, December 11, 2012 2:58 PM
Thanks - so at a very high level. You are saying its best to create the needed reports in SSRS. Then use SSIS to call SSRS which will then generate and email the reports, based on supplied parameters?
I'm quite new to this.
Tuesday, December 11, 2012 3:13 PMModerator
If you are totally new to SSIS and SSRS your best bet would be on producing a text file 1st.
Also make sure you can trap the error, it is not always easy and depends on what you do in your package.
I advocate my developers to start with some logging framework as soon as possible. One can be handling errors using Event Handlers.
It is just a better architecture to log the error in the SSIS and keep processing (the objective of SSIS is to shovel data).
Then say a SQL Agent job can run and scoop statuses on how did the ETL do, if an error found send an email, alert, page a DBA, this is how I do for a client - e.g. severity, if a file was not found we open a ticket progrmmatically, if too few rows were processed it is a warning message, etc.
Arthur My Blog
Tuesday, December 11, 2012 3:55 PM
Just to be clear - I could just use SSIS to generate an Excel file, or a Text file (for now) and then also use SSIS to email the file?
So I don't need to use SSRS at all?
Tuesday, December 11, 2012 3:59 PMModerator
Yes, you can use the Excel Destination to produce such a report, but even this would not be very easy.
To email it you simply use the stock Send Mail Task set to consume at attachment, takes exactly 1 min to make this.
SSRS is an overkill for what you want to accomplish Chad.
Arthur My Blog
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Monday, December 17, 2012 8:23 AM