Post reply

Unable to Pivot data in MS Excel for the (Output) file of SSIS package

  • November 11, 2024 at 11:59 am

    #4480049

    Good Morning,

    I have built an SSIS package which produces Excel File Output. In the course of the Run, the package itself makes a copy of the Template Excel file before populating it with the new data. One of the fields is called 'Count' (it is actually stored as a Varchar in the SQL Table).

    Convert to Number

    As a result, in MS Excel I am obliged to perform Convert to Number which will allow me to build a Pivot Table with the new data.

    Kindly advise, if there is a way to ensure that 'Count' is automatically a number in the Output file after the package runs. I have already tried these:

    1. Casting 'Count' in the T-SQL of the Stored Procedure (called within the package).
    2. Casting 'Count' within Derived Column and Data Conversion components (within the package). In Data Conversion I assigned 'Count' to type four-byte unsigned integer [DT_UI4], while in Derived Column I assigned it as follows:

    Derived Column Casting

    1. In the Excel Template I tried making the corresponding Column 'Count' of type Number.

    In spite of these efforts, the column 'Count' is still requiring manual Convert to Number for purposes of aggregation for Pivot table.

    Kind Regards

    • This topic was modified 5 months, 2 weeks ago by  Reh23.
    • This topic was modified 5 months, 2 weeks ago by  Reh23.
    • This topic was modified 5 months, 2 weeks ago by  Reh23.

  • November 11, 2024 at 1:08 pm

    #4480092

    It's been a while since I've had to do this, so I just created a dummy package to work it out again.

    Here are the steps that I took:

    1. In your template file, add a dummy row underneath your column headings. Put a zero (0) in all of the numeric columns of your dummy row.
    2. If you now look at Advanced Editor/Input and Output Properties for your Excel destination, you should see that the data type for the count column (under External Columns) has changed to Double Precision Float (previously would have been Unicode text)
    3. Use a Data Conversion component in your data flow to change the data type of the count column to Double Precision Float and map that new variable to your Excel target.

    That should do it. Of course, you'll need to exclude the dummy row from your pivot.

  • November 11, 2024 at 6:31 pm

    #4480219

    Thanks, Phil. Resolved !

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply