SSIS Pivot Transformation - UI Needed

SSIS Pivot Transformation - UI Needed by BI Monkey

Closed

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	632051
Opened:	12/16/2010 3:26:32 PM
Access Restriction:	Public

Workaround(s)

view

This transformation is extremely difficult to work with, even for seasoned users - who tend to fall back on the T-SQL Pivot to avoid this component.

The requirement to build the pivot using various different areas of the advanced editor makes it particularly confusing.

Details (expand)

English

Integration Services (DTS)

It needs either a custom UI or if not possible, at least a Wizard to make this a usable component.

Improved User Interface

0 attachments

Food for thought:

* Need to manually write down greyed-out lineage ids and entering them elsewhere
* Need to look up documentation on PivotUsage the hard way (F1 will NOT lead there directly, and worse, someone bothered setting the Property's description ("Describes how...") and missed the opportunity to deposit the value info there)
* Forgetting to set a PivotUsage for an input that's going to be pivoted will produce no errors or warnings but deliver unpredictable data (I have an example where the result was reliably that ov dividing the pivot key value given by two).
* Trying to set ErrorRowDisposition on an integer Key Column to RD_RedirectRow (self-evident idea: "put those rows I forgot to key in a separate bucket") - well, fails. Proving that the programmer that implemented the component must not only have been explicitly forbidden from providing an UI, but also from testing it.

Overall it must be the case that marketing insisted on being able to feature the Pivot keyword in SSIS ads, but no-one ever expected a developer would actually try to use it.

Sorry for the jibe, I *am* purposefully exaggerating to point out that those few devs out there that ever tried to create something productive using this component can hardly help but feel insulted. It's fitting that it has been seven years now that it's over. Otherwise, I *am* able to be glad the component exists at all, and I *do* see that it performs admirably after getting over the initial shock.