How to Chuck Norris Stop a COM+ Server Package via C#

using COMAdmin;
using System;
using System.Diagnostics;
using System.Management;
using System.Threading;
namespace ComAdminShutDown
{
    public class ComAdminManager
    {
        /// <summary>
        /// This method shuts down a COM+ package server.
        /// </summary>
        /// <param name="applicationNames">list of package names to shutdown</param>
        /// <param name="timeoutspan">How much time we allow the COM+ package to run after stop</param>
        /// <returns></returns>
        public static bool ShutDown(string[] applicationNames, int timeoutspan=1,string serverName="localhost")
        {
            COMAdminCatalog catalog = new COMAdminCatalog();
            catalog.Connect(serverName);
            ICatalogCollection catalogApplications = catalog.GetCollection("Applications");
            catalogApplications.Populate();
            for (int i = 0; i < applicationNames.Length; ++i)
            {
                string aplicationName = applicationNames[i];
                foreach (ICatalogObject catalogObject in catalogApplications)
                {
                    string packageGuid = catalogObject.Key;
                   
                    //Application Exists in COM+ Catalog
                    if (catalogObject.Name == aplicationName) 
                   
                    {
                   (bool,int) isPackageRunning = InstanceIsRunning(catalog, packageGuid);
                        if (isPackageRunning.Item1)
                        {
                        Console.WriteLine("The package {0} is running.", aplicationName);
                           catalogObject.Value["RecycleExpirationTimeout"] = timeoutspan;
                            catalogApplications.SaveChanges();
                            catalog.ShutdownApplication(catalogObject.Name);
                            catalogObject.Value["IsEnabled"] = 0;
                            catalogApplications.SaveChanges();
                            int count = 0;
                            bool needsFatality = true;
                            do
                            {
                              isPackageRunning = InstanceIsRunning(catalog, packageGuid);
                                if (isPackageRunning.Item1)
                                {
                                    Thread.Sleep(timeoutspan*1000/5);
                                }
                                else
                                {
                                    needsFatality = false;
                                    break;
                                }
                                count++;
                            } while (count < 5);
                            if (needsFatality)
                            {
                                if (serverName == "locahost")
                                {
                                    //Kill pid only works on localhost
                                    KillProcess(isPackageRunning.Item2, serverName);
                                }
                                else
                                {
                                    // WMI 
                                    WMIKillProcess(isPackageRunning.Item2, serverName);
                                }
                            }                            
                        }
                    }
                }
            }
            return true;
        }
        private static void WMIKillProcess(int item2, string serverName)
        {          
            var connectoptions = new ConnectionOptions();
            
            var endpoint= @"\\" + serverName + @"\root\cimv2";
            ManagementScope scope = new ManagementScope(endpoint, connectoptions);
            var queryStr = "select * from Win32_process where pid = '" + item2 + "'";
            var query = new SelectQuery(queryStr);
            using (var searcher = new ManagementObjectSearcher(scope, query))
            {
                foreach (ManagementObject process in searcher.Get()) 
                {
                    process.InvokeMethod("Terminate", null);
                }
            }
        }
        private static void KillProcess(int pid,string serverName)
        {
            Process[] process = Process.GetProcesses(serverName);
            foreach (Process prs in process)
            {
                if (prs.Id == pid)
                {
                    try
                    {
                        prs.Kill();
                        prs.WaitForExit(0);
                        break;
                    }
                    catch (NotSupportedException)
                    {
                        //Already dying
                    }
                    catch (InvalidOperationException)
                    {
                        //Already dying
                    }
                }
            }
        }
        private static (bool,int) InstanceIsRunning(COMAdminCatalog catalog, string packageId)
        {
            COMAdminCatalogCollection applicationInstances = catalog.GetCollection("ApplicationInstances");
            int pid = 0;
            applicationInstances.Populate();
            foreach (COMAdminCatalogObject applicationInstance in applicationInstances)
            {
                if (applicationInstance.Value["Application"] == packageId)
                {
                    pid =(int)applicationInstance.Value["ProcessID"];
                    return (true, pid);
                }
            }
            return (false, 0);
        }
    }
}

 

SSIS Build Performance

After a while we detected a low performance executing build of SSIS solution with the MsBuild extension of CodePlex (https://archive.codeplex.com/?p=sqlsrvintegrationsrv) .

Doing some debug with Visual Studio and with the help of Sysinternals tools we reached a diagnostic.

When using SSDT 2015 (SQL Server Data Tools) calling the method Add of the Microsoft.SqlServer.Dts.Runtime.ConnectionManagerItems class generates an insane lookup in the registry for the following pattern: HKCR…\CLSID…\Implemented Categories{GUID}.

The GUID changes according to the type of connection provider you are adding, the lookup is going to look all COM objects installed in the machine to see if the implement the category needed.

With Process Monitor, I detected approximately 25 Million lookups in the registry trying to find the above pattern, this number is indexed to the number of COM installed in the server.

The Implemented Categories probing is done for all connections in the VS Solution and again for all connections referenced or used in dtsx files.

Because the company I work in has Microsoft support we asked help with this issue, they classified this as a bug, but the version of this product is not under support, only for security bugs. We are going to have to live with this bug, but not for a long time as we are in progress with the upgrade of our SQL Server products.

Parallel copy of files in NAnt

There are two main options to execute parallel copy of files, one is to user JFlower project (https://sourceforge.net/projects/jflower) to create multithread in NAnt, the other option is to copy the files using PowerShell. The JFlower option we will discard because it creates a third part dependency with no support and with no updates warranty. The PowerShell option to execute parallel copy can be implemented in different ways, here some examples:

  1. Use Copy-Item with the Invoke-Command.
$servers = Get-Content 'C:\bpi_servers.txt'

Invoke-Command -ComputerName $servers -ScriptBlock

{
    Copy-Item -Path '\\machine01\_Projectos\137\Mainline\Artifacts\Latest\XML' -Destination "E:\NetDados\teste" -Verbose

}

Requirements: WinRM, PowerShell >=3, .Net Framework 4.0 in client and server

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_remote_requirements?view=powershell-6&viewFallbackFrom=powershell-5

Note:

https://blogs.technet.microsoft.com/heyscriptingguy/2011/06/13/use-powershell-invoke-command-for-remoting

“By default, PowerShell will talk to up to 32 computers at once; if you specified more than that, it will queue them up, so that, as one computer completes, the next one in line will begin. If you have a awesome network and powerful computers, you could raise that number by specifying the -throttleLimit parameter of Invoke-Command—read the command’s help for more information.”

 

  1. User Workflow, foreach –parallel and Copy-Item together
$computers = ‘W16TP5TGT01’, ‘W16TP5TGT02’
workflow parallelcopy
{

    param ([string[]]$computername)
    foreach -parallel ($computer in $computername)
    {

        InlineScript
        {

            $s = New-PSSession -ComputerName $using:computer
            Copy-Item -Path \\machine01\_Projectos\137\Mainline\Artifacts\Latest\XML -Destination E:\Netdados\teste -ToSession $s
            Remove-PSSession -Session $s
        }
    }
}

 

Requirements: WinRM, PowerShell >=3, .Net Framework 4.0 in client and server

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_remote_requirements?view=powershell-6&viewFallbackFrom=powershell-5

Note: Good for cenários were existes the need to execute a workflow that survives reboots or when execution order matters. Implemented with WWF 4.X.

 

  1. Use Start-Job together with robocopy
$startDate = (Get-Date)
Write-Host "Start Date:" + $startDate
$serverlist = @('SSMWFVNPBCOM01','SDMWFVNPBCOM01','SSMWFVINTCOM01','SDMWFVINTCOM01','SSMWFVNPBCOM01','SDMWFVNPBCOM01','SSMWFVINTCOM01','SDMWFVINTCOM01')
$jobs = foreach ($serverName in $serverlist)
{
    Write-Host "Starting copy job for $serverName"
    Start-Job -Name "Copyjob $serverName" -ArgumentList $serverName -ScriptBlock 
{ param($serverName) if (Test-Connection -ComputerName $serverName -Count 1 -Quiet)
{ robocopy "c:\temp" "\\$serverName\netdados" teste.zip /R:0 }}
}
write-host "Waiting for job(s)"
$jobs | Wait-Job
#write-host "Job(s) output(s)"
#$jobs | Receive-Job
$endDate = (Get-Date)
Write-Host "End Date:" + $endDate
$timeSpan = NEW-TIMESPAN –Start $startDate –End $endDate 
Write-Host "This script took $($timeSpan.Milliseconds) milliseconds to run."

Requirements: PowerShell >=2 no cliente e SMB protocol between cliente and server

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_remote_requirements?view=powershell-6&viewFallbackFrom=powershell-5

Note: Did not find a throttle specification for start-job, we need to control throttling with code.

https://exchange12rocks.org/2015/05/24/how-to-limit-a-number-of-powershell-jobs-running-simultaneously/

Discussion:

There are different ways to implement parallel copy of files in Nant.

In this specific scenario, we will choose to user Start-Job together with robocopy because our infrastructure already has PowerShell 2 or higher in every machine and SMB protocol is already configured in firewall and routing rules.

ReportingServiceTools 0.0.4.2. – Write-RsFolderContent – Idiosyncrasy

In the post, SQL Server Reporting Services buid and deploy automation, we used Write-RsFolderContent to deploy the source artifacts to the server, you can see the Write-RsFolderContent source code here.

Because Write-RsFolderContent does not have an special order over the items it sends to the report server you will end up with reports, and shared DataSets,  DataSource connections links broke, in the report server database.

To prevent this you can create the DataSouces before invoking the Write-RsFolderContent.

To complement the previous post I had to invoke the following PowerShell script before executing Write-RsFolderContent, once for witch Data Source, the orchestration is done by our deployer application written in NANT version 0.92.

Param
(
  [string]$sourceFolder = $(throw "sourceFolder is required."), #required parameter, 
  [string]$sqlConnectionString = $(throw "sqlConnectionString is required."), # connection string to report server
  [string]$folderPath = $(throw "folderPath is required."), # report folder path  
  [string]$FolderName = $(throw "FolderName is required."), # report folder name
  [string]$name = $(throw "parameter name is required.") # data source name
)

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$scriptPathInclude = $scriptPath + "\log.ps1"

$scriptPathIncludeRS = $scriptPath + "\ReportingServicesTools\ReportingServicesTools.psd1"

$scriptPathPassword = $scriptPath + "\Get-Password.ps1"

# Garantir importação manual dos comandos, caso nÃo estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
    write-Output "A importar " $scriptPathInclude
    . $scriptPathInclude
}

#Importar as reporting services power shell tools

LogMessage('A importar ' + $scriptPathIncludeRS )

Import-Module "$scriptPathIncludeRS" -Force


try 
{

$folderExists = $false

#Set Verbose Mode

$VerbosePreference = "continue"

$folders = Get-RsFolderContent -RsFolder $folderPath -ReportServerUri $sqlConnectionString

foreach ($element in $folders) {

  If($element.Name -eq $folderName -And $element.TypeName -eq "Folder" )
    {
          $folderExists = $true
          break
    }
}

#Criar folder no root do report server

If($folderExists -eq $false)
{
    New-RsFolder -ReportServerUri $sqlConnectionString -Path $folderPath -Name $folderName -Verbose
}

#Create Shared Resources
 
$dsPath = $sourceFolder + "\" + $name + ".rds"
$newRSFolderPath = $folderPath + "/" + $FolderName

Write-RsCatalogItem -ReportServerUri $sqlConnectionString -Path $dsPath  -RsFolder $newRSFolderPath -Overwrite -Verbose
 

Exit 0

}
Catch
{
  LogMessage ('[EXCEPTION] ' + $_)
  
  $err = $_.Exception
    
  while ( $err.InnerException )
    {
    $err = $err.InnerException
    LogMessage $err.Message
    };
    continue
  
  throw $_.Exception;
}

 

 

SSRS MSBuild Idiosyncrasy

When testing the SSRS build and deploy described in the previous post, I realized that after upgrading SSIS Solutions to Visual Studio 2015 and making changes to the reports they were automatically, in some cases, migrated to version 2016 by the Visual Studio 2015 Designer.

The start of the content of the upgraded rpt file was the following:

<?xml version=”1.0″ encoding=”utf-8″?><Report xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition”/>

The build generated a ispac with some rpt files updgraded to version 2016. During deploy to an infrastrure with SQL Server 2012 occurred the error:

“The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition‘ which cannot be upgraded.”

After some research I found that targetServerVersion setting choose via project properties and stored in rptproj file were ignored during Build time.

MSBuild has the following behavior, when invoking MSBuild over a project file (rptproj) :

Doesn’t take in account the choose TargetServerVersion.

When MSBuild is invoked over a Solution file (sln) :

The TargetServerVersion property is taken in account and the reports are changed to the selected version as we can see in the above picture.

Conclusion: I have changed the build task to make builds using the unit *.sln file.

SQL Server Reporting Services build and deploy automation

In sequence to my previous post, here, I also implemented the build and deploy automation for Reporting Services Solutions.

The Build is made using MSBuild.

The deploy uses PowerShell, back in November 2016 the SSRS team announced the availability of 28 new PowerShell commands for working with SSRS (in Native Mode) available in the PowerShell Gallery and in GitHub. These commands use the SSRS API so a lot of the commands will work with SSRS instances as old as SQL Server 2008 R2, however some of the commands like Register-PowerBI, will only work with a SSRS 2016+ instance. These scripts can be used in three flavor, SOAP, REST and WMI API, I opted to use the SOAP version.

1. Pre-requisites:

1.1. SQL Server Data Tools 2015 (SSDT2015), you can download them here.
1.2. Visual Studio using CLR 4.0 or above to build an library (assembly).
1.3. Power Shell 3.0 (Scripting the deploy)
1.4. SSRS > 2008 R2
1.5. HTTP, port 80, connectivity from buidl/deploy server to SSRS.
1.5. Connectivity between deploy server and Report Server, protocol HTTP port 80.

2. Build Solution via MSBuild

As announced by SQL Server Reporting Services Team, here, Visual Studio 2015 Reporting Services solutions with SSDT 2015 are now compatible with MSBuild. I had leverage this feature to build the solutions and generate the artifacts to deploy.

Like in the latest post the MSBuild command is invoked via a Ruby\Rake task:

module Build

    class CompileSSRSTask < Task

        attr_reader :solutions

        def initialize(rake_name, build_spec, logger)
            super(rake_name, build_spec, logger)
            @solutions = []
          end

          def setup
            @solutions = get_projects_to_compile
          end

          def execute            
           
       #Build projects not Solution
       
            @solutions.each do |sln|
                get_rptprj_from_solution(sln).each do |dtProj|

                    puts 'Solução: ' + sln
          
                    puts 'Projecto encontrado na solução: ' + dtProj  
          
            sdk_path = Build::DOT_NET_4_0_SDK_PATH

                    puts 'SDK Path: ' + sdk_path

                    msbuild_path = get_msbuild_path
                    
                    if(!msbuild_path.nil? && msbuild_path != "")
          
                      msbuild = "\"#{msbuild_path}/msbuild.exe\""
            
                    else
          
                      msbuild = "#{sdk_path}/msbuild"
            
                    end

                    command = "#{msbuild} #{dtProj}"

                    puts command

                    exec_and_log(command)

                end
            end

          end

          private
          def get_projects_to_compile
      build_spec.get_spec_for(rake_name.to_sym, :projects_to_compile, Rake::FileList[File.join(build_spec.working_dir_path, '*VSSSRS*.sln')])
          end

          def get_rptprj_from_solution(slnfile)
      references = []
      dtproj = []			
      dirname = ""
      File.open(slnfile, "r") do |f|
      dirname = File.dirname  f
        f.each_line do |line|
        if(line.start_with? "Project")
          tokens = line.split(',')
          candidate = tokens[1]	
          if(!candidate.nil?)
            if(candidate.include? ".rptproj")
              references.push candidate.gsub('"','').strip!
            else
              puts "\tskipping #{candidate}." if @verbose
            end
          end
        end
        end
      end

      references.each do |c|	
        c = File.join(dirname,c)
        dtproj.push c
      end
      
      return dtproj
        end
        
        def get_msbuild_path
            msbuild_version = build_spec.get_spec_for(rake_name.to_sym, :msbuild_version, "")
            if(msbuild_version == "14" || msbuild_version == "14.0")
                Build::MSBUILD_14_PATH
            end
        end
      
    end

end

3. Deploy via Power Shell 3

Now that we have the deploy artifacts, dtsx , datasources and datasets files, let us deploy it in a automated process.

To execute the deploy we used the Power Shell scripts of ReportingServiceTools 0.0.4.2.

This Power Shell module typically is installed via PS Consolse:

PS>Install-Module -Name ReportingServicesTools

As in the latest post, we need an offline installation, after installing it in your local machine, copy the module to the Deploy Server. During the online installation when no scope is defined, or when the value of the Scope parameter is AllUsers, the module is installed to %systemdrive%:\Program Files\WindowsPowerShell\Modules. When the value of Scope is CurrentUser, the module is installed to $home\Documents\WindowsPowerShell\Modules.

I wrote two scripts, one to install the report project folder in the reporting server.
The second one allows the parametrization of the datasource connection string.

3.1. PowerShell Deploy script

Param
(
  [string]$sourceFolder = $(throw "sourceFolder is required."), #required parameter, 
  [string]$ReportServer = $(throw "ProjectFilePath is required."), #required parameter, 
  [string]$folderPath = $(throw "EnvironmentName is required."), #required parameter,
  [string]$folderName = $(throw "sqlConnectionString is required.") #required parameter
)

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$scriptPathInclude = $scriptPath + "\log.ps1"

$scriptPathIncludeRS = $scriptPath + "\ReportingServicesTools\ReportingServicesTools.psd1"

# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
    write-Output "A importar " $scriptPathInclude
    . $scriptPathInclude
}

LogMessage('A importar ' + $scriptPathIncludeRS )

Import-Module "$scriptPathIncludeRS" -Force

#Get-Command -Module ReportingServicesTools

try 
{

$folderExists = $false

#Set Verbose Mode

$VerbosePreference = "continue"

$folders = Get-RsFolderContent -RsFolder $folderPath -ReportServerUri $ReportServer

foreach ($element in $folders) {

  If($element.Name -eq $folderName)
    {
          $folderExists = $true
          break
    }
}

#Criar folder no root do report server

If($folderExists -eq $false)
{
    New-RsFolder -ReportServerUri $ReportServer -Path $folderPath -Name $folderName -Verbose  
}

$fullpath = $folderPath + '/' + $folderName 

#Override do conteudo do folder

Write-RsFolderContent -ReportServerUri $ReportServer -Path $sourceFolder -Destination $fullpath -Overwrite -Verbose

LogMessage($folderName + ' folder has been deployed successfully')

Exit 0

}
Catch
{
  LogMessage ('[EXCEPTION] ' + $_)
  
  $err = $_.Exception
    
  while ( $err.InnerException )
    {
    $err = $err.InnerException
    LogMessage $err.Message
    };
    continue
  
  exit 1;
}

3.1. DataSource Connection String parametrization

Param
(
  [string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
  [string]$folderPath = $(throw "folderPath is required."), #required parameter,  
  [string]$FolderName = $(throw "FolderName is required."), #required parameter
  [string]$name = $(throw "parameter name is required."), #required parameter 
  [string]$value = $(throw "parameter value is required.") 
)

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$scriptPathInclude = $scriptPath + "\log.ps1"

$scriptPathIncludeRS = $scriptPath + "\ReportingServicesTools\ReportingServicesTools.psd1"

# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
    write-Output "A importar " $scriptPathInclude
    . $scriptPathInclude
}

LogMessage('A importar ' + $scriptPathIncludeRS )

Import-Module "$scriptPathIncludeRS" -Force

#Get-Command -Module ReportingServicesTools

try 
{

#Look for DataSources in the dest folder and set their values

$dataSourcefullpath = $folderPath + '/' + $folderName + '/' + $name

$dataSource = Get-RsDataSource -ReportServerUri $sqlConnectionString -Path $dataSourcefullpath 

if ($dataSource -ne $null) 
{
  #Change Connection

  Write-Host $dataSource.Name

  $dataSource.ConnectString = $value

  Set-RsDataSource -ReportServerUri $sqlConnectionString -RsItem $dataSourcefullpath -DataSourceDefinition $dataSource

  Write-Output "Changed datasource $($name)"
}
else 
{
  Write-Warning "DataSource $($name) does not exists!"
  Exit 1
}         

Exit 0

}
Catch
{
  LogMessage ('[EXCEPTION] ' + $_)
  
  $err = $_.Exception
    
  while ( $err.InnerException )
    {
    $err = $err.InnerException
    LogMessage $err.Message
    };
    continue
  
  exit 1;
}

3.2 The PowerShell Orchestration 

To execute the deployment I use a aplication in NANT version 0.92. This application receives as input a XML file with the indication of the location of the artifacts to install and an configuration xml file. The look like this:

<ssrsInstall><ssrs prefix="ssrs" reportServer="http://myServer/ReportServer_SQL01" destFolderPath="/reports/sales" destFolderName="grocery">
    <dataSources>
        <dataSource name="SQL01" connectionString ="data source= sql01.dev.loc;initialcatalog=sales"/>
        <dataSource name="Notfound"connectionString="value"/>
    <dataSources/>
</ssrs>...
</ssrsInstall>

Specific configurations to deploy the SSRS solutions:

reportServer- Report Server Virtual Directory;

destFolderPath- Report server folder path;

destFolderName- Reports folder name;

dataSource- (root node to apply datasource parametrization)

name- data source name;

connectionString – connectionstring

The NANT task is the following:

<?xml version="1.0" ?>
<project>
  <target name="ssrsInstall">
    
    <!-- Declare properties -->
    
    <property name="ssrsInstall.reportServer" value=""/>		
    <property name="ssrsInstall.destFolderPath" value=""/>
    <property name="ssrsInstall.destFolderName" value=""/>
    <property name="ssrsInstall.parameter.connectionString" value=""/>			
    <property name="ssrsInstall.parameter.name" value=""/>					
    <property name="ssrsInstall.prefix" value="" />			
      
    <!-- Iterate over ssrs instalations settings -->
    <xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssrsInstall/ssrs">    
      
      <!-- Read instalation properties -->
      <xmlpropertybinding>				
        <get xpath="@reportServer" property="ssrsInstall.reportServer"/>				
        <get xpath="@destFolderPath" property="ssrsInstall.destFolderPath"/>
        <get xpath="@destFolderName" property="ssrsInstall.destFolderName"/>
        <get xpath="@prefix" 	        property="ssrsInstall.prefix" />	
      </xmlpropertybinding>
    
      <do>
        <echo message="[project.prefixList]: ${project.prefixList}"></echo>
        <echo message="[ssrsInstall.prefix]: ${ssrsInstall.prefix}"></echo>
        <stringMatch stringCompare="${ssrsInstall.prefix}" stringList="${project.prefixList}" returnProperty="ssrsInstall.prefixMatch"></stringMatch>	
        <if test="${(string::to-upper(ssrsInstall.prefixMatch) == string::to-upper(ssrsInstall.prefix) or string::to-upper(project.prefixList) == 'ALL')}">
      
      
          <!-- Check for mandatory values -->
          <fail message="Property reportServer not found in manifest" 	if="${ssrsInstall.reportServer == ''}" />			
          <fail message="Property destFolderPath not found in manifest" if="${ssrsInstall.destFolderPath == ''}" />
          <fail message="Property destFolderName not found in manifest"		if="${ssrsInstall.destFolderName == ''}" />
            
          <!-- Debug: print values -->
          <echo message="reportServer: ${ssrsInstall.reportServer}"/>		
          <echo message="destFolderPath: ${ssrsInstall.destFolderPath}"/>			
          <echo message="destFolderName ${ssrsInstall.destFolderName}"/>
    
          
          <!-- Execute instalation -->
          <call target="ssrsInstall.Execute"/>  
              
          <xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssrsInstall/ssrs/dataSources/dataSource"> 						
            <xmlpropertybinding>				
              <get xpath="@name" property="ssrsInstall.parameter.name"/>
              <get xpath="@connectionString" property="ssrsInstall.parameter.connectionString"/> 					
            </xmlpropertybinding>
            
            <do>							
                          
              <call target="ssrsInstall.DataSource.Execute"/>	
              
              <property name="ssrsInstall.parameter.name" value=""/>			
              <property name="ssrsInstall.parameters.connectionString" value=""/>						
              
            </do>
          </xml-foreach>	
                    
          <property name="ssrsInstall.reportServer" value=""/>			
          <property name="ssrsInstall.destFolderPath" value=""/>
          <property name="ssrsInstall.destFolderName" value=""/>
          
        </if>
      </do>			
        
    </xml-foreach>
  </target>
  
  <target name="ssrsInstall.Execute">		
  
    <property name="ssrsInstall.args" value="-sourceFolder &quot;${project.artifactsDir}&quot;  -ReportServer 	&quot;${ssrsInstall.reportServer}&quot; -folderPath ${ssrsInstall.destFolderPath} -folderName &quot;${ssrsInstall.destFolderName}&quot;" />
    
    <echo message="ssrsInstall.args: ${ssrsInstall.args}" />		
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installRpt.ps1" 
            scriptargs="${ssrsInstall.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target>
  
  <target name="ssrsInstall.DataSource.Execute">
    
    <property name="ssrsInstall.Parameters.args" value="-sqlConnectionString &quot;${ssrsInstall.reportServer}&quot; -folderPath ${ssrsInstall.destFolderPath} -folderName &quot;${ssrsInstall.destFolderName}&quot; -name &quot;${ssrsInstall.parameter.name}&quot; -value  &quot;${ssrsInstall.parameter.connectionString}&quot;" />
    
    <echo message="ssrsInstall.Parameters.Args: ${ssrsInstall.Parameters.args}" />
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installRptDataSource.ps1" 
            scriptargs="${ssrsInstall.Parameters.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target>
  
  <!-- <target name="ssrsInstall.Parameters.ShowAll">
    
    <property name="ssrsInstall.Parameters.ShowAll.args" value="-sqlConnectionString &quot;${ssrsInstall.connectionString}&quot; -ProjectName &quot;${ssrsInstall.projectName}&quot; -FolderName &quot;${ssrsInstall.folderName}&quot;" />
    
    <echo message="ssrsInstall.Parameters.ShowAll.Args: ${ssrsInstall.Parameters.ShowAll.args}" />
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installIspacParametersShowAll.ps1" 
            scriptargs="${ssrsInstall.Parameters.ShowAll.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target> -->
  
  
</project>

Final notes:

This process allows the automation of  builds and deploy of SSRS solutions. The orchestration of the Ruby\Rake and Nant application is made by Hudson Server.

King Regards,
Nuno Monteiro

 

SQL Server Integration Services build and deploy automation

Based on an article of Nat Sundar I implemented another solution to automate build and deploys of SSIS projects via project deployment model, with a higher version of Visual Studio and SQL Server Data Tools. I describe the process here in this post. For the deployment I have made possible to configure all SSIS Parameters, project and package via Power Shell. Let us start.

1. Pre-requisites:

1.1. SQL Server Data Tools 2015 (SSDT2015), you can download them here.
1.2. Visual Studio using CLR 4.0 or above to build an library (assembly).
1.3. Power Shell 4.0 (Scripting the deploy)
1.4. SSIS solutions with Project Deployment Model, implies SSIS > 2008
1.5. Connectivity between deploy server and SSIS Server, protocols TCP and TDS, port 1433.

Note: I have tried to do this using Visual Studio 2017 and SSDT 2017 Preview, because our build server is a headless server and the offline installation of  SSDT 2017 Preview did not had success (Installation of SSDT 15.3.0 Preview fails with error: “0x8007138B, The cluster resource cannot be made dependent on the specified resource because it is already dependent.) I end-up using 2015 versions. Posted the bug at Microsoft connect with the nickname DonBox, now available here.

2. Build Solution via MSBuild


2.1. Build an extension task to let MSBuild build Visual Studio dtProj files.


After downloading the source code from CodePlex, here, you must install SSDT 2015 in your development machine.

Open the project and edit in text mode as you see in the following image, to set the path for the following 3 assemblies:

$(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Project.DLL
$(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataTransformationServices.VsIntegration.DLL

$(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataWarehouse.VsIntegration.DLL

 

My system variable VS140COMNTOOLS point to C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\Tools\, SSDT 2015 installation updates Visual Studio private assemblies.

This source code defines a new MSBuild task, named DeploymentFileCompilerTask that generates a ispac file using project artefacts.

The downloaded project is marked as an strong-named assembly, it is not a requirement, but if you want to maintain the strong-name you must generate a new par of keys associated with the project.

Now invoke MSBuild using this fresh built extension, you can do this via cmd console using the following command after generating a xml parameter file.

MSBuild MSBuild_Script_For_SSIS.proj

The content of the file MSBuild_Script_For_SSIS.proj is the following:

<?xml version="1.0" encoding="Windows-1252"?>
<Project  xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="SSISBuild">
<UsingTask TaskName="DeploymentFileCompilerTask"  AssemblyFile="C:/Windows/Microsoft.NET/Framework/v4.0.30319/Microsoft.SqlServer.IntegrationServices.Build.dll" />
<Target Name="SSISBuild">
<DeploymentFileCompilerTask InputProject="projNameHere" Configuration="Development" ProtectionLevel="DontSaveSensitive"/>
</Target>
</Project>

In this file you specify the MSBuild task to use and the assembly were the task is located.
In my case I have copied the generated assembly  and dependencies to C:/Windows/Microsoft.NET/Framework/v4.0.30319.


In my case the build process is managed by a Ruby/Rake application, I will leave here the code of the Ruby Task I use to make the build of the solutions for your analysis. 

module Build

    class CompileSSISTask < Task

        attr_reader :solutions

        def initialize(rake_name, build_spec, logger)
            super(rake_name, build_spec, logger)
            @solutions = []
          end

          def setup
            @solutions = get_projects_to_compile
          end

          def execute            
           
            @solutions.each do |sln|
                get_dtprj_from_solution(sln).each do |dtProj|

                    puts 'Solução: ' + sln
                    puts 'Projecto encontrado na solução: ' + dtProj
                   
                    filepath =  (File.dirname(dtProj)+"\\MSBuild_Script_For_SSIS.proj")
                   
                    puts 'Ficheiro de extensão MSBuild: ' + filepath

                    xml = %q{<?xml version="1.0" encoding="Windows-1252"?>
                    <Project  xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="SSISBuild">
                    <UsingTask TaskName="DeploymentFileCompilerTask" 
                    AssemblyFile="C:/Windows/Microsoft.NET/Framework/v4.0.30319/Microsoft.SqlServer.IntegrationServices.Build.dll" />
                    <Target Name="SSISBuild">
                    <DeploymentFileCompilerTask InputProject="}+dtProj+%q{" Configuration="Development" ProtectionLevel="DontSaveSensitive"></DeploymentFileCompilerTask>
                    </Target></Project>}
                   
                    File.open(filepath, 'w') { |file| file.write(xml) }

                    sdk_path = get_sdk_path

                    puts 'SDK Path: ' + sdk_path

                    msbuild_path = get_msbuild_path
                    
                    if(!msbuild_path.nil? && msbuild_path != "")
                      msbuild = "\"#{msbuild_path}/msbuild.exe\""
                    else
                      msbuild = "#{sdk_path}/msbuild"
                    end

                    command = "#{msbuild} #{filepath}"

                    puts command

                    exec_and_log(command)

                end
            end

          end

          private
          def get_projects_to_compile
            build_spec.get_spec_for(rake_name.to_sym, :projects_to_compile, Rake::FileList[File.join(build_spec.working_dir_path, '*VSSSIS*.sln')])            
          end

          def get_dtprj_from_solution(slnfile)
      references = []
      dtproj = []
      
      dirname = ""
      File.open(slnfile, "r") do |f|
      dirname = File.dirname  f
        f.each_line do |line|
        if(line.start_with? "Project")
          tokens = line.split(',')
          candidate = tokens[1]	
          if(!candidate.nil?)
            if(candidate.include? ".dtproj")
              references.push candidate.gsub('"','').strip!
            else
              puts "\tskipping #{candidate}." if @verbose
            end
          end
        end
        end
      end

      references.each do |c|	
        c = File.join(dirname,c)
        dtproj.push c
      end
      
      return dtproj
        end
        
        def get_msbuild_path
            msbuild_version = build_spec.get_spec_for(rake_name.to_sym, :msbuild_version, "")
            if(msbuild_version == "14" || msbuild_version == "14.0")
                Build::MSBUILD_14_PATH
            end
        end

        def get_sdk_path
            dot_net_version = build_spec.get_spec_for(rake_name.to_sym, :dot_net_version, "default")
      
           # if dot_net_version == "default"
            #  Build::DOT_NET_SDK_PATH
            #elsif (dot_net_version == "2.0" || dot_net_version == "2")
            #  Build::DOT_NET_2_0_SDK_PATH        
            #elsif (dot_net_version == "4.0" || dot_net_version == "4")
              Build::DOT_NET_4_0_SDK_PATH
            #end
        end
          
    end

end

3. Deploy via Power Shell 4

Now that we have the ispac, let us deploy it in a automated process.

To execute the deploy we also used the assemblies in SSDT 2015, the language of choice is PowerShell.

I wrote three scripts, one to install the ispac in a Integration Server as is.
the second one lists all possible parameter parametrization in a SSIS project.
A third to set the parameter values using as a souce a XML data file.

3.1. PowerShell Deploy scripts

This script receives as parameter the path for the ispac file ($ProjectFilePath ), the SSIS project name, the SSIS connection string, the folder name and its description.

Param
(
[string]$ProjectFilePath = $(throw "ProjectFilePath is required."), #required parameter,
[string]$ProjectName = $(throw "ProjectName is required."), #required parameter,
[string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
[string]$FolderName = $(throw "FolderName is required."), #required parameter,
[string]$FolderDescription = "" #required parameter
)$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition#$scriptLocal = $MyInvocation.MyCommand.Definition -replace ".ps1",".local.ps1"$scriptPathInclude = $scriptPath + "\log.ps1"# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
write-Output "A importar " $scriptPathInclude
. $scriptPathInclude
}try
{# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;# Create a connection object based on the connection string
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create a new Integration Services object based on the SSIS namespace and the connection object
$integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection#echo $integrationServices.Catalogs["SSISDB"]$catalog = $integrationServices.Catalogs["SSISDB"]
$SSISfolder = $catalog.Folders[$FolderName]

LogMessage('Testing if folder '+ $FolderName +' exists!')

if (!$SSISfolder)
{
LogMessage ('Creating Folder ' + $FolderName +' in the SSIS Catalog')
$SSISfolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder ($catalog, $FolderName, $FolderDescription)
$SSISfolder.Create()
LogMessage($FolderName + ' Folder has been created in the SSIS Catalog')
}

LogMessage ('Deploying ' + $ProjectName + ' project ...')

# Read the project file, and deploy it to the folder
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)

$SSISfolder.DeployProject($ProjectName, $projectFile)

LogMessage($ProjectName + ' project has been deployed successfully')

Exit 0

}
Catch
{
LogMessage ('[EXCEPTION] ' + $_)

#LogMessage ($_)

$err = $_.Exception

while ( $err.InnerException )
{
$err = $err.InnerException
LogMessage $err.Message
};
continue

exit 1;
}

3.2. List all existing parameters

This script lists all existing parameters in the project described in the parameters.

Param
(
[string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
[string]$ProjectName = $(throw "ProjectName is required."), #required parameter,
[string]$FolderName = $(throw "FolderName is required.") #required parameter
)$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition#$scriptLocal = $MyInvocation.MyCommand.Definition -replace ".ps1",".local.ps1"$scriptPathInclude = $scriptPath + "\log.ps1"# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
write-Output "A importar " $scriptPathInclude
. $scriptPathInclude
}try
{LogMessage("sqlConnectionString: "+$sqlConnectionString)
LogMessage("ProjectName: "+$ProjectName)
LogMessage("FolderName: "+$FolderName)# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;# Create a connection object based on the connection string
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create a new Integration Services object based on the SSIS namespace and the connection object
$integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection#echo $integrationServices.Catalogs["SSISDB"]
$catalog = $integrationServices.Catalogs["SSISDB"]

$SSISfolder = $catalog.Folders[$FolderName]

$Project = $SSISfolder.Projects[$ProjectName]

Write-Host("Available parameters to set: ")

foreach($package in $Project.Packages)
{
Write-Host("Packages: " + $package.Name)

foreach($parameter in $package.Parameters)
{
Write-Host $Parameter.Name
}
}
Write-Host("Project: " + $Project.Name )

foreach ($Parameter in $Project.Parameters)
{
Write-Host $Parameter.Name
}

LogMessage($ProjectName + ' Parameters Listed.')

Exit 0

}
Catch
{
LogMessage ('[EXCEPTION] ' + $_)

#LogMessage ($_)

$err = $_.Exception

while ( $err.InnerException )
{
$err = $err.InnerException
LogMessage $err.Message
};
continue

exit 1;
}

3.3. Set a parameter value

This last script allows to set parameter values, one of a time.

Param
(
[string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
[string]$ProjectName = $(throw "ProjectName is required."), #required parameter,
[string]$FolderName = $(throw "FolderName is required."), #required parameter
[string]$name = $(throw "parameter name is required."), #required parameter
[string]$value = $(throw "parameter value is required."),
[string]$type
)$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
#$scriptLocal = $MyInvocation.MyCommand.Definition -replace ".ps1",".local.ps1"
$scriptPathInclude = $scriptPath + "\log.ps1"
$scriptPathPassword = $scriptPath + "\Get-Password.ps1"# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
write-Output "A importar " $scriptPathInclude
. $scriptPathInclude
}try
{LogMessage("sqlConnectionString: "+$sqlConnectionString)
LogMessage("ProjectName: "+$ProjectName)
LogMessage("FolderName: "+$FolderName)
LogMessage("Parameters name: "+$name)
LogMessage("Parameters value: "+$value)
LogMessage("Parameters type: "+$type)# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;# Create a connection object based on the connection string
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create a new Integration Services object based on the SSIS namespace and the connection object
$integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection#echo $integrationServices.Catalogs["SSISDB"]$catalog = $integrationServices.Catalogs["SSISDB"]$SSISfolder = $catalog.Folders[$FolderName]
$Project = $SSISfolder.Projects[$ProjectName]

#Write-Host("Name: " + $name)
#Write-Host("Value: " + $value)

Write-Host("Start probing the parameters: " + $name)

$found = $false

foreach($package in $Project.Packages)
{
foreach($parameter in $package.Parameters)
{

if($Parameter.Name.ToUpper() -eq $name.ToUpper())
{

if([string]::IsNullOrEmpty($type)  -Or $type.ToUpper() -ne 'PASSWORD')
{
Write-Host "Setting parameter " $name " with value " $value
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$value)
$Project.Alter()
$found = $true
break;
}
else
{
Write-Host "Setting parameter " $name " with value " $value
$FilePassword = Invoke-Expression -Command ($scriptPathPassword + " -User " + $value)
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$FilePassword)
$Project.Alter()
$found = $true
break;
}

}
}
}

foreach ($Parameter in $Project.Parameters)
{

if($Parameter.Name.ToUpper() -eq $name.ToUpper())
{
if([string]::IsNullOrEmpty($type)  -Or $type.ToUpper() -ne 'PASSWORD')
{
Write-Host "Setting parameter " $name " with value " $value
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$value)
$Project.Alter()
$found = $true
break;
}
else
{
Write-Host "Setting parameter " $name " with value " $value
$FilePassword = Invoke-Expression -Command ($scriptPathPassword + " -User " + $value)
$Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$FilePassword)
$Project.Alter()
$found = $true
break;

}
}
}

if($found -eq $false)
{
Write-Host "[WARNING]: Parameter " $name " not found!"
}
else
{
LogMessage($ProjectName + ' Parameter Configured successfully')
}

Exit 0

}
Catch
{
LogMessage ('[EXCEPTION] ' + $_)

#LogMessage ($_)

$err = $_.Exception

while ( $err.InnerException )
{
$err = $err.InnerException
LogMessage $err.Message
};
continue

exit 1;
} 

3.2 The PowerShell Orchestration 

To execute the deployment I use a aplication in NANT version 0.92. This application receives as input a XML file with the indication of the location of the ispac files as theirs respective parameters values.
The input files look like this:

<ssisInstall>
 <ssis projectFilePath="\bin\Development\ssis1.ispac"
projectName="MyProject" connectionString="Data Source=SQLSSIS;Initial
 Catalog=master;Integrated Security=SSPI;" folderName="MyProject folderDescription="">
    <parameters showparameters="true">
        <parameter name="ServerName" value="ESA1DB2P">
        <parameter name="ConnectionString" value="Data Source=SERVERc;User ID=USER;
Initial Catalog=ESA ;Provider=DB2OLEDB.1">
        <parameter name="password" value="userAccount" type="password">
    <parameters/>
</ssis>
<ssis projectFilePath="\bin\Development\ssis2.ispac"
projectName="MyProject2" connectionString="Data Source=SQLSSIS;Initial
 Catalog=master;Integrated Security=SSPI;" folderName="MyProjectfolderDescription="">
    <parameters showparameters="true">
        <parameter name="CM.ServerName" value="Server2">
    <parameters/>
</ssis>
...
</ssisInstall>

File specifications:

projectFilePath- file system path to *.ispac, relative path;

projectName-  SSIS project name;

environment- Reserved for future user;

connectionString- Connection string for the server;

folderName- Name of the folder in SSIS catalog

folderDescription – Description associated to the folder;

showparameters – Indication if during deploy all possible parameters configurations must me printed

parameter name – name of the parameter
parameter value – value to assign to parameter

type- Optional, if password, this will be applied during execution time, translating the name of the user for the password.

The NANT task is the following:

<?xml version="1.0" ?>
<project>
  <target name="ssisInstall">
    
    <!-- Declare properties -->
    <property name="ssisInstall.projectFilePath" value=""/>
    <property name="ssisInstall.projectName" value=""/>		
    <property name="ssisInstall.environment" value=""/>
    <property name="ssisInstall.connectionString" value=""/>
    <property name="ssisInstall.folderName" value=""/>	
    <property name="ssisInstall.folderDescription" value=""/>
    <property name="ssisInstall.parameter.value" value=""/>			
    <property name="ssisInstall.parameter.name" value=""/>			
    <property name="ssisInstall.parameter.type" value=""/>	
    <property name="ssisInstall.parameters.showparameters" value=""/>	
    <property name="ssisInstall.prefix" value="" />			
      
    <!-- Iterate over ssis instalations settings -->
    <xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssisInstall/ssis">    
      
      <!-- Read instalation properties -->
      <xmlpropertybinding>				
        <get xpath="@projectFilePath" property="ssisInstall.projectFilePath"/>
        <get xpath="@projectName" property="ssisInstall.projectName"/> 				
        <get xpath="@environment" property="ssisInstall.environment"/>
        <get xpath="@connectionString" property="ssisInstall.connectionString"/>
        <get xpath="@folderName" property="ssisInstall.folderName"/>
        <get xpath="@folderDescription" property="ssisInstall.folderDescription"/>
        <get xpath="@prefix" 	        property="ssisInstall.prefix" />	
      </xmlpropertybinding>
    
      <do>
        <echo message="[project.prefixList]: ${project.prefixList}"></echo>
        <echo message="[ssisInstall.prefix]: ${ssisInstall.prefix}"></echo>
        <stringMatch stringCompare="${ssisInstall.prefix}" stringList="${project.prefixList}" returnProperty="ssisInstall.prefixMatch"></stringMatch>	
        <if test="${(string::to-upper(ssisInstall.prefixMatch) == string::to-upper(ssisInstall.prefix) or string::to-upper(project.prefixList) == 'ALL')}">
      
      
          <!-- Check for mandatory values -->
          <fail message="Property projectFilePath not found in manifest" 	if="${ssisInstall.projectFilePath == ''}" />
          <fail message="Property projectName not found in manifest"     	if="${ssisInstall.projectName == ''}" />				
          <fail message="Property connectionString not found in manifest" if="${ssisInstall.connectionString == ''}" />
          <fail message="Property folderName not found in manifest"		if="${ssisInstall.folderName == ''}" />
            
          <!-- Debug: print values -->
          <echo message="${ssisInstall.projectFilePath}"/>
          <echo message="${ssisInstall.projectName}"/>
          <echo message="${ssisInstall.environment}"/>			
          <echo message="${ssisInstall.connectionString}"/>
          <echo message="${ssisInstall.folderName}"/>
          <echo message="${ssisInstall.folderDescription}"/>		
          
          <!-- Execute ISPAC instalation -->
          <call target="ssisInstall.Execute"/>  
          
          <!-- Read Parameters properties-->
          <xmlpeek
            file="${ManifestFile}"
            xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssisInstall/ssis/parameters/@showparameters"
            property="ssisInstall.parameters.showparameters">
          </xmlpeek>
          
          <echo message="ssisInstall.parameters.showparameters: ${ssisInstall.parameters.showparameters}"/>
          
          <if test="${bool::parse(ssisInstall.parameters.showparameters)}">
            <call target="ssisInstall.Parameters.ShowAll"/>
          </if>
          
          
          <xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssisInstall/ssis/parameters/parameter"> 
            
            <xmlpropertybinding>				
              <get xpath="@name" property="ssisInstall.parameter.name"/>
              <get xpath="@value" property="ssisInstall.parameter.value"/> 
              <get xpath="@type" property="ssisInstall.parameter.type"/>						
            </xmlpropertybinding>
            
            <do>							
                          
              <call target="ssisInstall.Parameters.Execute"/>											
              <property name="ssisInstall.parameter.name" value=""/>			
              <property name="ssisInstall.parameters.value" value=""/>						
              
            </do>
          </xml-foreach>			
                  
          
          <property name="ssisInstall.projectFilePath" value=""/>
          <property name="ssisInstall.projectName" value=""/>				
          <property name="ssisInstall.environment" value=""/>
          <property name="ssisInstall.connectionString" value=""/>
          <property name="ssisInstall.folderName" value=""/>	
          <property name="ssisInstall.folderDescription" value=""/>	
          
        </if>
      </do>			
        
    </xml-foreach>
  </target>
  
  <target name="ssisInstall.Execute">		
    <property name="ssisInstall.args" value="-ProjectFilePath &quot;${project.artifactsDir}/${ssisInstall.projectFilePath}&quot;  -ProjectName 	&quot;${ssisInstall.projectName}&quot; -EnvironmentName ${ssisInstall.environment} -sqlConnectionString &quot;${ssisInstall.connectionString}&quot; -FolderName &quot;${ssisInstall.folderName}&quot; -FolderDescription &quot;${ssisInstall.folderDescription}&quot;" />
    <echo message="ssisInstall.args: ${ssisInstall.args}" />		
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installIspac.ps1" 
            scriptargs="${ssisInstall.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target>
  
  <target name="ssisInstall.Parameters.ShowAll">
    
    <property name="ssisInstall.Parameters.ShowAll.args" value="-sqlConnectionString &quot;${ssisInstall.connectionString}&quot; -ProjectName &quot;${ssisInstall.projectName}&quot; -FolderName &quot;${ssisInstall.folderName}&quot;" />
    
    <echo message="ssisInstall.Parameters.ShowAll.Args: ${ssisInstall.Parameters.ShowAll.args}" />
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installIspacParametersShowAll.ps1" 
            scriptargs="${ssisInstall.Parameters.ShowAll.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target>
  
  <target name="ssisInstall.Parameters.Execute">
    
    <property name="ssisInstall.Parameters.args" value="-sqlConnectionString &quot;${ssisInstall.connectionString}&quot; -ProjectName &quot;${ssisInstall.projectName}&quot; -FolderName &quot;${ssisInstall.folderName}&quot; -name &quot;${ssisInstall.parameter.name}&quot; -value &quot;${ssisInstall.parameter.value}&quot;-value &quot;${ssisInstall.parameter.type}&quot;" />
    
    <echo message="ssisInstall.Parameters.Args: ${ssisInstall.Parameters.args}" />
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installIspacParameters.ps1" 
            scriptargs="${ssisInstall.Parameters.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target>
  
  
</project>

Final notes:

This process allows the automation of  builds and deploy of SSIS solutions. The orchestration of the Ruby\Rake and Nant application is made by Hudson Server.

King Regards,
Nuno Monteiro