Problem using SSIS to output data from SQL query to Excel and updat charts of pivots. What's different about data going through SSIS?

Question

I have an Excel workbook with 3 worksheets. The first is aggregated data of login during timeframe and the other two are pivots to chart total CPU of login region for a timeframe and Total Duration of a login region in a timeframe.

I'm attempting to update all three worksheets but am having problem with the single row pivots. There is something different about the data coming through SSIS as it doesn't update the chart. If I copy and past the one row result from sql the chart updates and if I retype the data coming from SSIS and hit return or tab the chart updates.

The single row of data are sums in integer form. The SSIS source is an OLEDB Source with SQL command connected to an Excel destination. In the attached image I manually retyped the integers for VISN01 & VISN02. Notice the chart reflects that manual intervention.

How do I avoid this or correct this?

Answer 1

As noted in comment the issue is in the SSIS Dataflow task Excel Destination where numbers exported to Excel are being formatted as text and therefore unusable by chart. You must convert these cells to numbers, once you do that the chart looks very nice. Some say this is actually an Excel issue. Whatever: it's a Microsoft issue.

Since I'm using SSIS there is a script task where this can be done with C# which I pasted below. It converts numbers converted as text to numbers. In my case I have two worksheets with multiple columns. I loop on worksheet to operate on worksheets 2 & 3 and within that loop I loop on column index to operate on columns 1-21 I did have to add the Interop references noted in the Namespace using section. The only problem I have now is getting the Excel background task to quit. I see a lot of people encounter the problems I'm encountering and wonder why Microsoft doesn't fix them.

Now if you want to do this manual this web page shows a couple ways. I like the convert.

Hope this helps someone.

region Namespaces

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

endregion

    public void Main()
    {
        // TODO: Add your code here
            Excel.Application app = new Excel.Application();

            app.Visible = false;
            Excel.Workbook workbook = app.Workbooks.Open(@"D:\DevelopmentFolders\WhoIsActive_Temp.xlsx");

        for (int i = 2; i < 4; i++)
        {
                    Excel.Worksheet worksheet = workbook.Worksheets[i];
                    //adjust column index for your need
                    for (int ColumnIndex = 1; ColumnIndex < 22; ColumnIndex++)
                    {
                        worksheet.Columns[ColumnIndex].TextToColumns();
                        worksheet.Columns[ColumnIndex].NumberFormat = "0";
                    }
        }

        workbook.Save();
        workbook.Close();

        Dts.TaskResult = (int)ScriptResults.Success;
    }

Answer 2

Hi @Ash, Thomas E. (VISN5) ,

It seems that we should change the excel data to excel chart manually.

Please contact us if you have any questions about exporting data from sql server to excel.

Hope the following links will be helpful:

Create a Chart

Learn SQL: SQL Server export to Excel

Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.