November 11, 2024 at 6:40 pm
Good afternoon,
I have an SSIS package which loads raw records into a sheet of an Excel file. I now need a second sheet with a Pivot (for these raw records) where the columns are of Months. But obviously with time, the list of months will continuously grow. My understanding is that for purposes of the Pivot component in SSIS, or T-SQL you must have in-hand the list of column names in order to build the Pivot, correct ?
Rather than having to manually pivot the raw records in MS Excel I was hoping that SSIS package would be to build the entire Pivot automatically and place it into a new second sheet in the Excel file.
Kind advise
November 11, 2024 at 9:58 pm
You could build the pivot dynamically in T-SQL (sample link), but getting that out of SSIS and into Excel is extremely tricky, because SSIS likes to know its columns at design time. So I suggest you don't even try, unless you are prepared to push the data as a single column into a CSV file instead and let Excel parse out the columns itself when it opens the file.
I don't know whether this is possible, but perhaps you could build the pivot into your Excel template in such a way that opening it and doing a Refresh is enough to rebuild the pivot with the new data.
If you can get that to work, but really want to automate the refresh as well, it's possible, but could require an installation of Excel on the server alongside SSIS. In fact, automating the creation of the pivot from scratch is also probably possible if you have Excel installed.
Note, however, that installing Excel on a SQL/SSIS server is generally considered bad practice.
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
November 11, 2024 at 11:16 pm
Where's the data coming from?
I'm basically terrible at SSIS (but Phil's good at it, so listen to him), but you can pivot data in Excel using PowerQuery and then you could connect to that pivoted or unpivoted data sheet using SSIS and import it. You can just select the columns you want to Pivot (or not) and select Unpivot Columns or Unpivot Other Columns, and you're finished. =) Now if that would just play nice with SSIS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply