Cartwheel.SSIS.psm1
|
Class SSIS { [string]$ProjectName [string]$ConfigName # build configuration to use [string]$ProjectDirectory # folder our .proj/.dtsx/.params is located [string]$OutputDirectory # place we want to output our ispac. typically /bin/debug or bin/release [string]$Version # everything below is hidden [string]$ProjectFile # set by our ctor | project file path [string]$ProjParamFile # set by our ctor [string]$ProjEnvFile # set by our ctor [System.Xml.xmlElement]$ProjectFileXmlData # set by our ctor | project node from dtproj [System.Xml.xmlElement]$BuildConfigXmlData # set by our ctor | specific configuration node we are using (debug|release) from dtproj [System.Xml.xmlElement]$ProjParamXmlData # set by our ctor [System.Xml.xmlElement]$ProjEnvXmlData # set by our ctor [string]$IspacPath [string]$BuildFilePath SSIS() { } hidden ApplyConfigs() { write-host "-----------------------------------------------------------------" write-host "APPLYING BUILD CONFIGURATIONS TO PACKAGE PARAMETERS" write-host "-----------------------------------------------------------------" $($this.ProjectFileXmlData).DeploymentModelSpecificContent.Manifest.Project.Packages.Package.Name | foreach { $packagenameWithExtension = $_ # get our packages from the metadata $package = ($($this.ProjectFileXmlData).DeploymentModelSpecificContent.Manifest.Project.DeploymentInfo.PackageInfo.PackageMetaData | where {$_.Name -eq $packagenameWithExtension}) write-host ">>" write-host "searching [$packagenameWithExtension] for configuration values..." $this.ApplyConfigsToPackage($package) } write-host "-----------------------------------------------------------------" write-host "" write-host "-----------------------------------------------------------------" write-host "APPLYING BUILD CONFIGURATIONS TO PROJECT PARAMETERS" write-host "-----------------------------------------------------------------" write-host "searching project.param for configuration values..." $this.ApplyConfigsToParams() write-host "-----------------------------------------------------------------" write-host "" } hidden ApplyConfigsToPackage([object] $package) { # get our parameters from our packages $packageparameters =$package.Parameters.Parameter $pacakgeNameNoExtension = $_.Replace(".dtsx","") #sub loop after getting our config values for this package $configValues = $($this.BuildConfigXmlData).Options.ParameterConfigurationValues.ConfigurationSetting | where {$_.Name.Split("::")[0] -eq $pacakgeNameNoExtension } if($configValues) { $configValues.Name.Split("::")[2] | foreach { $index = $_ write-host "configuration setting $index found for $pacakgeNameNoExtension..." $configValue = ($configValues | where {$_.Name.Split("::")[2] -eq $index}) $newValue = $configValue.Value."#text" write-host "setting $index value to $newValue..." $parameter = $packageparameters | where {$_.Name -eq $index} # single parameter ($parameter.Properties.Property | where {$_.Name -eq "Value"}).set_InnerXML((clean-xml $newValue)) # set value of this param } } else { write-host "no configuration settings found for $pacakgeNameNoExtension..." } } hidden ApplyConfigsToParams() { $paramparams = $($this.ProjParamXmlData).Parameter $configValues = $($this.BuildConfigXmlData).Options.ParameterConfigurationValues.ConfigurationSetting | where {$_.Name.Split("::")[0] -eq "Project" } if($configValues) { $configValues| foreach { $index = $_.Name.Split("::")[2] write-host "project parameter $index found..." $configValue = ($configValues | where {$_.Name.Split("::")[2] -eq $index}) $newValue = $configValue.Value."#text" write-host "setting $index value to $newValue..." $parameter = $paramparams | where {$_.Name -eq $index} # single parameter ($parameter.Properties.Property | where {$_.Name -eq "Value"}).set_InnerXML((clean-xml $newValue)) # set value of this param } } } hidden AddManifest() { write-host "-----------------------------------------------------------------" write-host "ADDING MANIFEST TO TEMP FOLDER" write-host "-----------------------------------------------------------------" format-xml $($this.ProjectFileXmlData).DeploymentModelSpecificContent.Manifest.InnerXml | Out-File -LiteralPath "$($this.OutputDirectory)\temp\@Project.manifest" -Encoding "UTF8" write-host "-----------------------------------------------------------------" write-host "" } hidden AddProjectParam() { write-host "-----------------------------------------------------------------" write-host "ADDING PROJECT PARAMS TO TEMP FOLDER" write-host "-----------------------------------------------------------------" format-xml $($this.ProjParamXmlData).OuterXml | Out-File -LiteralPath "$($this.OutputDirectory)\temp\Project.params" -Encoding "UTF8" write-host "-----------------------------------------------------------------" write-host "" } hidden AddContentTypes() { $data = "<?xml version=""1.0"" encoding=""utf-8""?><Types xmlns=""http://schemas.openxmlformats.org/package/2006/content-types""><Default Extension=""dtsx"" ContentType=""text/xml"" /><Default Extension=""conmgr"" ContentType=""text/xml"" /><Default Extension=""params"" ContentType=""text/xml"" /><Default Extension=""manifest"" ContentType=""text/xml"" /></Types>" write-host "-----------------------------------------------------------------" write-host "ADDING CONTENT TYPES TO TEMP FOLDER" write-host "-----------------------------------------------------------------" format-xml $data | Out-File -LiteralPath "$($this.OutputDirectory)\temp\[Content_Types].xml" -Encoding "UTF8" write-host "-----------------------------------------------------------------" } hidden AddPackages() { write-host "-----------------------------------------------------------------" write-host "ADDING PACKAGES TO TEMP FOLDER" write-host "-----------------------------------------------------------------" $($this.ProjectFileXmlData).DeploymentModelSpecificContent.Manifest.Project.Packages.Package.Name | foreach { write-host ">> $_" Copy-Item -Path "$($this.ProjectDirectory)\$_" -Destination "$($this.OutputDirectory)\temp\$($this.ReplaceSpecialChars($_))" �Recurse -Force -Confirm:$False } write-host "-----------------------------------------------------------------" } hidden AddConnectionManagers() { write-host "-----------------------------------------------------------------" write-host "ADDING CONNECTION MANAGERS TO TEMP FOLDER" write-host "-----------------------------------------------------------------" $($this.ProjectFileXmlData).DeploymentModelSpecificContent.Manifest.Project.ConnectionManagers.ConnectionManager.Name | foreach { write-host ">> $_" Copy-Item -Path "$($this.ProjectDirectory)\$_" -Destination "$($this.OutputDirectory)\temp\$($this.ReplaceSpecialChars($_))" �Recurse -Force -Confirm:$False } write-host "-----------------------------------------------------------------" } hidden AddIspac() { Add-Type -AssemblyName System.IO.Compression.FileSystem > $null $parent = Split-Path -Path "$($this.OutputDirectory)\temp" $ispac = "$parent\$($this.ProjectName).ispac" [io.compression.zipfile]::CreateFromDirectory("$($this.OutputDirectory)\temp", $ispac) #clean up Get-ChildItem "$($this.OutputDirectory)\temp" -Recurse -Force > $null Remove-Item -Recurse -Force "$($this.OutputDirectory)\temp" > $null $this.IspacPath = $ispac } BuildIspac([string]$ProjectDirectory,[string]$ConfigName) { $this.ProjectDirectory = $ProjectDirectory $this.ProjectFile = (Get-ChildItem -Path $this.ProjectDirectory -Filter "*.dtproj" -ErrorAction SilentlyContinue -Force).FullName $this.ProjectFileXmlData = ([xml](get-content $this.ProjectFile)).Project $this.ConfigName = $ConfigName $this.BuildConfigXmlData = $($this.ProjectFileXmlData).Configurations.Configuration | where {$_.Name -eq $this.ConfigName} $this.ProjParamFile = (Get-ChildItem -Path $this.ProjectDirectory -Filter "*.params" -ErrorAction SilentlyContinue -Force).FullName $this.ProjParamXmlData = ([xml](get-content $this.ProjParamFile)).Parameters If (Test-Path "$($this.ProjectDirectory)\Project.Environment"){ $this.ProjEnvFile = (Get-ChildItem -Path $this.ProjectDirectory -Filter "*.Environment" -ErrorAction SilentlyContinue -Force).FullName $this.ProjEnvXmlData = ([xml](get-content $this.ProjEnvFile)).Environments } $this.OutputDirectory = "$($this.ProjectDirectory)\$($this.BuildConfigXmlData.Options.OutputPath)\$($this.ConfigName)" $this.ProjectName = ($this.ProjectFileXmlData.DeploymentModelSpecificContent.Manifest.Project.Properties.Property | where {$_.Name -eq "Name"})."#text" $major = ($this.ProjectFileXmlData.DeploymentModelSpecificContent.Manifest.Project.Properties.Property | where {$_.Name -eq "VersionMajor"})."#text" $minor = ($this.ProjectFileXmlData.DeploymentModelSpecificContent.Manifest.Project.Properties.Property | where {$_.Name -eq "VersionMinor"})."#text" $build = ($this.ProjectFileXmlData.DeploymentModelSpecificContent.Manifest.Project.Properties.Property | where {$_.Name -eq "VersionBuild"})."#text" $this.Version = [System.Version]::Parse("$major.$minor.$build") ($this.ProjectFileXmlData.DeploymentModelSpecificContent.Manifest.Project.Properties.Property | where {$_.Name -eq "Description"}).set_InnerXML("v$($this.Version)") #Cartwheel.IO.Set-Directory method | rebuild by deleting folder then putting it back Set-Directory -DirectoryPath "$($this.OutputDirectory)" -Ensure "Absent" Set-Directory -DirectoryPath "$($this.OutputDirectory)\temp" -Ensure "Present" $this.ApplyConfigs() $this.AddManifest() $this.AddProjectParam() $this.AddContentTypes() $this.AddPackages() $this.AddConnectionManagers() $this.AddIspac() } hidden AddEnvironment() { # might need clean-xml here #($_ssis.ProjEnvXmlData.Environment | where {$_.Name -eq "Environment1"}).ConfigurationSettings.ConfigurationSetting.Name } DeployIspac( [string]$TargetServerName, [string]$CatalogFolder, [string]$IspacPath, [string]$ProjectName ) { write-host "-----------------------------------------------------------------" write-host "DEPLOYING TO SERVER $TargetServerName" write-host "-----------------------------------------------------------------" if ($IspacPath) { # TODO: Remove ProjectName. This needs to be derived from the deployment file since they have to match for deployment to succeed. Write-Host "Checking connectivity to server ..." # Local Variables $SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" # Load the IntegrationServices assembly $loadStatus = [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, "+ "Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL") # Create a connection to the server $sqlConnectionString = ` "Data Source=" + $TargetServerName + ";Initial Catalog=master;Integrated Security=SSPI;" $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString # Create the Integration Services object $integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection # Get the Integration Services catalog $catalog = $integrationServices.Catalogs["SSISDB"] $folder = $catalog.Folders[$CatalogFolder] if($folder) { Write-Host "Target folder exists ..." }else { Write-Host "Creating Catalog folder ..." # Create the target folder $folder = New-Object $SSISNamespace".CatalogFolder" ($catalog, $ProjectName, "Folder description") $folder.Create() } Write-Host "Deploying " $($this.ProjectName) " project ..." # Read the project file and deploy it [byte[]] $file = [System.IO.File]::ReadAllBytes($IspacPath) $folder.DeployProject("$ProjectName".Trim(), $file) Write-Host "Done." } write-host "-----------------------------------------------------------------" } <# helpers #> hidden [string] ReplaceSpecialChars([string] $string) { return $string.Replace(" ","%20") } } function Build-SSIS{ param([string]$ProjectDirectory,[string]$BuildConfigurationName) $_ssis = [SSIS]::new() $_ssis.BuildIspac($ProjectDirectory,$BuildConfigurationName); $_ssis.BuildFilePath = "$($_ssis.OutputDirectory)\build.xml" $_ssis | ConvertTo-Xml -as string | Set-Content -path $_ssis.BuildFilePath return $_ssis } function Deploy-SSIS{ param([string]$TargetServer,[string]$CatalogFolder,[string]$BuildFilePath) $ProjectName = ((([xml](get-content $BuildFilePath)).Objects.Object.Property) | where {$_.Name -eq "ProjectName"})."#text" $IspacPath = ((([xml](get-content $BuildFilePath)).Objects.Object.Property) | where {$_.Name -eq "IspacPath"})."#text" $_ssis = [SSIS]::new() $_ssis.DeployIspac($TargetServer,$CatalogFolder,$IspacPath,$ProjectName) } <# $ProjectDirectory = "C:\Users\derek.mckee\Source\Repos\Cartwheel.SSIS.UnitTesting\Cartwheel.SSIS.UnitTesting" Import-Module Cartwheel.SSIS $out = Build-SSIS -ProjectDirectory $ProjectDirectory -BuildConfigurationName "debug" Deploy-SSIS -TargetServer ".\SQL_2017" -CatalogFolder "UnitTest" -BuildFilePath $out.BuildFilePath #> |