Powershell and SSIS in SQL 2008R2

by Klaas at 2012-10-04 07:51:58

In SQL Server 2012 we have a PSDrive ‘SQLSERVER:\SSIS’. This is an easy way to list existing packages and execution results, like this:
PS SQLSERVER:> ls SSIS\My2012Server\default\catalogs\ssisdb\executions | ?{$_.starttime -gt $(get-date).adddays(-1)} | select packagename,starttime,endtime,status
When I try the same to My2008R2Server, I get an error:
[quote]Cannot find path ‘SQLSERVER:\SSIS\My2008R2Server\default\catalogs\SSISDB\executions’ because it does not exist.[/quote]
PS SQLSERVER:> ls SSIS\My2008R2Server
results in:
[quote]WARNING: ‘DEFAULT’ not available: Operation not supported on version 10.50.[/quote]
I can’t find any SSIS SMO objects.

Is there an alternative to list packages and executions on SQl Server 2008R2?
by cmille19 at 2012-10-04 08:23:22
As you are probably aware SSIS is notorious for not being backwards compatible. SQL Server 2005 requires using SSMS 2005 to manage SSIS, the assembly is "Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0", while 2008 and 2008 R2 use "Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0". SSIS 2012 uses a new assembly entirely (I don’t recall the name). Even the dtsx files are different between 2005, 2008/2008R2 and 2012.

So, it is not surprising SSIS provider in 2012 only works with 2012. As far as alternatives for managing lower versions. There is command-line tool called dtutil.exe which is documented that provides some functionality. Another option, I’ve written an SSIS Powershell module for SSIS 2005 and 2008/2008 R2 which is part of the CodePlex project SQL Server Powershell Extensions (SQLPSX). The module should have similar functionality as the new 2012 provider: http://sqlpsx.codeplex.com/
by Klaas at 2012-10-05 00:26:23
Thanks Chad

for both your answer and the SQLPSX module. :slight_smile:
I have SQLPSX since years, but I keep forgetting to use it. Sorry.
All I need is in there.