I heard recently that developers imprint on their first ever programming language. My actual computing career started with the Korn shell on AIX UNIX quickly followed by PERL and Java a while later. Maybe this is why I am constantly enthralled to have finally got KSH, PERL and Java all in one: PowerShell. When you read The UNIX Programming Environment by Kernighan and Pike, everything beautifully falls into place - so much elegance. Anyway, its the way I feel about PowerShell too.
One particular e-commerce solution we are building using Microsoft Commerce Server 2007 has a sizeable ETL sitting behind it. It ingests nearly 100 million rows of data (which will grow considerably in future phases) to compose a catalogue with many millions of products and variants. PowerShell is used to manage the transport/protocols in ensuring the multiple data feeds are ready for the ETL process. Microsoft SQL Server Integration Services then manages the process of preparing this data for importing into Commerce Server 2007, for which we have custom SSIS tasks.
It is one thing to have PowerShell running on the outside of SSIS, or even "shelled out to"; but how about being able to actually embed and write PowerShell directly inside SSIS? You'd want it to share the variables, write to the SSIS logger and be aware of executing in the context of SSIS. My colleague Richard Case has done exactly this, please browse here for his blog post and the custom SSIS PowerShell task. Give him some feedback.