Refresh Excel Pivot Tables Automatically Using SSIS Script Task

By: Nisarg Upadhyay   |   Comments (5)   |   Related: 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:

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.

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:

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:

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:

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.

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:

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

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

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

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

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:

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

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:

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:

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:

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:

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:

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:

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.

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

Next Steps

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

• Export data from SQL Server to Excel file
• The SSIS File System Task
• SSIS C# Script task
• SQL Server Integration Services Tutorial

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips