I’ve taken a few hours to try out Denali CTP 3 sqlps and noticed some welcome changes.  The biggest change for sqlps is that it has been implemented as module and plain old Powershell host–It’s no longer mini-shell!

SQLPS Host

SQLPS is now regular Powershell host implemented as the familiar sqlps.exe. Prior versions of sqlps were a  mini-shell, which is to say a Powershell host that implements RunsapceConfiguration with explicitly defined cmdlets and no support for add-pssnapin. The old implementation was limiting in that you couldn’t add cmdlets or providers. I’ve previously written about the SQL Server 2008 and SQL 2008 R2 sqlps  so I won’t spend much time on it here, but I will say that I really like what SQL Server product team has done with the Denali version of sqlps.

There some 40 new cmdlets and 2 new “providers” over what was provided in SQL Server 2008 R2. Note: sqlps uses something called SqlServerProviderExtensions which are not like regular providers in that you can’t load them individually—so there’s really only a single “SQLServer” provider. Its interesting to see how the SQL Server product team has organized their provider so that they easily plug in these extensions. As far as I know this is unique among provider implementations.

Modules

Denali Powershell implementation also includes two modules, SQLASCMDLETS and to make things a little confusing there’s binary module called SQLPS which has the same name as the sqlps.exe host. Both modules are located under:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules

Cmdlets

get-command -CommandType cmdlet -Module sqlps,sqlascmdlets | group-object -Property verb

Providers

PS SQLSERVER:\> dir

IntegrationServices and SQLAS are new to Denali.

Using SQLPS

Just as in previous versions you launch the sqlps.exe host by right-clicking an object in SQL Server Management Studio Object Explorer and selecting “Start Powershell” or by typing sqlps.exe from the Run or command-prompt.

Alternatively if you want to load the SQLPS module in your powershell.exe host then you can run:

$env:PSModulePath = $env:PSModulePath + ";C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules"
import-module sqlps

Since I’ve implemented my own backup and restore functions I thought I’d test the Backup-SqlDatabase cmdlet:

The cmdlet seems to be fully functional and even implements a nice write-progress bar showing the overall backup progress.

Integration Services Provider

The next thing I did was try to use the IntegrationServices. I say try because there a bug in CTP3, if you navigate to the IntegrationServices provider extension you’ll see:

PS SQLSERVER:\IntegrationServices\SQL11> dir
WARNING: 'DEFAULT' not available: Could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServices,
Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find
the file specified. --> Could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServices,
Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find
the file specified.

Looking at the module manifest sqlps.psd1 I could see that the Microsoft.SqlServer.Management.IntegrationServices assemlby wasn’t being loaded, so I’d I tried to load it:

add-type -Path "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll"
Add-Type : Could not load file or assembly 'file:///C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlSer
ver.IntegrationServices.Server.dll' or one of its dependencies. This assembly is built by a runtime newer than the curr
ently loaded runtime and cannot be loaded.

The  message “runtime newer than the currently loaded runtime” means an assembly was written in higher version than the Powershell host supports. In PowerShell V2 only supports .NET 3.5.

Fixing Integration Services Provider

I filled a bug report on Connect (please vote for the the item), but rather than wait for a a fix I’ve seen this error before with other assemblies and so have few other folks. One way to fix the issue is to create a config file to tell the Powershell host to use a later version of the .NET framework. I used this post by Thomas Lee (blog|twitter) as a guide and crafted a SQLPS.exe.config file with the following contents:

<?xml version="1.0"?>
<configuration>
    <startup uselegacyv2runtimeactivationpolicy="true">
        <supportedruntime version="v4.0.30319"/>
        <supportedruntime version="v2.0.50727"/>
    </startup>
</configuration>

Place the config file in the same directory as sqlps.exe (C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn).

I uploaded a few test SSIS packages and now I’m able to use the Integration Services provider. Once you navigate to the right container the Integration Servcies provider returns PackageInfo objects.

Since the preferred storage model of SSIS packages is moving away from the file system in Denali– I can definitely see  Integration Services provider extension being very useful in managing SSIS packages. I need to work with PackageInfo object in Denali some more as things have changed. As an example in 2005 to 2008 R2 I could call the Execute method on a package object, you can do the same Denali, but the method signature has changed and now expects something called an EnvironnmentReference.I haven’t figure out how execute a package in the context of the provider yet. I’ll post an update once I do or if anyone has figures this out, please post a comment.

Summary

• sqlps has been re-done as a regular Powershell host
• There are two modules, 40 new cmdlets and 2 new SqlProviderExtensions
• The Integration Services Provider has bug in CTP3
• Overall I’m impressed with what the SQL Server product team has done