Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Refresh Excel Pivot Tables Automatically Using SSIS Script Task


By:   |   Last Updated: 2019-02-26   |   Comments (1)   |   Related Tips: More > Integration Services Development

Problem

I have a process that exports data from SQL Server to an Excel file.  The Excel file also contains a Pivot Table that I want to auto refresh whenever the Excel file is created from the SQL Server Integration Services (SSIS) package.  How can I refresh Pivot Tables within an Excel file using a SSIS package?

Solution

When we export data from SQL Server to Excel file by using an SSIS package or directly copying the result set from SQL Server Management Studio (SSMS), Pivot Tables within Excel do not automatically update. In this tip we will walk through how this can be done.

Create Test Data

To demonstrate this and show how this can be done, we will create a table named "tblEmployees" in a demo database by executing the following query.

CREATE TABLE [dbo].[tblEmployees]
  (
     [employeename] [VARCHAR](max) NULL,
     [birthdate]    [DATETIME] NULL,
     [jobtitle]     [VARCHAR](150) NULL,
     [emailid]      [VARCHAR](100) NULL,
     [phonenumber]  [VARCHAR](20) NULL,
     [hiredate]     [DATETIME] NULL,
     [departmentid] [INT] NULL
  )

Execute the following script to dump test data in "tblEmployee" table.  This data comes from the AdventureWorks database, but you can use any data source you want.

INSERT INTO tblEmployees
            (EMPLOYEENAME,
             JOBTITLE,
             EMAILID,
             PHONENUMBER)
SELECT ISNULL(FIRSTNAME, '') + ' '
       + ISNULL(MIDDLENAME, '') + ' '
       + ISNULL(LASTNAME, '')AS EMPLOYEENAME,
       JOBTITLE,
       EMAILADDRESS,
       PHONENUMBER
FROM   HUMANRESOURCES.VEMPLOYEE
WHERE  JOBTITLE IN ( 'NETWORK MANAGER', 
                     'NETWORK ADMINISTRATOR',
                     'APPLICATION SPECIALIST',
                     'DATABASE ADMINISTRATOR' )  

I then created an Excel file to store the details of employees (Sheet1). I also created a Pivot Table (Sheet3) which shows total employees by department.

To export the data from SQL Server to Excel, I created an SSIS package which performs the following tasks:

  1. Copy and replace the empty Excel file from the source directory (E:\EmptyFile) to the destination directory (E:\UpdatedFile) using a File System Task.
  2. Export the query output from SQL Server to Excel to the destination directory (E:\UpdatedFile) using a Data Flow Task.
  3. Refresh the Pivot Tables.

Configure the SQL Server Integration Services File System Task

To configure File System Task, drag and drop a File System Task from the SSIS Toolbox. Double click on the File System Task and a dialog box "File System Task Editor" opens as shown in the following image:

SSIS File System Task Editor

I copied the empty Excel file with the two worksheets I mentioned above into the "E:\EmptyFile" directory. In the File System Task editor double click the Source Connection and select "New Connection." A dialog box opens and in the Usage Type drop down box select "Existing file" and in File text box, provide the full path of the empty Excel file and click OK to close the dialog box.

SSIS File Connection Manager Editor

Once the source connection is configured, configure the destination connection. In the File Connection Manager Editor, provide the path of the destination directory. See the following image:

SSIS File Connection Manager Editor File Path

Configure the SQL Server Integration Services Data Flow Task

Drag a Data Flow Task from the SSIS toolbox and drop it into the Control Flow window. See the following image:

SSIS Data Flow Task

Here is a summary of the steps of how to configure the SSIS package.

Step 1 – SQL Server Query to Export Data

We want to export the output of a SQL query to an Excel file, so copy the following query into the SQL command text box of the ADO.NET source editor.

select EmployeeName,Birthdate,JobTitle,EmailID,PhoneNumber,HireDate,DepartmentID from tblEmployees

See the following image:

SSIS SQL Server Query to Export Data

Step 2 – Specify Excel File Path

We are going to use the Excel file which is copied to "E:\UpdatedFile" directory, so in the Excel destination manager, enter "E:\UpdatedFile\Refresh_Pivot.xlsm" as shown below.

SSIS Specify Excel File Path

In the Excel destination editor dialog box, select the Excel sheet where the query output will be stored. For this demo, the query output will be stored in Sheet 1, so select "Sheet1$" for the Name of the Excel sheet drop down box. See the following image:

SSIS Excel Destination Editor - specify the Name of the Excel Sheet

Once the Data Flow Task is configured, it should look like following image:

Completed SSIS Data Flow Task

Once the Data Flow Task is configured, join the File System Task and the Data Flow Task. See the following image:

Join the File System Task and Data flow task in SSIS

Once the SSIS package is ready, execute it. Once execution completes successfully, open the Excel file and you should see the data as follows.

Refreshed Excel File

If we look at Sheet3, we can see the Pivot Table, but there is no data.

Excel Pivot Table

As you can see the output of the query is exported correctly, but the Pivot Table is not refreshed.

To fix this issue, we must:

  1. Create a VBA module/macro to refresh the Pivot Table in the Excel file that we created in the source directory (E:\EmptyFile).
  2. Execute the macro using a SSIS Script Task.

Create Excel Macro Module

We can create a macro by using Microsoft Visual Basic for Applications. To open it, right click on Sheet1 of and select View Code. Alternatively, you open it by using the shortcut key Alt + F1. See the following image:

View Code in Excel

In the left pane, right click on VBAProject and select Insert > Module. See the following image:

Configure the Excel module

In the code window of the module, enter the following code:

Public Sub RefreshAllPivots()
Dim Pivot_Table As PivotTable
Dim Work_Sheet As Worksheet
    For Each Work_Sheet In ThisWorkbook.Worksheets
        For Each Pivot_Table In Work_Sheet.Pivot_Table
          Pivot_Table.RefreshTable
        Next Pivot_Table
    Next Work_Sheet
End Sub

The above code creates a function named "RefreshAllPivots()." It iterates through the entire worksheet and if it finds any Pivot Table, it refreshes it. See the following image:

Function named RefreshAllPivots()

Save the code and close the "Microsoft Visual Basic for Applications".

Configure SSIS Script Task to Refresh Excel Pivot Tables

Once the Excel macro is created, let's configure the SSIS Script Task. The Script Task uses Visual Studio Tools for application as the development environment. First, open the SSIS package. In the Control Flow window, drag and drop a Script Task from the SSIS toolbox. See the following image:

Configure the SSIS Script Task

Now, double click on Script Task and a dialog box "Script Task Editor" opens. In the dialog box click on the "Edit Script" button. See the following image:

On the SSIS Script Task Editor interface, click the Edit Script button

When you click on "Edit Script" button, it creates a VSTA project. VSTA project contains a single ScriptMain.cs file. We can write our logic in the ScriptMain.cs file.

We created a macro in the Excel file, so to execute it we need to write some more code.  First, we must import Microsoft Office Interop ExcelDLL, so we can control actions in the Excel file. This DLL can be downloaded from here. Once you download it, add the DLL as a project reference. To do that, in Solution Explorer right click on References and select Add Reference. In the Reference Manager, provide the path of the downloaded DLL file. See the following image:

Browse the file system to locate the DLL file

To refresh the Pivot Table, we must execute the Excel macro. To do that write the following code in the ScriptMain.cs file:

string filename = "E:\\UpdatedFile\\Refresh_Pivot.xlsm";
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); 
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filename); 
xlApp.Visible = false; 
xlApp.Run("RefreshAllPivots"); 
xlWorkBook.Close(true); 
xlApp.Quit();
Dts.TaskResult = (int)ScriptResults.Success;

The above code performs the following tasks:

  1. Creates an object of Microsoft Excel application.
  2. Opens the "Refresh_Pivot.xlsm" file created in the destination directory (E:\UpdatedFile).
  3. Runs the macro named "RefreshAllPivots" within the Excel file.
  4. Closes the Excel workbook and destroys the Excel object.

Following is a snapshot of Visual Studio code editor:

Snapshot of Visual Studio code editor

Save the code and close the Visual Studio Code Editor.

Update the SSIS Package

Now on the Control Flow tab connect the Data Flow Task and Script Task as follows:

In SSIS connect the Data Flow Task and Script Task

Now run the package. Once the package executes successfully, open the Excel file "refresh_pivot.xlsm" located in the destination directory (E:\UpdatedFiles) and we can see the following in Sheet1.

Updated Excel worksheet

If we go to Sheet3, we can see the updated Pivot Table as follows. As you can see, the Pivot Table is refreshed correctly.

Updated Excel Pivot Table

Next Steps

Check out the following tips and articles which might help understanding what was done in this article:



Last Updated: 2019-02-26


get scripts

next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 11, 2019 - 11:18:26 AM - Jiyang Back To Top

Fantastic job Nisarg! It is a really thoughtful walk through tutorial which covers all the details.


Learn more about SQL Server tools