Automated Email Reports with SQL Agent Job & SSIS Packages
THE PROBLEM
Send a daily excel file to a vendor via email. The file contains 10,000 rows and is too large to generate directly using SQL Server Agent Job.
Want to know more about the process? Find out more after the Solution Section below.
THE SOLUTION
We are going create an SSIS Package using SQL Server Import and Export Wizard. Then we will use a SQL Server Agent Job in SSMS to schedule and run the package.
Step One- Using Import and Export Data Wizard
- Go to Start > Microsoft SQL Server Your Version > Import and Export Data
- Choose your data source; in this case SQL Server Native Client 10.0.
- Enter your Server Name and set Authentication. For this example we are using Windows Authentication.
- Choose your target database and click Next.
5. Choose your destination output file. In this case, Destination is Microsoft Excel.
6. Set your Excel file path and Excel version. Click Next.
7. I needed specific data, so I selected Write a query to specify the data to transfer. Click Next.
8. Write your query. Click Parse to be sure that it runs, and click Next
9. Click Next again on ‘Select Source Tables and Views’ page. You can preview your results here too.
10. The wizard will identify if there are any conversion issues in your data. Be sure to address any problems.
11. Finally, on the next screen, check Save SSIS Package. I saved mine to a specific folder so I chose File System. You can set a package protection level if needed. Click Next.
12. Give your package a name and click Next
13. Finally, click Finish. Your package will execute and when it is successful click Close.
Step Two — Creating your SSMS SQL Agent Job
- In SSMS in the Object Explorer pane, right click SQL Server Agent. Then select New > Job.
- The Job Window opens up. Give your job a name.
2. Click Steps in the left pane and then click New on the bottom of the window.
3. Enter a Step Name and change the Type to Sql Server Integration Services Package. Select the Package Source (in our example it is stored in the File System) and select the package we just created. Click OK.
If you are using 64 bit Windows you may want to go to Execution Options and select Use 32 bit runtime.
4. Now we need to Email the package data. Add a new step and give it a name. This type our type is Transact-SQL script (T-SQL). Choose the target database. Use the send_dbmail command as in the example below.
I took the liberty of highlighting all the values you can change as needed.
Tip : If you have forgotten what your excel file is called, open your data package. It will open in the Execute Package Utility. Go to Connection Managers on the left pane. Your destination file name will be in the Connection String of the DestinationConnectionExcel row.
5. From this point the job will work! However if the account you’re using to run the job does not have privileges to edit the Excel file, you will encounter the following error: Executing the query “CREATE TABLE `Query` (…” failed with the following error: “Table ‘Query’ already exists.”
To get around this, either grant the account permissions to edit the file or delete the file after every run. For some reason the file could not be overwritten so deleting it and recreating it seemed to be the safest option.
6. To delete the file I used a PowerShell script. Create a new step in the Agent Job. Select the Type as PowerShell and Run As SQL Server Agent Service Account.
In the Command window, type the following
Remove-Item –path “yourfilepath.xls”
This step should be run last, after the Email Step is run.
Set the Schedule
The last step is to set the job schedule.
- In the Job Properties window of the SQL Agent Job, select Schedules. Click New at the bottom of the window.
- Give your job a name and select your parameters. Click OK and you’re all set!
THE BACKGROUND
A vendor required that we send them a daily Excel/CSV report via email. For months our staff ran the report manually from our in-house software application and formatted in in Excel, before sending it off to the vendor.
One can imagine how cumbersome this could be, especially with limited human resources. Automation incoming!
I used a SQL Server Agent job to complete this task… the main problem was that the generated file contained 10,000 rows and was too large to send via email!
The file was large because of the encoding that SQL Agent uses to create the Excel file. So to mitigate this, I created a SSIS package and used the SQL Agent to run the package. The package generated a much smaller file, with better formatting.
