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

• Reh23

  Old Hand

  Points: 344

  More actions

  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).

  

  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:

  

  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 3 weeks, 5 days ago by  Reh23.
  • This topic was modified 3 weeks, 5 days ago by  Reh23.
  • This topic was modified 3 weeks, 5 days ago by  Reh23.

• Phil Parkin

  SSC Guru

  Points: 246970

  More actions

  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.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Reh23

  Old Hand

  Points: 344

  More actions

  November 11, 2024 at 6:31 pm

  #4480219

  Thanks, Phil. Resolved !