- Writing a provider is hard or at least harder than writing a cmdlet. Haven written a few cmdlets myself, its really not that much more code than writing a classic console (exe) type application. If you can write a console application, you can write a cmdlet.
- I have a new found respect for those who have written providers including the SQL Server 2008 provider. This is some advanced programming stuff and requires a lot more thought than writing a cmdlet. Although I feel a few dozen cmdlets for SQL Server is more useful than a provider interface, my hat is off to you, Michiel Wories, for creating the SQL Server 2008 Powershell provider.
- The more I use ManagedDts, the more I appreciate SQL Server Management Server (SMO). SMO is elegant, well-laid out and after a bit of a learning curve generally makes sense. ManagedDts on the other hand is missing core functionality which is exposed through Microsoft's own SQL Server Management Studio (SSMS), Dtexec or BIDS. As someone who automate things through scripts, I find the limited support for SSIS administration scripting disappointing.
<Folder xsi:type="SqlServerFolder">
<Name>Z002_SQLEXPRESS</Name>
<ServerName>Z002\SQLEXPRESS</ServerName>
</Folder>
And finally we will enumerate the folders and packages using the GetPackgeInfos method.
$app.GetPackageInfos("\",'Z002\SQLExpress',$null,$null)
The following information is returned:
Problem #1: The call to GetPackgeInfos requires the SQL instance name i.e. Z002\SQLExpress while the equivalent SSMS SSIS connection you specify just the server name i.e. Z002
Next let's load an SSIS package called test stored on the root of the package store. Note this does not execute the package this just loads the package into a variable, you can then execute using the execute method.
$package = $app.LoadFromDtsServer("\Z002_SQLEXPRESS\test",'Z002',$null)
Problem #2: The call to LoadFromDtsServer requires the top level folder as defined in MsDtsSrvr.ini.xml while the equivalent SSMS SSIS Object Explorer just presents it. Furthermore there are no methods in ManagedDts to return the top level folder. So, basically we have GUI, SSMS that includes functionality not available in the API one would use to automate SSIS administration
Here's what I'd like to be able to do in an SSIS provider, but can't because the API does not expose the needed functionality:
cd SSIS:\
SSIS:\>
cd Z002
Get top level folder and the
SSIS:\Z002\> get-childitem
There are still some useful things to scripting SSIS administration activities. I recently worked with a DBA to move dozens of SSIS packages and the folder structure from one SQL Server to another. By using a Powershell script over a GUI approach this saved him a few hours of work. Here's an example of the script we used which is part of the LibrarySSIS functions in SQLPSX:
copy-isitemsqltosql -path '\' -topLevelFolder 'Z002_SQL1' -serverName 'Z002\SQL1' -destination 'Z003_SQL2' -destinationServer 'Z003' -recurse $true
I created a Connect item requesting functionality in Microsoft.SqlServer.Dts.Runtime to support discovering the top level folder and using the server name instead of the instance name. Both of these items are already in SSMS. Please vote on my connect item.
Related Posts:
*Note: Professional Windows Powershell Programming is a book geared for developers to create C# Powershell cmdlets and Providers. This is the best book on the subject. It's also the only book on creating snapins, cmdlets and providers. Because of the topic of the book I only recommend it to those who want to write C# cmdlets and providers. Most Powershell users will not have a need to do so and instead will simply use the built-in or 3rd party cmdlets and providers.
