Cartwheel.SSIS.psm1
|
<# HELPERS #> function Format-XML ([xml] $xml, $indent=2){ $StringWriter = New-Object System.IO.StringWriter $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter $xmlWriter.Formatting = �indented� $xmlWriter.Indentation = $Indent $xml.WriteContentTo($XmlWriter) $XmlWriter.Flush() $StringWriter.Flush() return $StringWriter.ToString() } function Clean-XML{ [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$line ) $lookupTable = @{ '"' = '"' '''' = ''' '<' = '<' '>' = '>' '&' = '&' } $lookupTable.GetEnumerator() | ForEach-Object { if ($line -match $_.Key) { $line = $line -replace $_.Key, $_.Value } } return $line } <# GETS #> function Get-ProjectFolders{ [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$SourceFolder # this is our vsts source location ) $Folders = New-Object System.Collections.ArrayList $projects = Get-ChildItem -Path $SourceFolder -Filter "*.dtproj" -ErrorAction SilentlyContinue -Force -Recurse |? { $_.FullName -notmatch '\\obj\\|\\bin\\' } foreach($project in $projects) { $a =Split-Path -Path $project.FullName $Folders.Add($a) > $null } return $Folders } function Get-Configuration{ # Currently only supports project configs and standard folder layouts [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$ConfigurationValue ) # Grab our file $ProjectFile = Get-ChildItem -Path $ProjectDirectory -Filter "*.dtproj" -ErrorAction SilentlyContinue -Force $item = $ProjectFile.FullName [xml]$xmldata = get-content $item $ParameterConfiguration = $xmldata.Project.Configurations.Configuration # get our established config that we are building $configuration = $ParameterConfiguration | where {$_.Name -eq $ConfigurationValue} #dev,debug,prod,etc $dict = @{} # loop our entries and create foreach($entry in $configuration.Options.ParameterConfigurationValues.ConfigurationSetting) { $entryName = $entry.Name $valueproperty=$entry.Value $dict.Add($entryName,$valueproperty."#Text") } return $dict } <# ADDS #> function Add-Manifest { [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$ProjectConfiguration ) Add-Type -AssemblyName System.IO # create return object $info = @{ "Project"=$ProjectDirectory "FileLocation" = ""; "XML" ="" ; "Error" = ""; "OutDir" = "" "OutFile" = "" } #find the project file in here $ProjectFile = Get-ChildItem -Path $ProjectDirectory -Filter "*.dtproj" -ErrorAction SilentlyContinue -Force $item = $ProjectFile.FullName $info.FileLocation = $item # if we didn't get an error trying to find the file... if(!$ItemErr){ [xml]$xmldata = get-content $item [System.Xml.XmlElement]$manifest = $xmldata.Project.DeploymentModelSpecificContent.Manifest # update for project name bug $ProjectNode = $manifest.Project.Properties.Property | where {$_.Name -eq 'Name'} $newname = [System.IO.Path]::GetFileNameWithoutExtension($item) $ProjectNode.set_InnerXML($newname) $info.XML = Format-XML $manifest.InnerXml $OutputDirectory = "$ProjectDirectory\bin\$ProjectConfiguration\ispac" $binpath = "$ProjectDirectory\bin\" $info.OutDir = $OutputDirectory $testPath = Test-Path -PathType Container -path $binpath -ErrorVariable ItemErr -ErrorAction SilentlyContinue ; if($testPath) { Get-ChildItem $binpath -Recurse -Force Remove-Item -Recurse -Force $binpath } New-Item -ItemType Directory -Force -Path $OutputDirectory $info.OutFile = "$OutputDirectory\@Project.manifest" $info.XML | Out-File $info.OutFile return $info } else { $info.Error = "err: $ItemErr" return $info } #> return $info } function Add-Packages{ [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$Bin ) # Add-Manifest must be run first, we expect a clear bin dir from this (Re-Build ALL) $PackageFiles = Get-ChildItem -Path $ProjectDirectory -Filter "*.dtsx" -ErrorAction SilentlyContinue -Force -Recurse foreach($PackageFile in $PackageFiles) { $item = $PackageFile.FullName Copy-Item -Path $item -Destination $Bin �Recurse -Force -Confirm:$False } } function Add-ConnectionManager{ [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$Bin ) # Add-Manifest must be run first, we expect a clear bin dir from this (Re-Build ALL) $PackageFiles = Get-ChildItem -Path $ProjectDirectory -Filter "*.conmgr" -ErrorAction SilentlyContinue -Force -Recurse foreach($PackageFile in $PackageFiles) { $item = $PackageFile.FullName Copy-Item -Path $item -Destination $Bin �Recurse -Force -Confirm:$False } } function Add-Parameters{ [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$Bin, [Parameter(Mandatory = $true)] [string]$ConfigurationValue ) $configuration = Get-Configuration $ProjectDirectory $ConfigurationValue # Add-Manifest must be run first, we expect a clear bin dir from this (Re-Build ALL) $PackageFiles = Get-ChildItem -Path $ProjectDirectory -Filter "*.params" -ErrorAction SilentlyContinue -Force -Recurse $item = $PackageFiles[0].FullName # there will only ever be one of these # this section is a find/replace for the config value from the project file into the parms file [xml]$xmldata = get-content $item $configurations = $xmldata.Parameters.Parameter foreach($entry in $configurations) { $lookup="" $entryName = $entry.Name $valueproperty=$entry.Properties.Property | where {$_.Name -eq 'Value'} $lookup = $configuration["Project::$entryName"] if($lookup -ne $null) { $newvalue = Clean-XML $lookup $valueproperty.set_InnerXML($newvalue) } } # end seciton # now copy the item over $out = format-XML $xmldata $out| Out-File "$Bin\Project.params" #Copy-Item -Path $item -Destination $Bin �Recurse -Force -Confirm:$False } function Add-ContentTypes{ [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$Bin ) $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>" $data | Out-File -LiteralPath "$Bin\[Content_Types].xml" -Encoding "UTF8" } function Add-Ispac{ [CmdletBinding()] param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$Bin ) Add-Type -AssemblyName System.IO.Compression.FileSystem $parent = Split-Path -Path $Bin $name = Split-Path -Path $ProjectDirectory -leaf [io.compression.zipfile]::CreateFromDirectory($Bin, "$parent\$name.ispac") #clean up Get-ChildItem $Bin -Recurse -Force Remove-Item -Recurse -Force $Bin } <# ::: :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: ::: #> function Build-SSIS{ param( [Parameter(Mandatory = $true)] [string]$ProjectDirectory, [Parameter(Mandatory = $true)] [string]$ConfigurationValue ) Write-Host "Discovering projects ..." $projects = Get-ProjectFolders $ProjectDirectory #"C:\Users\derek.mckee\Documents\ETLFramework" foreach($project in $projects) { Write-Host "Found project: $project" Write-Host "Writing manifest ..." $manifest = Add-Manifest $project $ConfigurationValue Write-Host "Writing packages ..." Add-Packages $project $manifest.OutDir Write-Host "Writing connection managers ..." Add-ConnectionManager $project $manifest.OutDir Write-Host "Writing parameters ..." Add-Parameters $project $manifest.OutDir $ConfigurationValue Write-Host "Writing content types ..." Add-ContentTypes $project $manifest.OutDir Write-Host "Packing ispac ..." Add-Ispac $project $manifest.OutDir } } function Deploy-SSIS{ Param( [string]$TargetServerName, [string]$IspacPath, [string]$CatalogFolder, [string]$ProjectName ) Write-Host "Checking connectivity to server ..." # Local Variables $SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" $ProjectFilePath = $IspacPath # 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, $TargetFolderName, "Folder description") $folder.Create() } Write-Host "Deploying " $ProjectName " project ..." # Read the project file and deploy it [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath) $folder.DeployProject($ProjectName, $projectFile) Write-Host "Done." } # SSIS function Get-SSISInstance { [CmdletBinding()] param() $Win32Name= "msdts*" $Win32Path= "MsDtsSrvr.exe" $Instances = Get-WmiObject win32_service -computerName localhost | Where-Object { $_.Name -match $Win32Name -and $_.PathName -match $Win32Path } return $Instances | Select Name,State } <# # EXAMPLE USAGE [string]$SourceFolder = "C:\Users\derek.mckee\Documents\ETLFramework" [string]$ConfigurationValue = "production" Build-SSIS $SourceFolder $ConfigurationValue [string]$TargetServerName=".\SQL_2017" [string]$IspacPath="C:\Users\derek.mckee\Documents\ETLFramework\InfoMgmt DevOps Framework\EDW.SSIS.ETLEngine\bin\$ConfigurationValue\EDW.SSIS.ETLEngine.ispac" [string]$DeploymentFolder="ETLEngine" [string]$ProjectName= "EDW.SSIS.ETLEngine" Deploy-SSIS $TargetServerName $IspacPath $DeploymentFolder $ProjectName #> |