Display an Excel Sheet using SSIS

Question

Display an Excel Sheet using SSIS

happy55

Ten Centuries

Points: 1243
More actions
July 11, 2014 at 11:29 am

#296436

I have to schedule a job which query the database and generate an excel report and then email the report.
I thought of creating an SSIS package which will do all the steps .
I am using Visual studio premium 2012 and excel 2013 and Sql server 2012 .The excel configuration manager does not connect and it gives error
"Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
Failed to connect to the source using the connection manager 'Excel Connection Manager' "
I am not sure if Excel 2013 will work with SSIS package in Visual Studio 2012.Any help appreciated.
If the two are not compatible is there a different way of displaying an excel like generating an SSRS report and then calling the SSRS report from SSIS.
I have a lot of reports that I have to create this way so can anyone suggest which would be a better option using Excel configuration Manager or calling SSRS from SSIS?
Thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

OldJimmy SSC Veteran Points: 233 More actions · Answer 1

We find it infinitely easier to use SSRS for this. Just post the RDL to Report Manager and set up a subscription. That gives you the option of selecting the rendition you want (Excel, in this case).

happy55 Ten Centuries Points: 1243 More actions · Answer 2

Thanks for the reply .Any pointers for how can I achieve this ?

Call SSRS report i.e rdl file from SSIS ?

Thanks,

OldJimmy SSC Veteran Points: 233 More actions · Answer 3

Not sure I understand why SSIS has to be involved. If you have a stored procedure to develop your dataset, then you can create an SSRS report template to use that. When you copy the report template to Report Manager, you can select the item and select "Manage", then go to the "Subscriptions" option.

In that, you can list who to email the report to (To, CC, BCC), use the provided subject line or define one of your own, and then choose the output format of the report data (Excel, PDF, CSV, etc.).

You can then set up the schedule for the report to run when you desire. No SSIS processes are involved. Perhaps I have missed something in your inquiry that is causing me to give you information that does not "compute"?

happy55 Ten Centuries Points: 1243 More actions · Answer 4

You are correct here when it is a simple case of just getting the data and emailing the report SSRS works but there are jobs for which I have to do multiple steps like select data ,update table ,copy data and then generate reports so for such cases I will need SSIS reports.

Thanks

OldJimmy SSC Veteran Points: 233 More actions · Answer 5

Ok, understood. We are not yet at the same VS level as you so I cannot comment knowingly, but this sounds suspiciously like a 32-bit vs 64-bit conflict somewhere, perhaps. We can only choose Excel 2007 (latest) as an Excel connection. When we set the job up in SQL Agent, we have to go to execution options and choose the 32-bit option when we are exporting to (or importing from) Excel.

Not sure this is what your issue is, but something to consider. Sorry I can't help more.

happy55 Ten Centuries Points: 1243 More actions · Answer 6

Thanks or the help .Because of excel issues ,I was thing of a way of creating the excel file via SSRS i.e SSIS has a step to connect to SSRS and generate excel and place it in a location and then SSIS will send email.

Any one has any idea of connecting SSRS through SSIS.

I have read a script task can do the purpose but any pointers to what code to use will be helpful

Thanks,

sql Sarah SSCrazy Points: 2081 More actions · Answer 7

happy55 (7/11/2014)
You are correct here when it is a simple case of just getting the data and emailing the report SSRS works but there are jobs for which I have to do multiple steps like select data ,update table ,copy data and then generate reports so for such cases I will need SSIS reports.
Thanks

Could you use SSIS to do the steps to select data ,update table ,copy data, and then once that is done use SSRS separately for the report subscription?

For example, you could set up the SSIS job to run at 10 pm. It would process all the data - do the updating and copying. Then the SSRS subscription would run at 12 am. It would use that data to send the email with the Excel report attached.

happy55 Ten Centuries Points: 1243 More actions · Answer 8

Thanks for the suggestions .I have lots of reports to create and I think with this approach I will need two schedules for each report and anytime the job fails then also Check for two different jobs and keep a record when each one runs.

Any way I can connect to SSRS from SSIS package?

Like one step may be script task using C# to connect to SSRS ,generate the report and then email using SSIS.

I am not sure about what code to use to connect to SSRS.

Thanks,

sql Sarah SSCrazy Points: 2081 More actions · Answer 9

In SSIS, you could use the stored procedure sp_send_dbmail from msdb.

http://msdn.microsoft.com/en-us/library/ms190307(v=sql.105).aspx

There are 2 ways I have used it in an SSIS package.

1. Use sp_send_dbmail to run a query and attach the results without storing them.

Put an Exec SQL task in SSIS, the query runs sp_send_dbmail, and uses the parameters to run a query and attach the results. It does not allow Excel files, so the attachment extension would have to be .csv

http://thebakingdba.blogspot.com/2010/05/files-tricking-excel-with-spsenddbmail.html

2. Create the Excel file and use sp_send_dbmail to attach it.

Create a data flow task to create an excel file - with an OLE DB source and a flat file destination. Then create an Exec SQL task to run sp_send_dbmail and attach the file.

Hope this helps.

MMartin1 One Orange Chip Points: 27768 More actions · Answer 10

The way I read it your error is within SSIS. So let us see if you can use Excel as a dataflow destination. Right click your project > properties > debugging > Run64BitRuntime ... is this set to false at it should be? If it works here then in the job that you want to handle this package .. you have to have it run in 32 bit mode, as mentioned in a post above.

Given that, I still think that the ETL and reports rendering should be different steps. All ETL's data movements should be scheduled together in the same process if possible , for example -> 1 job with multiple steps if it is a once a day load.

----------------------------------------------------