0

Here's the James May method. Looking for feedback & ways to do it smarter.

1. Write your query in SSMS. To get dynamic date ranges that always fall to the first of the month, I do it this way (there is almost certainly an easier way):

    declare 
    @start date,
    @end date

    set @start = cast(datepart(year, dateadd(month, -1, getdate())) as char(4)) + '-' 
    + cast(datepart(month,(dateadd(month, -1, getdate()))) as varchar(2)) + '-01'
    print @start
    set @end = cast(datepart(year, current_timestamp) as char(4)) + '-' 
    + cast(datepart(month,(dateadd(month, 0, getdate()))) as varchar(2)) + '-01'
    print @end;

Then, you can write a query like this, and it will always get last month's data:

    select * from AdmVisits
    where ServiceDateTime between @start and @end

2. Create your excel file: Paste the results into an excel sheet. Format it the way you want it, Create your pivot tables and charts 'n stuff, then delete all the rows with data (keep the headers).

Important: Save the excel file in a location where your SQL Server Agent can access the file. You may need to edit the security properties of the windows folder and add SQL's service account to the Group or User Names.

I keep a template file in a place where no end users can open it up, change a column and break everything. It adds a few steps, but makes it a bit more end-user-proof. In this example, my template is:

    D:\Data\Admits_blank.xlsx

Before we go to the next step, save a copy of the file that will be re-created each month, get filled with data then renamed. SQL needs to see it in the wizard, or it will argue with you. This is also the folder that your end-users will need permissions to access. In my example, this file is:

     \\intranet\Reports\Admits\Admits.xlsx

3. Set up a .dtsx package: You can do it directly in SSIS, but I find it easier to use the SQL Server Import and Export Wizard by right click on the database, hitting Tasks and Export Data.

Set the Data source to by SQL Server Native Client, set up your server and database, then hit Next.

Set the Destination to be Microsoft Excel, and the excel file path to your shared folder (not your blank template):

     \\intranet\Reports\Admits\Admits.xlsx

Hit next and you should be on the page to Specify Table Copy or Query. In this example, hit the button to Write a query to specify the data to transfer, but you can also create a view or table, if you prefer.

On the Provide a Source Query page of the wizard, paste all your sql. In this example, I need to paste:

    declare 
    @start date,
    @end date

    set @start = cast(datepart(year, dateadd(month, -1, getdate())) as char(4)) + '-' 
    + cast(datepart(month,(dateadd(month, -1, getdate()))) as varchar(2)) + '-01'
    print @start
    set @end = cast(datepart(year, current_timestamp) as char(4)) + '-' 
    + cast(datepart(month,(dateadd(month, 0, getdate()))) as varchar(2)) + '-01'
    print @end;

    select * from AdmVisits
    where ServiceDateTime between @start and @end

On the next page, change the destination to Sheet1$ (or whatever you named the sheet).

On the Review Data Type and Mapping page, you can change the data types if you want, so that dates and integers come out properly.

On the Save and Run Package page, make sure you Save SSIS package to the File System. I used:

    D:\Data

Finish the export wizard, and you should be able to go to your shared folder and see your file, full of data. We haven't used the _blank one yet.

4. Create the Job in SSMS: Go down to SQL server agent, right click on jobs and create new job. Give it a name, and specify an owner that isn't you (your SQL server service account for example).

Go to steps and add a new step. First thing we are going to do is copy our _blank template into the shared folder.

Step 1 for me will be:

    xp_cmdshell 'copy D:\Data\Admits_blank.xlsx \\intranet\Reports\Admits\Admits.xlsx';

Step 2 will be the .dtsx package. Change the step type to be SQL Server Integration Services Package, and at the bottom, specify the path for the package, which we saved at

    D:\Data

Step 3: Rename the excel file with your month:

    declare @date varchar(25),
    @sql varchar(1000)

    set @date = cast(datepart(month, current_timestamp) as varchar(2)) + '_' +  cast(datepart(day, current_timestamp) as varchar(2)) + '_' +  cast(datepart(year, current_timestamp) as varchar(4))
    print @date

    set @sql = 'copy \\intranet\Reports\Admits\Admits.xlsx \\\intranet\Reports\Admits\Admits_' + @date + '.xlsx';
    print @sql

    exec xp_cmdshell @sql

Step 4: Delete the no-date copy of the file on the shared folder:

    xp_cmdshell 'del \\intranet\Reports\Admits\Admits.xlsx';

Step 5: Send out an email telling your user where to get the file:

    declare @date varchar(25),
    @sql varchar(1000),
    @body varchar(255),
    @subject varchar(255)

    set @date = cast(datepart(month, current_timestamp) as varchar(2)) + '_' + cast(datepart(day, current_timestamp) as varchar(2)) + '_' +  cast(datepart(year, current_timestamp) as varchar(4))

    set @subject = 'Admissions ' + @date
    set @body = 'The Admissions Monthly file can be found at:

    \\ehsintra3\Meditech\HART\Admits\ '

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'NoReply',
    @recipients = 'nunya@business.org; ',
    @copy_recipients = 'ReportCommittie@business.org',
    @subject = @subject,
    @execute_query_database = 'Master',
    @body = @body,
    @body_format = 'html'

In the end, it looks like this:

enter image description here

Finally, schedule the job, then test it by right clicking the job and hitting start job at step.

In a few moments, you should see an email that looks something like this:

enter image description here

When you need to modify the package, fire up SSIS, open up the file and bash it around a bit.

If it's stupid and it works, sometimes its still stupid.

Question: How would you do this smarter?

  • 1
    Have you considered linking the excel to SQL directly so users could use the refresh button to get the most up to date data? Its been a while but you should also be able to let them use a cell's value if you wanted to pass a date as a parameter to a SP to get a specific month's data. That'd remove the majority if your steps, assuming you have a reasonable DB copy to run these against. – LowlyDBA Mar 19 '18 at 20:42
  • interesting, i'l give that a try. – James Mar 20 '18 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.