December 20, 2016 at 10:58 pm
Hi Experts,
I have a job which runs an SSIS package to fetch data from multiple tables in a database and then put it in an .csv file and then mails it to receipients.
Is there any software available which sits outside of SQL Server runs the query and put it in excel and send mail?
We want to move all these reports out of SQL Server agent . This is to avoid load and to keep Database server for that purpose alone and the output .csv file we get now is not properly formatted.
TIA
December 20, 2016 at 11:22 pm
VastSQL (12/20/2016)
Hi Experts,I have a job which runs an SSIS package to fetch data from multiple tables in a database and then put it in an .csv file and then mails it to receipients.
Is there any software available which sits outside of SQL Server runs the query and put it in excel and send mail?
We want to move all these reports out of SQL Server agent . This is to avoid load and to keep Database server for that purpose alone and the output .csv file we get now is not properly formatted.
TIA
There are multiple ways of doing this, SSIS is one of the simplest, in order to avoid excessive load on the database server, you will have to move the execution to another server, otherwise you will have gained absolutely nothing, regardless of what substitution for the SQL Server Agent will be used.
😎
The improper formatting of the CSV is a separate matter, simply fix the SSIS package.
December 21, 2016 at 12:07 am
You can use Windows Scheduler to schdule your SSIS package execution.
Another tool which can call SSIS package is Control-M .
I had used Windows scheduler as well as COntrol-M to run some SQL scripts but you should be able to specify the path of DTSEXEC.exe and the package path in command line and call that using both of these methods.
December 21, 2016 at 12:54 am
VastSQL (12/20/2016)
Hi Experts,I have a job which runs an SSIS package to fetch data from multiple tables in a database and then put it in an .csv file and then mails it to receipients.
Is there any software available which sits outside of SQL Server runs the query and put it in excel and send mail?
We want to move all these reports out of SQL Server agent . This is to avoid load and to keep Database server for that purpose alone and the output .csv file we get now is not properly formatted.
TIA
If you move the jobs out of the instance you will introduce other problems into your scenario such as increased latency and response times.
The greatest part of the load you incur is in the fetching of the data from multiple tables and that will occur in the database regardless of what else you do. Packing it into a CSV file and Mailing it is negligible in terms of load.
An improvement you could make involves where you store your SSIS Packages or where you host your SSIS Catalog but then you would Need to consider the effect and cost of additional licensing.
My advice would be to leave it exactly where it is. The SQL Server Agent consumes resources outside of the instance (it has ist own Windows Service) so the instance itself will not be affected. If you are experiencing resource starvation during the running of this job then consider whether the query gathering the data should be rewritten.
Either way, running the jobs outside of the instance will not bring any tangible benefits and introduces a complexity that will make your job as an Administrator just that little bit more uncomfortable.
December 21, 2016 at 8:38 am
Just to point out one thing, csv are not excel documents Excel just happens to be able to open them 🙂
There are any number of tools that can call SQL commands or generate csv files from SQL Server but SQL Agent has specific functionality designed to run SSIS packages and has built in logic for error handling etc... is there a compelling reason you don't want to use SQL Agent?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply