Create reports using SSIS - best practice?

Question

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.

Thanks

Answer 1

I will suggest you create tables and log your errors in there and use ssrs to report.

Answer 2

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

Answer 3

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?

Thanks

Answer 4

If you want to output to different format easily, the best way is to do it with SSRS in SSIS.

See: http://randypaulo.wordpress.com/2011/10/27/ssrs-calling-reporting-services-using-wcf-and-using-the-html-output-for-email-notification/

---

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

Answer 5

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.

Answer 6

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.

See http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

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

Answer 7

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?

Answer 8

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