I'm trying to find a way to get normalized data that's in an MSSQL database out of there, pivoted, and into an Excel file. The main problem is that I have many of those databases, each with the same schema but different data, that also leads to a different set of pivoted columns for each database.
Currently I use SQL Server Reporting Services for this task, but it just breaks down beyond 3000 rows (which isn't entirely unexpected as it's not meant for this task).
To explain my requirements let me set the scenario. I'm always exporting entities of -say- type "
Person". Each Person should become a row in the final export. I have about of those 250.000 records. This Person record has about 25 "flat" properties, and up to another 20-some pivoted properties. In its final form after pivoting I expect anywhere between 25 and 300 columns.
Basic example of main requirement
To visualize this with a basic, small example, suppose I have this data:
I need a tool or library to pivot that into this:
This is a small example but you probably get the point.
It's not a requirement (or a nice-to-have, tops) to have the first two rows seperate, it's merely in the example for clarity. They can be combined into one row (textually merging things, e.g. "Trait - Bold", etc).
The following things are my main requirements:
- Pivot as above, for about 250.000
Persons max, with about max 20 normalized
PropNames with each about 5 to 10
ValNames (counting "Other..." as one) and export into Excel.
- Pivot-columns not known until run-time. I have many DB's with the same schema but with different data in the columns that are to be pivoted.
- Should be live exports that run within seconds.
- Export to XLSX (slightly preferred) or XLS.
- Any solution in the .NET ecosystem would do, including non-gratis options.
- Any solution should be solid and maintainable, e.g. by being testable through integration tests.
I don't mind whether it's a ready-made tool or piece of software, or some software library that I have to wire myself in my own app.
The following are non-essential but would be a huge pre:
- Localization of things like column headers (e.g. "First Name") in the Excel.
- Being able to style (fonts, borders, backgrounds) the resulting Excel file.
- Being able to leave metadata (export date, etc) in the final Excel.
ValNames have ordering, which should result in ordering of the columns.
Things I've considered
Here's a short list of things I've considered or tried:
- Straight up SSRS, obviously. This tool's not up to the amount of pivoting/data involved.
- SSIS packages. The tool seems meant for the job. I do hold a grudge against the tool, but maybe it’s time to get over that. Main worry I have is that SSIS seems to want to know the data to pivot into columns before runtime.
- Dynamic SQL + generated RDLs. Use DynSQL to do the pivoting. This requires generated RDL files because the fields of a query must be known up front to SSRS.
- Dynamic SQL + OPENROWSET + OleDB. Use DynSQL to do the pivoting, and export it straight to Excel using OleDB.
- FOR XML queries into OpenXML. The basic idea: fast FOR XML queries, possibly an XSLT, generating OpenXML data, and plug it in a basic XLSX.
- ORM or ADO.NET into an OpenXML using an XmlWriter. Something along these lines.
- BCP directly into Excel files. Haven’t looked into this yet, but it may be an option.
- SQL CLR. Not sure how this would work (if at all), but there might be options here.
My main question and bottom line is: what would you recommend?