SQLPSX 1.4 Release

Coordinator
Jan 16, 2009 at 3:11 AM
I completed Release 1.4 of SQLPSX which adds 15 new functions for working with SQL Server Integration Services (SSIS). With this release there are now 74 total functions and 10 scripts around SMO, Agent, RMO, and SSIS.
 
Here's a few example of working with SQL Server Integration Services:
 
Load an SSIS package and execute:
$package = Get-ISPackage 'Z002_SQL1\MyPackage' 'Z002'
$package.Execute()
 
Recursively list all SSIS packages on the Z002\SQL1 instance
get-isitem '\' 'Z002_SQL1' 'Z002\SQL1' -recurse $true 
 
Recursively copy all SSIS packages and folder from the Integration Server Z002 to Z003. In addition change the Connection Manager named SSISCONFIG to the server Z003\SQL2 during the copy process
--SQL to SQL,
copy-isitemsqltosql -path '\' -topLevelFolder 'Z002_SQL1' -serverName 'Z002\SQL1' -destination 'Z003_SQL2' -destinationServer 'Z003' -recurse $true -connectionInfo @{SSISCONFIG='Z003\SQL2'}
 
Recursively copy all SSIS packages and folder from the Integration Server Z002 to file system path C:\usr\binSSIS  In addition change the Connection Manager named SSISCONFIG to the server Z003\SQL2 during the copy process
--SQL to file
copy-isitemsqltofile -path '\' -topLevelFolder 'Z002_SQL1' -serverName 'Z002\SQL1' -destination 'C:\usr\bin\SSIS' -recurse $true -connectionInfo @{SSISCONFIG='Z002\SQL2'}
 
Recursively copy all SSIS packages and folder from file system path C:\usr\binSSIS to the Integration Server Z003. In addition change the Connection Manager named SSISCONFIG to the server Z003\SQL2 during the copy process
--file to SQL
copy-isitemfiletosql -path 'C:\usr\bin\SSIS' -destination 'Z003_SQL2' -desinationServer 'Z003' -recurse $true -connectionInfo @{SSISCONFIG='Z002\SQL2'}
 
List running packages on the Z002 server:
Get-ISRunningPackage Z002
 
The complete list of new functions added in the 1.4 Release:
        Copy-ISItemSQLToSQL
            Copies a Package or SSIS folder from SQL to SQL
        Copy-ISItemSQLToFile
            Copies a Package or SSIS folder from SQL to file
        Copy-ISItemFileToSQL
            Copies a Package or SSIS folder from file to SQL
        Get-ISData
            Executes a query and returns an ADO.NET DataTable
        Get-ISItem
            Retrieves a list of SQL Server Integration Services folders and packages from the specified SQL Server instance. Returns a PackInfo Object.
            Note: Unlike the other SSIS functions this function requires a SQL instance name i.e. serverName\instanceName
        Get-ISPackage
            Retrieves an SSIS package from the specified Integration Services server or file path. Returns a Package Object
        Get-ISRunningPackage
            Returns a list of running packages on the specified Integration Services server. Returns a RunningPackage object or collection of objects
        Get-ISSqlConfigurationItem
            Executes a query to retrieve a configuration item
        New-ISApplication
            Base object for all other functions. Executes new-object ("Microsoft.SqlServer.Dts.Runtime.Application")
        New-ISItem
            Creates a SQL storage folder for the specified Integration Services server
        Remove-ISItem
            Deletes a SQL storage folder or package on the specified Integration Services server
        Rename-ISItem
            Renames a SQL storage folder or package on the specified Integration Services server
        Set-ISConnectionString
            Sets the Connection string for an SSIS package. Useful for package configuration connection string which cannot be set dynamically at run
            or deploy time
        Set-ISPackage
            Saves an SSIS package to an Integration Services server or file path as a dtsx file.
        Test-ISPath
            Test the existance of a SQL storage folder or package on the specified Integration Services server
 
I choose to put the SSIS related functions into a separate Library file, LibrarySSIS.ps1. I did this because the SSIS related objects are in the Microsoft.SqlServer.Dts.Runtime namespace instead of the Smo or RMO namespace, so it made sense to use separate Library file. You'll need to source the additional Library file to load function definitions.  Also because SSIS 2005 and 2008 Microsoft.SqlServer.Dts.Runtime namespace are not compatible you'll need to change the load assembly to the specific 2005 or 2008 version.
 
With Release 1.4 complete, I'm starting work on the 1.5 Release which will add Remove, Add, and Update functions where appropriate (prior releases have focused soley on Get functions). My goal is to the 1.5 release be feature complete and then move to a 2.0 release which will re-implement the functions as proper C# cmdlets and providers OR re-implement everything as advanced functions for Powershell V2. I haven't decided yet, whether to go the C# route or the Powershell V2 route.