Brandie Tarvin (5/31/2012)
Is SSIS integrated with Powershell?
You can use PowerShell from within SSIS using this[/url] (works well). Or you can call an SSIS package from PowerShell by invoking DTExec (no integration of app domains if done this way). If you need app domain integration from PowerShell you can do it but I have not known the need for such heavy integration in that direction. If you needed it though, since you can create new or manipulate and invoke existing SSIS packages in .NET it means you can do the same using PowerShell, see here for details.
Both SSIS and PowerShell are managed language environments built atop .NET. PowerShell is a scripting language layer built atop of .NET. If you're familiar with Perl you know it is a layer built atop C, same idea. Do you need to know .NET to use PowerShell, no, not at all. Same with SSIS. Do you need to know .NET to create an SSIS package, not at all, but it's right there if you need it in the form of Script Tasks and Script Components. You can create very powerful SSIS and PowerShell solutions without knowing one lick of .NET, but it's right there behind the scenes if you need it.
SMO are .NET assemblies so it is no accident that PowerShell scripts that leverage SMO are easy to develop. The existence and enhancement of the managed providers Steve referenced for other MS platforms (e.g. Windows, SharePoint, Exchange, obviously SQL Server) show that MS is backing the technology in a strategic way. PowerShell and Sql Server Central Management Server is also a very powerful combination.
From PowerShell we can conveniently access handy things like WMI, Web Services, Event Logs, T-SQL, AD...pretty much anything proprietary in a Microsoft environment as well as anything standard in a mixed environment (e.g. DNS, NNTP, SMTP, etc.). These services be accessed using generic .NET methods either directly using the .NET base-class libraries or from nicely-packaged built-in PowerShell CmdLets (e.g. Get-WmiObject). Can we manage multiple Windows servers running SQL Server from a T-SQL context, all from a single SQL Server? Sure, if we try hard enough, but it is not as easy as doing the same using PowerShell, I have done both. As a proponent I think anyone with more than a mere few instances to manage would be missing the boat if they ignored PowerShell for system and database administration.
PowerShell has been out for over 5 years and it is clear MS is backing the technology.
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato