Powershell and SSIS in SQL 2008R2

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 4 months ago.

  • Author
    Posts
  • #6362

    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. 🙂
    I have SQLPSX since years, but I keep forgetting to use it. Sorry.
    All I need is in there.

You must be logged in to reply to this topic.