Should I use SSRS or SSIS?

I have an Oracle query and I want to export the query results to an excel file daily. I've looked into both SSRS and SSIS and am not sure which would be better to use.

The query is a normal select that returns 10-20 fields. It is pretty straight forwa开发者_如何学JAVArd with a couple joins and where clauses. It selects DISTINCT to get rid of duplicate rows.

It's a straight mapping from the query to the excel file.

Does SSIS have performance advantages over SSRS?

I was leaning toward SSRS because it's very simple to set up and there are added benefits of being able to easily run our extract/report with different dates through the SSRS web UI.

SSIS seems like it will be more complex to set up, but still simple. However, it seems I would have to handle how to rename the extracts without using the main excel "template" so there are more steps involved. Also having issues getting parameters to work with Oracle queries.

Even though I am a big fan of SSIS, I would go with SSRS in this scenario.

1. Your requirement is that you simply need data in an Excel file. Though both SSIS and SSRS can do this task. SSRS has slight advantage in what you are trying to achieve.

2. You can format the Excel file in SSRS report however you would like to.

3. Similar to SSIS package that is easy to configure, SSRS also has easier development process. You can design and populate however you would like.

4. SSIS requires a SQL job to schedule it in order to run the package and then send you the Excel file or save it to some location. However in SSRS, you can simply create a subscription and export the Excel file to particular folder or send it to you in an email.

5. If you ever want to change the file export format, SSRS already does that for you.

Some of the points that I could think of.

This isn't a report, so don't use Reporting Services.

The SSIS package necessary for this is a single Data Flow task with two components: an "OLE DB Source" for your Oracle query, and an "Excel Destination". Draw a connection between the two components, configure them, press F5 and you're done.

Almost any property in SSIS can be set to the value of an expression. This includes the "ExcelFilePath" property of the Excel Connection Manager. Simply set that to an expression that appends the date to the file path, and you'll be set as long as you only run the package daily.

If you need to run it more than once a day, then simply precede the Data Flow task with a File System task to delete any previous version of the file.

Just tried this quickly myself and found one small issue. The data source I used included VARCHAR columns. The Excel Destination wanted Unicode, so I had to place a Data Conversion component between source and destination.

This link has a nice evaluation of the case you are presenting:

http://theruntime.com/blogs/gscarfone/archive/2009/07/15/data-dump-to-excel-through-ssis-and-ssrs.aspx

Basically it depends on your particular scenerio.

Hope it helps...