I just downloaded and tried the CalendarTransform component for SSIS that Microsoft released with a shared source permissive license.

It’s a useful data flow transformation component that is able to generate apparently all of the attributes you can desire to place into a date dimension. While I think it’s a pretty useful component (and you have source code that can be improved!), I’d like to highlight areas of improvement:

  • Editor can be more user friendly: you have to go into “Input and Output Properties” and define what output columns have to be generated.
  • Add a preview of output, better if provided with sample data: a sample talks more than thousands of words, and in this case you have only one word (the enum member name…).
  • It’s not clear how to force locale id from SSIS editor: I correctly get Italian names for calendar with my default configuration, but order of items in a complete date string is the American one, very strange… Anyway the biggest problem is that you probably want to enforce a localization identifier independently by execution condition, so for a SSIS user it could be not so intuitive as it is for a veteran .NET programmer.
  • A little legenda for Format property (who remember differences between MMM, MM and M?): actually is very difficult to get appropriate documentation for date format even from .NET documentation.
  • A DateGenerator data source would be very useful: I know it’s very easy to write one, but for those very basic features (in a data warehouse environment) it could be better to rely on “standard” components.

This release (together with UnDoubleOut – a quote / double quote transformation helper), suggest me that it would be great to have a common repository for SSIS components, particularly for dataflow components. A simple one I’d like to have is a “dimension lookup” that internally does the work of Lookup component placing a dummy value if the searched application key is not found. I haven’t tried it is possible to build it deriving the Lookup component (and if it would work, how to handle the editor user interface?), the problem is that in my understanding it isn’t possible to create a dataflow component that aggregates several other dataflow components. Feedbacks on that are welcome!