functions/Invoke-ImpactAnalysis.ps1
|
function Invoke-ImpactAnalysis { [CmdletBinding()] [OutputType([PSObject])] param ( [Parameter(Mandatory = $True)] [string]$Server, [Parameter(Mandatory = $False)] [string]$ConfigPath = ".\_impactConfig.json", [Parameter(Mandatory = $False)] [string]$OutDir = ".\_impact" ) begin { $Msg = "Impact analysis [$($Server)]"; Write-Host $Msg -ForegroundColor Magenta; Write-Verbose $Msg; Write-Log $Msg; if (!(Test-Path $ConfigPath)) { $Msg = "$(" " * 4)Unable to find configuration file in current directory or specified path"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Do { $TemplateFlag = Read-Host -Prompt 'Create a config template? (Y/N)' } while ('y', 'n' -notcontains $TemplateFlag) if ($TemplateFlag -eq 'y') { New-EmptyFile './_impactConfig.json'; Add-Content ./_impactConfig.json "{`n ""Columns"": {`n ""SQL"": {`n ""Connection"": {`n ""Database"": ""<database>""`n },`n ""FilePath"": ""./columns.sql""`n }`n },`n ""Queries"": {`n ""SQL"": {`n ""Connection"": {`n ""Database"": ""<database>""`n },`n ""FilePath"": ""./queries.sql""`n }`n },`n ""Mappings"": {`n ""CSV"": {`n ""FilePath"": ""./mappings.csv""`n }`n }`n}"; New-EmptyFile './columns.sql'; Add-Content ./columns.sql "SELECT`n /******REQUIRED******/`n tbl.DatabaseNM`n ,tbl.SchemaNM`n ,tbl.TableNM`n ,col.ColumnNM`n /********************/`n /* ADD ANY OTHER GROUPERS YOU NEED`n ,Grouper1NM?`n ,Grouper2NM?`n */`nFROM CatalystAdmin.TableBASE AS tbl`nINNER JOIN CatalystAdmin.DatamartBASE AS dm`n ON dm.DatamartID = tbl.DatamartID`nINNER JOIN CatalystAdmin.ColumnBASE AS col`n ON col.TableID = tbl.TableID`n AND col.IsSystemColumnFLG = 'N'`nWHERE dm.DatamartNM = '<MY_DATAMART>'`n AND tbl.PublicFLG = 1;" New-EmptyFile './mappings.csv'; '' | Select-Object FromDatabaseNM, FromSchemaNM, FromTableNM, FromColumnNM, ToDatabaseNM, ToSchemaNM, ToTableNM, ToColumnNM | Export-Csv './mappings.csv' -NoTypeInformation New-EmptyFile './queries.sql'; Add-Content ./queries.sql "SELECT`n /******REQUIRED******/`n obj.AttributeValueLongTXT AS QueryTXT`n /********************/`n /* ADD ANY OTHER GROUPERS YOU NEED`n ,tbl.ViewNM+' ('+b.BindingNM+')' AS QueryNM`n ,'SAM Designer' AS Grouper1NM`n ,dm.DatamartNM AS Grouper2NM`n */`nFROM CatalystAdmin.ObjectAttributeBASE AS obj`nINNER JOIN CatalystAdmin.BindingBASE AS b`n ON b.BindingID = obj.ObjectID`nINNER JOIN CatalystAdmin.TableBASE AS tbl`n ON tbl.TableID = b.DestinationEntityID`nINNER JOIN CatalystAdmin.DataMartBASE AS dm`n ON dm.DatamartID = tbl.DatamartID`nWHERE obj.ObjectTypeCD = 'Binding'`n AND obj.AttributeNM = 'UserDefinedSQL'`n AND b.BindingClassificationCD != 'SourceMart'`n AND LEN(obj.AttributeValueLongTXT) > 0`n AND tbl.TableID NOT IN`n(`n SELECT`n tbl.TableID`n FROM CatalystAdmin.TableBASE AS tbl`n INNER JOIN CatalystAdmin.DatamartBASE AS dm`n ON dm.DatamartID = tbl.DatamartID`n WHERE dm.DatamartNM = '<MY_DATAMART>'`n);" $Msg = "Configuration files created, rerun when you are ready.`r`n"; Write-Host $Msg -ForegroundColor Green; Write-Verbose $Msg; Write-Log $Msg; } Break; } else { $Msg = "$(" " * 4)Creating output directory..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; try { if (Test-Path $OutDir) { Remove-Item $OutDir -Recurse -Force | Out-Null } New-Item -ItemType Directory -Force -Path $OutDir -ErrorAction Stop | Out-Null $Msg = "$(" " * 8)Created ""$(Split-Path $OutDir -Leaf)"" directory"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; New-Item -ItemType Directory -Force -Path "$($OutDir)/raw/csv" -ErrorAction Stop | Out-Null $Msg = "$(" " * 8)Created ""$(Split-Path $OutDir -Leaf)/raw/csv"" directory"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; New-Item -ItemType Directory -Force -Path "$($OutDir)/raw/json" -ErrorAction Stop | Out-Null $Msg = "$(" " * 8)Created ""$(Split-Path $OutDir -Leaf)/raw/json"" directory"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 4)Unable to create output directory (""$(Split-Path $OutDir -Leaf)"" or ""$(Split-Path $OutDir -Leaf)/raw"")"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $Config = Get-Content $ConfigPath | ConvertFrom-Json $Properties = ($Config | Get-Member | Where-Object MemberType -eq NoteProperty).Name if (!($Properties -contains 'Columns' -and $Properties -contains 'Queries')) { $Msg = "$(" " * 8)Configruation file (""$(Split-Path $ConfigPath -Leaf)"") must contain all of the the following properies: Columns, Queries"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $MappingsFlag = $False; if ($Properties -contains 'Mappings') { $MappingsFlag = $True; } $Msg = "$(" " * 4)Getting data..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; #region Columns (REQUIRED) $ColumnsPath = $Config.Columns.SQL.FilePath if (!(Test-Path $ColumnsPath)) { $Msg = "$(" " * 4)Unable to find ""$(Split-Path $ColumnsPath -Leaf)"" specified in the configuration file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } else { try { $ColumnsSQL = Get-Content $ColumnsPath | Out-String try { $ColumnsDb = $Config.Columns.SQL.Connection.Database if (!($ColumnsDb)) { $Columns = Invoke-Sqlcmd -Query $ColumnsSQL -ServerInstance $Server } else { $Columns = Invoke-Sqlcmd -Query $ColumnsSQL -ServerInstance $Server -Database $ColumnsDb } $Msg = "$(" " * 8)$(($Columns | Measure-Object).Count) records from query ""$(Split-Path $ColumnsPath -Leaf)"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $Properties = ($Columns[0] | Get-Member | Where-Object MemberType -eq Property).Name if (!($Properties.ToLower() -contains 'databasenm' -and $Properties.ToLower() -contains 'schemanm' -and $Properties.ToLower() -contains 'tablenm' -and $Properties.ToLower() -contains 'columnnm')) { $Msg = "$(" " * 8)Sql query must contain the following columns: DatabaseNM, SchemaNM, TableNM, and ColumnNM"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $UpdatedColumns = @() $I = 0 foreach ($Column in $Columns) { $Fqn = "$($Column.DatabaseNM.ToLower()).$($Column.SchemaNM.ToLower()).$($Column.TableNM.ToLower() -replace 'base$', '').$($Column.ColumnNM.ToLower())" $UpdatedColumn = New-Object PSObject $UpdatedColumn | Add-Member -Type NoteProperty -Name `$ColumnId -Value $I.ToString() $UpdatedColumn | Add-Member -Type NoteProperty -Name `$Fqn -Value $Fqn $UpdatedColumn | Add-Member -Type NoteProperty -Name `$Queries -Value @() if ($MappingsFlag) { $UpdatedColumn | Add-Member -Type NoteProperty -Name `$Mappings -Value @() } foreach ($Property in $Properties) { $UpdatedColumn | Add-Member -Type NoteProperty -Name $Property -Value $Column.$Property } $UpdatedColumns += $UpdatedColumn $I++ } $Columns = $UpdatedColumns; } catch { $Msg = "$(" " * 8)Unable to establish a connection to db or execute query"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 8)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } catch { $Msg = "$(" " * 4)Unable to get the contents of the ""$(Split-Path $Config.Queries.SQL.FilePath -Leaf)"" file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } } #endregion #region Queries (REQUIRED) $QueriesPath = $Config.Queries.SQL.FilePath if (!(Test-Path $QueriesPath)) { $Msg = "$(" " * 4)Unable to find ""$(Split-Path $QueriesPath -Leaf)"" specified in the configuration file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } else { try { $QueriesSQL = Get-Content $QueriesPath | Out-String try { $QueriesDb = $Config.Queries.SQL.Connection.Database if (!($QueriesDb)) { $Queries = Invoke-Sqlcmd -Query $QueriesSQL -ServerInstance $Server -MaxCharLength 8000000 } else { $Queries = Invoke-Sqlcmd -Query $QueriesSQL -ServerInstance $Server -Database $QueriesDb -MaxCharLength 8000000 } $Msg = "$(" " * 8)$(($Queries | Measure-Object).Count) records from query ""$(Split-Path $QueriesPath -Leaf)"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $Properties = ($Queries[0] | Get-Member | Where-Object MemberType -eq Property).Name if (!($Properties.ToLower() -contains 'querytxt')) { $Msg = "$(" " * 8)Sql query must contain the following column: QueryTXT"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $I = 0 $UpdatedQueries = @() foreach ($Query in $Queries) { $UpdatedQuery = New-Object PSObject $UpdatedQuery | Add-Member -Type NoteProperty -Name `$QueryId -Value $I.ToString() $UpdatedQuery | Add-Member -Type NoteProperty -Name `$Query -Value $Query.querytxt $UpdatedQuery | Add-Member -Type NoteProperty -Name `$Columns -Value @() foreach ($Property in $Properties | Where-Object { $_.ToLower() -ne 'querytxt' }) { $UpdatedQuery | Add-Member -Type NoteProperty -Name $Property -Value $Query.$Property } $UpdatedQueries += $UpdatedQuery $I++ } $Queries = $UpdatedQueries; } catch { $Msg = "$(" " * 8)Unable to establish a connection to db or execute query"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 8)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } catch { $Msg = "$(" " * 4)Unable to get the contents of the ""$(Split-Path $Config.Queries.SQL.FilePath -Leaf)"" file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } } #endregion #region Mappings (OPTIONAL) if ($MappingsFlag) { $MappingsPath = $Config.Mappings.CSV.FilePath if (!(Test-Path $MappingsPath)) { $Msg = "$(" " * 4)Unable to find ""$(Split-Path $MappingsPath -Leaf)"" specified in the configuration file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } else { try { $Mappings = Get-Content $MappingsPath | ConvertFrom-Csv } catch { $Msg = "$(" " * 4)Unable to parse the contents of the ""$(Split-Path $Config.Mappings.CSV.FilePath -Leaf)"" file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $Msg = "$(" " * 8)$(($Mappings | Measure-Object).Count) records from csv ""$(Split-Path $MappingsPath -Leaf)"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $Properties = ($Mappings[0] | Get-Member | Where-Object MemberType -eq NoteProperty).Name if (!($Properties.ToLower() -contains 'fromdatabasenm' -and $Properties.ToLower() -contains 'fromschemanm' -and $Properties.ToLower() -contains 'fromtablenm' -and $Properties.ToLower() -contains 'fromcolumnnm' -and ` $Properties.ToLower() -contains 'todatabasenm' -and $Properties.ToLower() -contains 'toschemanm' -and $Properties.ToLower() -contains 'totablenm' -and $Properties.ToLower() -contains 'tocolumnnm')) { $Msg = "$(" " * 8)Csv file must contain the following columns: FromDatabaseNM, FromSchemaNM, FromTableNM, FromColumnNM, ToDatabaseNM, ToSchemaNM, ToTableNM, ToColumnNM"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } foreach ($Mapping in $Mappings) { $Fqn = "$($Mapping.FromDatabaseNM.ToLower()).$($Mapping.FromSchemaNM.ToLower()).$($Mapping.FromTableNM.ToLower() -replace 'base$', '').$($Mapping.FromColumnNM.ToLower())" $Index = $Columns.'$Fqn'.indexOf($Fqn) if ($Index -gt -1) { $AddMapping = New-Object PSObject $AddMapping | Add-Member -Type NoteProperty -Name ToDatabaseNM -Value $Mapping.ToDatabaseNM $AddMapping | Add-Member -Type NoteProperty -Name ToSchemaNM -Value $Mapping.ToSchemaNM $AddMapping | Add-Member -Type NoteProperty -Name ToTableNM -Value $Mapping.ToTableNM $AddMapping | Add-Member -Type NoteProperty -Name ToColumnNM -Value $Mapping.ToColumnNM $Columns[$Index].'$Mappings' += $AddMapping } } $Msg = "$(" " * 8)$(($Columns.'$Mappings' | Measure-Object).Count) columns assigned mappings"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $MappingsFlag = $Columns | Where-Object { ($_.'$Mappings' | Measure-Object).Count -eq 0 } | Select-Object @{ n = 'FromDatabaseNM'; e = { $_.DatabaseNM } }, @{ n = 'FromSchemaNM'; e = { $_.SchemaNM } }, @{ n = 'FromTableNM'; e = { $_.TableNM } }, @{ n = 'FromColumnNM'; e = { $_.ColumnNM } }, ToDatabaseNM, ToSchemaNM, ToTableNM, ToColumnNM } } #endregion } catch { $Msg = "$(" " * 4)Unable to parse the contents of ""$(Split-Path $ConfigPath -Leaf)"""; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 4)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } } } process { $Msg = "$(" " * 4)Parsing queries..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; $ScriptStart = (Get-Date) $DataColumns = $Columns if ($MappingsFlag) { $DataMappings = @() foreach ($Column in $Columns) { foreach ($Mapping in $Column.'$Mappings') { $ColumnMapping = New-Object PSObject $ColumnMapping | Add-Member -Type NoteProperty -Name `$ColumnId -Value $Column.'$ColumnId' $ColumnMapping | Add-Member -Type NoteProperty -Name ToDatabaseNM -Value $Mapping.ToDatabaseNM $ColumnMapping | Add-Member -Type NoteProperty -Name ToSchemaNM -Value $Mapping.ToSchemaNM $ColumnMapping | Add-Member -Type NoteProperty -Name ToTableNM -Value $Mapping.ToTableNM $ColumnMapping | Add-Member -Type NoteProperty -Name ToColumnNM -Value $Mapping.ToColumnNM $DataMappings += $ColumnMapping } } } try { $I = 0; $J = 0; $Total = ($Queries | Measure-Object).Count; $DataQueriesToColumns = @() $DataQueries = @() foreach ($Query in $Queries) { if ($I -eq 0) { $Msg = "$(" " * 8)$(("{0:P0}" -f ($J/$Total)).PadLeft(5)) $($J.ToString().PadLeft($Total.ToString().Length))/$($Total) ...parsing..."; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } $Q = $True; $ParsedTables = $(Invoke-SqlParser -Query $Query.'$Query' -Log $False -SelectStar $False -Brackets $False) foreach ($ParsedTable in $ParsedTables) { foreach ($ParsedColumn in $ParsedTable.Columns) { $Fqn = "$($ParsedTable.FullyQualifiedNM.ToLower() -replace 'base$', '').$($ParsedColumn.ColumnNM.ToLower())" $Index = $DataColumns.'$Fqn'.indexOf($Fqn) if ($Index -gt -1) { $Match = New-Object PSObject $Match | Add-Member -Type NoteProperty -Name `$QueryId -Value $Query.'$QueryId' $Match | Add-Member -Type NoteProperty -Name `$ColumnId -Value $DataColumns[$Index].'$ColumnId' $DataQueriesToColumns += $Match if ($Q) { $DataQueries += $Query; $Q = $False; } } } } $I++; $J++; if ($I -eq 100) { $I = 0; } } $ScriptEnd = (Get-Date) $RunTime = New-Timespan -Start $ScriptStart -End $ScriptEnd $Msg = "$(" " * 8)$(("{0:P0}" -f ($Total/$Total)).PadLeft(5)) $($Total)/$($Total) Done ~ $("Elapsed Time: {0}:{1}:{2}" -f $RunTime.Hours, $Runtime.Minutes, $RunTime.Seconds)"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)An error occurred during query parsing"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 8)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } foreach ($Column in $DataQueriesToColumns) { $IXColumn = $DataColumns.'$ColumnId'.IndexOf($Column.'$ColumnId') $IXQuery = $DataQueries.'$QueryId'.IndexOf($Column.'$QueryId') $QueryObj = New-Object PSObject; $QueryObj | Add-Member -Type NoteProperty -Name '$QueryId' -Value $DataQueries[$IXQuery].'$QueryId'; $ColumnObj = New-Object PSObject; $ColumnObj | Add-Member -Type NoteProperty -Name '$ColumnId' -Value $DataColumns[$IXColumn].'$ColumnId'; $DataColumns[$IXColumn].'$Queries' += $QueryObj; $DataQueries[$IXQuery].'$Columns' += $ColumnObj; } #region CSV files $Msg = "$(" " * 4)Creating output csv files..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; try { $DataColumns | Select-Object * -ExcludeProperty '$Mappings', '$Queries' | Export-Csv -Path "$($OutDir)/raw/csv/columns.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/columns.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/columns.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $DataQueries | Select-Object * -ExcludeProperty '$Query', '$Columns' | Export-Csv -Path "$($OutDir)/raw/csv/queries.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/queries.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/queries.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $DataQueriesToColumns | Export-Csv -Path "$($OutDir)/raw/csv/queries-to-columns.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/queries-to-columns.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/queries-to-columns.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } if ($MappingsFlag) { try { $DataMappings | Export-Csv -Path "$($OutDir)/raw/csv/mappings.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/mappings.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/mappings.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $MappingsFlag | Export-Csv -Path $OutDir/raw/csv/unmapped.csv -NoTypeInformation $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/unmapped.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/unmapped.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } #endregion #region JSON files $Msg = "$(" " * 4)Creating output json files..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; try { $DataColumns | ConvertTo-Json -Depth 100 -Compress | Out-File "$($OutDir)/raw/json/columns.json" -Encoding Default -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/json/columns.json"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/json/columns.json"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $DataQueries | ConvertTo-Json -Depth 100 -Compress | Out-File "$($OutDir)/raw/json/queries.json" -Encoding Default -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/json/queries.json"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/json/queries.json"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } if ($MappingsFlag) { try { $MappingsFlag | ConvertTo-Json -Depth 100 -Compress | Out-File $OutDir/raw/json/unmapped.json -Encoding Default -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/json/unmapped.json"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/json/unmapped.json"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } #endregion } end { $Msg = "Success!`r`n"; Write-Host $Msg -ForegroundColor Green; Write-Verbose $Msg; Write-Log $Msg; } } # SIG # Begin signature block # MIIaxgYJKoZIhvcNAQcCoIIatzCCGrMCAQExDzANBglghkgBZQMEAgEFADB5Bgor # BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG # KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCDLDDAJ/MtODX4k # p5y9SjGJlSobumHbL05KTnTR9jOH6aCCCqMwggUwMIIEGKADAgECAhAECRgbX9W7 # ZnVTQ7VvlVAIMA0GCSqGSIb3DQEBCwUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQK # EwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNV # BAMTG0RpZ2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0xMzEwMjIxMjAwMDBa # Fw0yODEwMjIxMjAwMDBaMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2Vy # dCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lD # ZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwggEiMA0GCSqGSIb3 # DQEBAQUAA4IBDwAwggEKAoIBAQD407Mcfw4Rr2d3B9MLMUkZz9D7RZmxOttE9X/l # qJ3bMtdx6nadBS63j/qSQ8Cl+YnUNxnXtqrwnIal2CWsDnkoOn7p0WfTxvspJ8fT # eyOU5JEjlpB3gvmhhCNmElQzUHSxKCa7JGnCwlLyFGeKiUXULaGj6YgsIJWuHEqH # CN8M9eJNYBi+qsSyrnAxZjNxPqxwoqvOf+l8y5Kh5TsxHM/q8grkV7tKtel05iv+ # bMt+dDk2DZDv5LVOpKnqagqrhPOsZ061xPeM0SAlI+sIZD5SlsHyDxL0xY4PwaLo # LFH3c7y9hbFig3NBggfkOItqcyDQD2RzPJ6fpjOp/RnfJZPRAgMBAAGjggHNMIIB # yTASBgNVHRMBAf8ECDAGAQH/AgEAMA4GA1UdDwEB/wQEAwIBhjATBgNVHSUEDDAK # BggrBgEFBQcDAzB5BggrBgEFBQcBAQRtMGswJAYIKwYBBQUHMAGGGGh0dHA6Ly9v # Y3NwLmRpZ2ljZXJ0LmNvbTBDBggrBgEFBQcwAoY3aHR0cDovL2NhY2VydHMuZGln # aWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENBLmNydDCBgQYDVR0fBHow # eDA6oDigNoY0aHR0cDovL2NybDQuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJl # ZElEUm9vdENBLmNybDA6oDigNoY0aHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0Rp # Z2lDZXJ0QXNzdXJlZElEUm9vdENBLmNybDBPBgNVHSAESDBGMDgGCmCGSAGG/WwA # AgQwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAK # BghghkgBhv1sAzAdBgNVHQ4EFgQUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHwYDVR0j # BBgwFoAUReuir/SSy4IxLVGLp6chnfNtyA8wDQYJKoZIhvcNAQELBQADggEBAD7s # DVoks/Mi0RXILHwlKXaoHV0cLToaxO8wYdd+C2D9wz0PxK+L/e8q3yBVN7Dh9tGS # dQ9RtG6ljlriXiSBThCk7j9xjmMOE0ut119EefM2FAaK95xGTlz/kLEbBw6RFfu6 # r7VRwo0kriTGxycqoSkoGjpxKAI8LpGjwCUR4pwUR6F6aGivm6dcIFzZcbEMj7uo # +MUSaJ/PQMtARKUT8OZkDCUIQjKyNookAv4vcn4c10lFluhZHen6dGRrsutmQ9qz # sIzV6Q3d9gEgzpkxYz0IGhizgZtPxpMQBvwHgfqL2vmCSfdibqFT+hKUGIUukpHq # aGxEMrJmoecYpJpkUe8wggVrMIIEU6ADAgECAhAMMCpTLsjxo9FR9hag8ePUMA0G # CSqGSIb3DQEBCwUAMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJ # bmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0 # IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwHhcNMjAwMzMxMDAwMDAw # WhcNMjMwNTEwMTIwMDAwWjCBpzELMAkGA1UEBhMCVVMxDTALBgNVBAgTBFV0YWgx # FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVIZWFsdGggQ2F0YWx5 # c3QsIEluYy4xHjAcBgNVBAMTFUhlYWx0aCBDYXRhbHlzdCwgSW5jLjEwMC4GCSqG # SIb3DQEJARYhYWRtaW5uaXN0cmF0b3JAaGVhbHRoY2F0YWx5c3QuY29tMIIBIjAN # BgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA2fY0HWdxDJezDOsbHp7f9u/lrrD5 # nuZ1mENMgvixlrtC/KXgBRXlcWH7ajIOKljKnWCSAZwlZy4nFGbMagKmMzohXUXg # xo94u5nCdiBa/kgPazNGpL0AyGgX2VARMbcpm8Gdy+/uH3Kc7L91lcoGZVVBnVIt # 1oj5iXURqmhL83TrMyYqyj3XOH0So8Y10FVLPSukocMzMqBIRgvn/7EP0iWtOjXx # +o1wB5Ql+z9G3NCqF6CKE/Pn355XYbbmjF7BPzKoOjocHO6VU2uEflJWq1ZFb0QY # /tAosyyLYi9kFfO1damtJfRbbsVqavwg2UeQkzhg9CpB6eSsmBXPlFHudQIDAQAB # o4IBxTCCAcEwHwYDVR0jBBgwFoAUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHQYDVR0O # BBYEFFjfHOOIre2C4m9NCk8TFJlDwMxUMA4GA1UdDwEB/wQEAwIHgDATBgNVHSUE # DDAKBggrBgEFBQcDAzB3BgNVHR8EcDBuMDWgM6Axhi9odHRwOi8vY3JsMy5kaWdp # Y2VydC5jb20vc2hhMi1hc3N1cmVkLWNzLWcxLmNybDA1oDOgMYYvaHR0cDovL2Ny # bDQuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJlZC1jcy1nMS5jcmwwTAYDVR0gBEUw # QzA3BglghkgBhv1sAwEwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNl # cnQuY29tL0NQUzAIBgZngQwBBAEwgYQGCCsGAQUFBwEBBHgwdjAkBggrBgEFBQcw # AYYYaHR0cDovL29jc3AuZGlnaWNlcnQuY29tME4GCCsGAQUFBzAChkJodHRwOi8v # Y2FjZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRTSEEyQXNzdXJlZElEQ29kZVNp # Z25pbmdDQS5jcnQwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQsFAAOCAQEAsBxn # 9yJAQi+9cJPZpJvOEV6iHaOBGv8898wNJCc4eB5g8WPziEY70GZVeqEdx3z0wS8U # QQIr19Hkju2NFZjDtzB9z1jAc/9EgqFGoCZbPijv1EYAa2oOVAp1BPbLjqBSdXqu # 2mzqo14CJ30oNom9ep9F6LGZ5zEoPsMrJejSbJGr4EacrksX8C8qeFklc7FzwiGk # GX7IQxidrrhOm2fOvGGAAxnvNYAR0FqJK0LiWWPSt5R/j63H/6HQtqD2sLevI3+O # bRP74TPchDobFmWlSogX9oB63E7fsbDAqecY0cRPQ6tVWK53Ke2sB514nahFjZDa # mxsa3/acZWL659ly3jGCD3kwgg91AgEBMIGGMHIxCzAJBgNVBAYTAlVTMRUwEwYD # VQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAv # BgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EC # EAwwKlMuyPGj0VH2FqDx49QwDQYJYIZIAWUDBAIBBQCgfDAQBgorBgEEAYI3AgEM # MQIwADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAcBgorBgEEAYI3AgELMQ4w # DAYKKwYBBAGCNwIBFTAvBgkqhkiG9w0BCQQxIgQgEAPQqLWzidK+TPbXB9kbT+7q # xvoecsXvt2QwLCEaofcwDQYJKoZIhvcNAQEBBQAEggEAmMPPwse/ReMQlhh7zA8Z # fJCwO78DuZUuhah7qtWJSUG38ybMPXU56lYBmXiaz7h5+c4w9ilDWxFsQYY0GKc/ # EfeZtZeLZNzMyv9utspoJzOtaBnnTMWmeX9KDbdVIjLdlKBZPbxPuQvHapKHMoKL # fjxTnmoUxNnCIDwRiCVmR+OkCL1u5uusXqtVZ2uM4NSVLtAKgbvKIPiY1cMXTviC # q3+lkU8PeEfztCoc3rwqFuZPteO3qPCdfEYxrAhP72BQVnP9wxljrqiPyYZDYdyO # JwVFcgTI8GEDonPmlc1IFwBuRqNM26nBLdG9A9nUKl7ztFFtnv4CXY4GM7DQMX/o # 2KGCDUUwgg1BBgorBgEEAYI3AwMBMYINMTCCDS0GCSqGSIb3DQEHAqCCDR4wgg0a # AgEDMQ8wDQYJYIZIAWUDBAIBBQAweAYLKoZIhvcNAQkQAQSgaQRnMGUCAQEGCWCG # SAGG/WwHATAxMA0GCWCGSAFlAwQCAQUABCB0PX7H4O+5/22R+VBOeUtZz7eqF6oT # VfoKP93k0ralOwIRAMnxuoiPfvg6whOVc4A/gq4YDzIwMjEwMzI1MjAxOTAxWqCC # CjcwggT+MIID5qADAgECAhANQkrgvjqI/2BAIc4UAPDdMA0GCSqGSIb3DQEBCwUA # MHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsT # EHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJl # ZCBJRCBUaW1lc3RhbXBpbmcgQ0EwHhcNMjEwMTAxMDAwMDAwWhcNMzEwMTA2MDAw # MDAwWjBIMQswCQYDVQQGEwJVUzEXMBUGA1UEChMORGlnaUNlcnQsIEluYy4xIDAe # BgNVBAMTF0RpZ2lDZXJ0IFRpbWVzdGFtcCAyMDIxMIIBIjANBgkqhkiG9w0BAQEF # AAOCAQ8AMIIBCgKCAQEAwuZhhGfFivUNCKRFymNrUdc6EUK9CnV1TZS0DFC1JhD+ # HchvkWsMlucaXEjvROW/m2HNFZFiWrj/ZwucY/02aoH6KfjdK3CF3gIY83htvH35 # x20JPb5qdofpir34hF0edsnkxnZ2OlPR0dNaNo/Go+EvGzq3YdZz7E5tM4p8XUUt # S7FQ5kE6N1aG3JMjjfdQJehk5t3Tjy9XtYcg6w6OLNUj2vRNeEbjA4MxKUpcDDGK # SoyIxfcwWvkUrxVfbENJCf0mI1P2jWPoGqtbsR0wwptpgrTb/FZUvB+hh6u+elsK # IC9LCcmVp42y+tZji06lchzun3oBc/gZ1v4NSYS9AQIDAQABo4IBuDCCAbQwDgYD # VR0PAQH/BAQDAgeAMAwGA1UdEwEB/wQCMAAwFgYDVR0lAQH/BAwwCgYIKwYBBQUH # AwgwQQYDVR0gBDowODA2BglghkgBhv1sBwEwKTAnBggrBgEFBQcCARYbaHR0cDov # L3d3dy5kaWdpY2VydC5jb20vQ1BTMB8GA1UdIwQYMBaAFPS24SAd/imu0uRhpbKi # JbLIFzVuMB0GA1UdDgQWBBQ2RIaOpLqwZr68KC0dRDbd42p6vDBxBgNVHR8EajBo # MDKgMKAuhixodHRwOi8vY3JsMy5kaWdpY2VydC5jb20vc2hhMi1hc3N1cmVkLXRz # LmNybDAyoDCgLoYsaHR0cDovL2NybDQuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJl # ZC10cy5jcmwwgYUGCCsGAQUFBwEBBHkwdzAkBggrBgEFBQcwAYYYaHR0cDovL29j # c3AuZGlnaWNlcnQuY29tME8GCCsGAQUFBzAChkNodHRwOi8vY2FjZXJ0cy5kaWdp # Y2VydC5jb20vRGlnaUNlcnRTSEEyQXNzdXJlZElEVGltZXN0YW1waW5nQ0EuY3J0 # MA0GCSqGSIb3DQEBCwUAA4IBAQBIHNy16ZojvOca5yAOjmdG/UJyUXQKI0ejq5LS # JcRwWb4UoOUngaVNFBUZB3nw0QTDhtk7vf5EAmZN7WmkD/a4cM9i6PVRSnh5Nnon # t/PnUp+Tp+1DnnvntN1BIon7h6JGA0789P63ZHdjXyNSaYOC+hpT7ZDMjaEXcw30 # 82U5cEvznNZ6e9oMvD0y0BvL9WH8dQgAdryBDvjA4VzPxBFy5xtkSdgimnUVQvUt # MjiB2vRgorq0Uvtc4GEkJU+y38kpqHNDUdq9Y9YfW5v3LhtPEx33Sg1xfpe39D+E # 68Hjo0mh+s6nv1bPull2YYlffqe0jmd4+TaY4cso2luHpoovMIIFMTCCBBmgAwIB # AgIQCqEl1tYyG35B5AXaNpfCFTANBgkqhkiG9w0BAQsFADBlMQswCQYDVQQGEwJV # UzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQu # Y29tMSQwIgYDVQQDExtEaWdpQ2VydCBBc3N1cmVkIElEIFJvb3QgQ0EwHhcNMTYw # MTA3MTIwMDAwWhcNMzEwMTA3MTIwMDAwWjByMQswCQYDVQQGEwJVUzEVMBMGA1UE # ChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMTEwLwYD # VQQDEyhEaWdpQ2VydCBTSEEyIEFzc3VyZWQgSUQgVGltZXN0YW1waW5nIENBMIIB # IjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvdAy7kvNj3/dqbqCmcU5VChX # tiNKxA4HRTNREH3Q+X1NaH7ntqD0jbOI5Je/YyGQmL8TvFfTw+F+CNZqFAA49y4e # O+7MpvYyWf5fZT/gm+vjRkcGGlV+Cyd+wKL1oODeIj8O/36V+/OjuiI+GKwR5PCZ # A207hXwJ0+5dyJoLVOOoCXFr4M8iEA91z3FyTgqt30A6XLdR4aF5FMZNJCMwXbzs # PGBqrC8HzP3w6kfZiFBe/WZuVmEnKYmEUeaC50ZQ/ZQqLKfkdT66mA+Ef58xFNat # 1fJky3seBdCEGXIX8RcG7z3N1k3vBkL9olMqT4UdxB08r8/arBD13ays6Vb/kwID # AQABo4IBzjCCAcowHQYDVR0OBBYEFPS24SAd/imu0uRhpbKiJbLIFzVuMB8GA1Ud # IwQYMBaAFEXroq/0ksuCMS1Ri6enIZ3zbcgPMBIGA1UdEwEB/wQIMAYBAf8CAQAw # DgYDVR0PAQH/BAQDAgGGMBMGA1UdJQQMMAoGCCsGAQUFBwMIMHkGCCsGAQUFBwEB # BG0wazAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuZGlnaWNlcnQuY29tMEMGCCsG # AQUFBzAChjdodHRwOi8vY2FjZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRBc3N1 # cmVkSURSb290Q0EuY3J0MIGBBgNVHR8EejB4MDqgOKA2hjRodHRwOi8vY3JsNC5k # aWdpY2VydC5jb20vRGlnaUNlcnRBc3N1cmVkSURSb290Q0EuY3JsMDqgOKA2hjRo # dHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNlcnRBc3N1cmVkSURSb290Q0Eu # Y3JsMFAGA1UdIARJMEcwOAYKYIZIAYb9bAACBDAqMCgGCCsGAQUFBwIBFhxodHRw # czovL3d3dy5kaWdpY2VydC5jb20vQ1BTMAsGCWCGSAGG/WwHATANBgkqhkiG9w0B # AQsFAAOCAQEAcZUS6VGHVmnN793afKpjerN4zwY3QITvS4S/ys8DAv3Fp8MOIEIs # r3fzKx8MIVoqtwU0HWqumfgnoma/Capg33akOpMP+LLR2HwZYuhegiUexLoceywh # 4tZbLBQ1QwRostt1AuByx5jWPGTlH0gQGF+JOGFNYkYkh2OMkVIsrymJ5Xgf1gsU # pYDXEkdws3XVk4WTfraSZ/tTYYmo9WuWwPRYaQ18yAGxuSh1t5ljhSKMYcp5lH5Z # /IwP42+1ASa2bKXuh1Eh5Fhgm7oMLSttosR+u8QlK0cCCHxJrhO24XxCQijGGFbP # QTS2Zl22dHv1VjMiLyI2skuiSpXY9aaOUjGCAk0wggJJAgEBMIGGMHIxCzAJBgNV # BAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdp # Y2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBUaW1l # c3RhbXBpbmcgQ0ECEA1CSuC+Ooj/YEAhzhQA8N0wDQYJYIZIAWUDBAIBBQCggZgw # GgYJKoZIhvcNAQkDMQ0GCyqGSIb3DQEJEAEEMBwGCSqGSIb3DQEJBTEPFw0yMTAz # MjUyMDE5MDFaMCsGCyqGSIb3DQEJEAIMMRwwGjAYMBYEFOHXgqjhkb7va8oWkbWq # tJSmJJvzMC8GCSqGSIb3DQEJBDEiBCD9m3Af9pFlgZsrHR9cy3w0fenlhVTKqeJr # 8Te//Ex+BTANBgkqhkiG9w0BAQEFAASCAQA8RnXGzmUlI/bbuBCPCJPUqq7IQv7g # Kn7CAoSlhfwWalVN0Fj6tsD1mr/kDPCYWvOeUx1WdFqHTMH8ZC5S3y7BmBSxLIrw # B166qB+xGoejcKu6OK3dQ/MjsaPZgRI/B+y0q6hjwVItwhQIpfyGnqoPTdItN26k # Vy0ltAu+fuvMquXPn0JeXxTnyMfQtlFH1w7SVnj63gsGovQyfANTrVmNeZkMDng9 # DFCNJXwauPXkz3GL0kiDrFXUd2FEtaZH58yyMlIWAGkblGvgQdUIG9DytLQjEl1F # EAJhhXQGXyRrc5AT8TzZfaDbKB98p6wXFifIcgiFAWKyBfCklsoujwkS # SIG # End signature block |