1189 lines
107 KiB
PowerShell
1189 lines
107 KiB
PowerShell
# Export-DBDataToFile
|
||
# ----------------------------------------------------------------------------
|
||
# This Script exports DB Results to a File
|
||
#
|
||
# Returns: -
|
||
# ----------------------------------------------------------------------------
|
||
# Copyright (c) 2023 by Digital Data GmbH
|
||
#
|
||
# Digital Data GmbH • Ludwig-Rinn-Strasse 16 • D-35452 Heuchelheim
|
||
# Tel.: 0641/202360 • E-Mail: info-flow@digitaldata.works
|
||
# ----------------------------------------------------------------------------
|
||
# Creation Date / Author: 30.11.2023 / MK
|
||
# Version Date / Editor: 18.08.2025 / MD
|
||
# Version Number: 1.0.0.1
|
||
|
||
#Requires –Version 4.0
|
||
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
######################################## check for arguments ##########################################
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
|
||
Param (
|
||
|
||
[Parameter(Position=0,Mandatory=$False)]
|
||
[ValidateNotNull()]
|
||
[STRING]$pProfileType,
|
||
|
||
[Parameter(Position=1,Mandatory=$False)]
|
||
[ValidateNotNull()]
|
||
[STRING]$pExportPath,
|
||
|
||
[Parameter(Position=2,Mandatory=$False)]
|
||
[ValidateNotNull()]
|
||
[STRING]$pExportFilename,
|
||
|
||
[Parameter(Position=3,Mandatory=$False)]
|
||
[ValidateNotNull()]
|
||
[STRING]$pExportSQLCheckQuery,
|
||
|
||
[Parameter(Position=4,Mandatory=$False)]
|
||
[ValidateNotNull()]
|
||
[STRING]$pExportSQLProcedure,
|
||
|
||
[Parameter(Position=5,Mandatory=$False)]
|
||
[ValidateNotNull()]
|
||
[STRING]$pExportSQLGetQuery
|
||
|
||
) #end Param
|
||
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
################################## add additional buildin assemblys ###################################
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
|
||
#Add-Type -AssemblyName System.Windows.Forms -ErrorAction Stop
|
||
#Add-Type -AssemblyName PresentationCore -ErrorAction Stop
|
||
#Add-Type -AssemblyName PresentationFramework -ErrorAction Stop
|
||
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
############################################ set variables ############################################
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
|
||
Set-Variable -Scope Global -Name ScriptName -Value (($MyInvocation.MyCommand.Name) -split "\.")[0].ToString()
|
||
Set-Variable -Scope Global -Name ScriptPath -Value (Split-Path ($MyInvocation.MyCommand.Path))
|
||
Set-Variable -Scope Global -Name ConfigFile -Value (Get-ChildItem -Path "$ScriptPath" -Recurse:$false -Filter "$ScriptName`_Settings.ini" -File -Force).FullName | Select-Object -First 1
|
||
Set-Variable -Scope Global -Name ConfigFileContent -Value $NULL
|
||
Set-Variable -Scope Global -Name Timestamp1 -Value $(Get-Date -Format 'ddMMyyyy')
|
||
Set-Variable -Scope Global -Name Timestamp2 -Value $(Get-Date -Format 'ddMMyyyy_HHmmss')
|
||
Set-Variable -Scope Global -Name Timestamp3 -Value $(Get-Date -Format 'ddMMyyyy_HHmmssffff')
|
||
Set-Variable -Scope Global -Name Timestamp4 -Value $(Get-Date -Format 'yyyyMMdd HH:mm:ss.fff')
|
||
Set-Variable -Scope Global -Name Item -Value $NULL
|
||
Set-Variable -Scope Global -Name Items -Value $NULL
|
||
Set-Variable -Scope Global -Name VersionSeperator -Value "~"
|
||
Set-Variable -Scope Global -Name Counter1 -Value 0
|
||
Set-Variable -Scope Global -Name LogFile -Value "$ScriptName`_$Timestamp2.log"
|
||
Set-Variable -Scope Global -Name LogFileKeepTime -Value 60
|
||
Set-Variable -Scope Global -Name LogPath -Value $NULL
|
||
Set-Variable -Scope Global -Name FileDelayAge -Value 5
|
||
Set-Variable -Scope Global -Name FileTest1 -Value $NULL
|
||
Set-Variable -Scope Global -Name FileTest2 -Value $NULL
|
||
Set-Variable -Scope Global -Name FileTest3 -Value $NULL
|
||
Set-Variable -Scope Global -Name PathTest -Value $NULL
|
||
Set-Variable -Scope Global -Name Module -Value $NULL
|
||
Set-Variable -Scope Global -Name Modules -Value ("Read-ConfigFile2","Write-LogFile","Test-Path-withLogging","Test-Path-withLogging","Remove-Item-withLogging")
|
||
Set-Variable -Scope Global -Name ModuleOverrideSourcePath -Value $NULL
|
||
Set-Variable -Scope Global -Name ModuleDefaultSourcePath -Value "P:\Skriptentwickung\released-and-published\Modules"
|
||
Set-Variable -Scope Global -Name ModuleHKLMRegistryPath -Value "HKLM:\SOFTWARE\Digital Data\Modules"
|
||
Set-Variable -Scope Global -Name ModuleHKCURegistryPath -Value "HKCU:\SOFTWARE\Digital Data\Modules"
|
||
Set-Variable -Scope Global -Name Profile -Value $NULL
|
||
|
||
Set-Variable -Scope Global -Name DBType -Value $NULL
|
||
Set-Variable -Scope Global -Name DBLine -Value $NULL
|
||
Set-Variable -Scope Global -Name DBConnString -Value $NULL
|
||
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
############################################ set functions ############################################
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
|
||
Function Load-PowerShellModule {
|
||
|
||
<#
|
||
.SYNOPSIS
|
||
Function will load external - additional - PowerShell Modules into current PSSession.
|
||
|
||
.DESCRIPTION
|
||
By working with Modules, this Function is necessary to load external Modul Functions into the current PowerShell Session.
|
||
In a productive Enviroment it is recommanded to let this Function set the Registry Key in HKLM for the ModuleSourcePath.
|
||
In develepment and Test Enviroment it is possible, to work with distributed Folders with different Modules. Therefor the Parameter
|
||
"ModuleOverrideSourcePath" and the preset Variable "ModuleDefaultSourcePath" are made for.
|
||
After a successful Import of a Module, Function will Return $True, otherwise a $False.
|
||
|
||
.REQUIREMENT General
|
||
PowerShell V3
|
||
|
||
.REQUIREMENT Assembly
|
||
System.Windows.Forms, PresentationCore, PresentationFramework
|
||
|
||
.REQUIREMENT Variables
|
||
ModuleOverrideSourcePath, ModuleName, Path, Paths, PathTest, FileTest, Result
|
||
|
||
.REQUIREMENT Variables preSet
|
||
ScriptName, ScriptPath, ModuleDefaultSourcePath (optional)
|
||
|
||
.REQUIREMENT Functions
|
||
<NONE>
|
||
|
||
.VERSION
|
||
1.1.0.0 / 22.02.2017
|
||
|
||
.PARAMETER ModuleName
|
||
Give the Module Name, you want to load into the current PSSession (without File-Extension).
|
||
|
||
.PARAMETER ModuleOverrideSourcePath
|
||
Optional Parameter. By giving the ModuleOverrideSourcePath, Function will not check other Paths for the Function you want to load.
|
||
|
||
.PARAMETER ModuleFileExtensions
|
||
Optional Parameter. Give the Module File-Extension (regular: "psm1" or "dll") without a dot ("."), this is just for the checking routine, not the Import itself.')]
|
||
|
||
.PARAMETER Force
|
||
Optional Parameter. By using the Force Parameter, Module will be unload and reload.
|
||
|
||
.EXAMPLE
|
||
Load-PowerShellModule -ModuleName Write-LogFile -ModuleFileExtensions psm1
|
||
|
||
.EXAMPLE
|
||
Load-PowerShellModule -ModuleName Write-LogFile -Force
|
||
|
||
.EXAMPLE
|
||
Load-PowerShellModule -ModuleName Write-LogFile -ModuleFileExtensions psm1 -ModuleOverrideSourcePath D:\ScriptFiles\Modules
|
||
#>
|
||
|
||
Param (
|
||
|
||
[Parameter(Position=0,Mandatory=$True,ValueFromPipeline=$True,HelpMessage='Give the ModuleName, you want to load into the current PSSession (without File-Extension)')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[String]$ModuleName,
|
||
|
||
[Parameter(Position=1,Mandatory=$False,HelpMessage='Optional Parameter. By giving the ModuleOverrideSourcePath, Function will not check other Paths for the Function you want to load.')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[String]$ModuleOverrideSourcePath,
|
||
|
||
[Parameter(Position=2,Mandatory=$False,HelpMessage='Optional Parameter. Give the Module File-Extension (regular: "psm1" or "dll") without a dot ("."), this is just for the checking routine, not the Import itself.')]
|
||
[ValidateSet("psm1","dll")]
|
||
[Array]$ModuleFileExtensions = @("psm1","dll"),
|
||
|
||
[Parameter(Position=3,Mandatory=$False,HelpMessage='Optional Parameter. By using the Force Parameter, Module will be unload and reload.')]
|
||
[Switch]$Force
|
||
|
||
) #end param
|
||
|
||
Process {
|
||
|
||
#Clear Error Variable
|
||
$error.clear()
|
||
|
||
#Loop for every possible File Extension (eg. psm1 and dll)
|
||
FOREACH ($ModuleFileExtension in $ModuleFileExtensions) {
|
||
|
||
#If FileExtension was given, remove it! Because otherwise "Import-Module" Function will have trouble importing.
|
||
$ModuleName = $ModuleName -Replace("\.[a-zA-Z0-9]{3,4}","")
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: You want to load Module $ModuleName.$ModuleFileExtension"
|
||
|
||
IF ((([String]::IsNullOrWhiteSpace($ScriptName) -ne $True) -and ([String]::IsNullOrEmpty($ScriptName) -ne $True)) -and (([String]::IsNullOrWhiteSpace($ScriptPath) -ne $True) -and ([String]::IsNullOrEmpty($ScriptPath) -ne $True))) {
|
||
|
||
#Try this if $ModuleOverrideSourcePath was given by calling the function
|
||
IF ((([String]::IsNullOrWhiteSpace($ModuleOverrideSourcePath)) -ne $True) -and (([String]::IsNullOrEmpty($ModuleOverrideSourcePath)) -ne $True)) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Function has been called with 'ModuleOverrideSourcePath' Parameter input!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Testing for existence: $ModuleOverrideSourcePath\$ModuleName.$ModuleFileExtension"
|
||
$FileTest = Test-Path -Path "$ModuleOverrideSourcePath\$ModuleName.$ModuleFileExtension" -PathType Leaf
|
||
|
||
IF ($FileTest -eq $True) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: $ModuleOverrideSourcePath and ModuleName seems to exist."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Trying to import Module: $ModuleName.$ModuleFileExtension"
|
||
|
||
Try {
|
||
|
||
$Result = Import-Module $ModuleOverrideSourcePath\$ModuleName -Verbose -DisableNameChecking -Scope Global -Force:$Force -PassThru -ErrorAction SilentlyContinue
|
||
|
||
IF ("$Result" -eq "$ModuleName") {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Successfully loaded Module: $ModuleName.$ModuleFileExtension"
|
||
Return $True
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Error "DEBUG Info - Load-PowerShellModule: Unsuccessfully loaded Module: $ModuleName.$ModuleFileExtension"
|
||
Return $False
|
||
|
||
} #end else
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Error while importing the Module:"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: $ModuleName"
|
||
Write-Host $Error
|
||
Return $False
|
||
|
||
} #end catch
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: ModuleOverrideSourcePath and/or ModuleName seems not to exist."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Cannot load Module, please check your input!"
|
||
Return $False
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
#If $ModuleOverrideSourcePath was not given, try to find a matching folder
|
||
ELSE {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Function has been called without 'ModuleOverrideSourcePath' Parameter input!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Trying to find Module Files on some Places of this Computer."
|
||
|
||
#Set dynamic Array for locations Modul Path could be, even for some testing.
|
||
#The first value of the array is just a dummy and will never be used -but keep it for the array value starting with 0!
|
||
[System.Collections.ArrayList]$Paths = @()
|
||
Write-Host ""
|
||
$Paths.Add("$env:systemroot\") | Out-Null
|
||
|
||
IF (([String]::IsNullOrEmpty($ModuleDefaultSourcePath)) -or ([String]::IsNullOrWhiteSpace($ModuleDefaultSourcePath))) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: ModuleDefaultSourcePath was not set! That could be a normal behavior in productive enviroment!"
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (1): $ModuleDefaultSourcePath" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$ModuleDefaultSourcePath") | Out-Null
|
||
|
||
} #end else
|
||
|
||
IF ([String]::IsNullOrEmpty($ScriptPath) -or ([String]::IsNullOrWhiteSpace($ScriptPath))) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: ScriptPath is invalid! That is terrifying! How could that be???"
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (2): $ScriptPath" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$ScriptPath") | Out-Null
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (3): $($ScriptPath+"\Module")" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$($ScriptPath+"\Module")") | Out-Null
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (4): $($ScriptPath+"\Modules")" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$($ScriptPath+"\Modules")") | Out-Null
|
||
|
||
} #end else
|
||
|
||
IF (([String]::IsNullOrEmpty((Get-Item $ScriptPath).Parent.FullName)) -or ([String]::IsNullOrWhiteSpace((Get-Item $ScriptPath).Parent.FullName))) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: ScriptPath has no Parent Folders!"
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (5): $((Get-Item $ScriptPath).Parent.FullName)" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$((Get-Item $ScriptPath).Parent.FullName)") | Out-Null
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (6): $(((Get-Item $ScriptPath).Parent.FullName)+"\Module")" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$(((Get-Item $ScriptPath).Parent.FullName)+"\Module")") | Out-Null
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (7): $(((Get-Item $ScriptPath).Parent.FullName)+"\Modules")" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$(((Get-Item $ScriptPath).Parent.FullName)+"\Modules")") | Out-Null
|
||
|
||
} #end else
|
||
|
||
IF (([String]::IsNullOrEmpty($((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath))) -or ([String]::IsNullOrWhiteSpace($((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)))) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: ModuleSourcePath was not set to Windows Registry (HKLM)!"
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (8): $((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)") | Out-Null
|
||
|
||
} #end else
|
||
|
||
IF (([String]::IsNullOrEmpty($((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath))) -or ([String]::IsNullOrWhiteSpace($((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)))) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: ModuleSourcePath was not set to Windows Registry (HKCU)!"
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Possible Path (8): $((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)" -ErrorAction SilentlyContinue
|
||
$Paths.Add("$((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)") | Out-Null
|
||
|
||
} #end else
|
||
|
||
[Int]$Counter = 0
|
||
[String]$ModuleSourcePath = $Null
|
||
|
||
#Loop for multiple Pathtests - for each Path, where Module files could be
|
||
DO {
|
||
|
||
$Counter++
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Testing mutiple Paths ( $Counter of"($($Paths.Count)-1)") for existence, now testing:"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: $($Paths[$Counter])"
|
||
|
||
IF ((([String]::IsNullOrWhiteSpace($($Paths[$Counter]))) -ne $True) -and (([String]::IsNullOrEmpty($($Paths[$Counter])) -ne $True))) {
|
||
|
||
$PathTest = (Test-Path $($Paths[$Counter]) -ErrorAction SilentlyContinue)
|
||
|
||
IF ($PathTest -eq $True) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Yes, Path seems to exist."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Lets check, if there are any Module Files, in it."
|
||
|
||
$FileTest = Get-ChildItem -Path $($Paths[$Counter]) -Filter *.$ModuleFileExtension
|
||
|
||
IF ($($FileTest.count) -gt 0) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Found $($FileTest.count) $ModuleFileExtension Module Files in Path!"
|
||
Set-Variable -Name ModuleSourcePath -Value $($Paths[$Counter]) -Scope local
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Found no $ModuleFileExtension Module Files in Path!"
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: No, Path seems not to exist."
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Path seems to be invalid!"
|
||
|
||
} #end else
|
||
|
||
} #end do
|
||
|
||
UNTIL ($Counter -ge ($($Paths.Count)-1) -or ($ModuleSourcePath -eq $($Paths[$Counter])))
|
||
|
||
IF ($ModuleSourcePath -eq $($Paths[$Counter])) {
|
||
|
||
$FileTest = (Test-Path -Path $ModuleSourcePath\$ModuleName.$ModuleFileExtension -PathType Leaf -ErrorAction SilentlyContinue)
|
||
|
||
IF ($FileTest -eq $True) {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Trying to import Module: $ModuleName.$ModuleFileExtension"
|
||
|
||
Try {
|
||
|
||
$Result = Import-Module $ModuleSourcePath\$ModuleName -Verbose -DisableNameChecking -Scope Global -Force:$Force -PassThru -ErrorAction SilentlyContinue
|
||
|
||
IF ("$Result" -eq "$ModuleName") {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Successfully loaded Module: $ModuleName.$ModuleFileExtension"
|
||
Set-Variable -Name ModuleDefaultSourcePath -Value $ModuleSourcePath -Scope Global
|
||
Return $True
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Error "DEBUG Info - Load-PowerShellModule: Unsuccessfully loaded Module: $ModuleName.$ModuleFileExtension"
|
||
Return $False
|
||
|
||
} #end else
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Loading Module: $ModuleName went wrong."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Exiting Script, because of this error!"
|
||
Write-Host $Error
|
||
Return $False
|
||
exit
|
||
|
||
} #end catch
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Module does not exist!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Skipping: $ModuleName.$ModuleFileExtension"
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Cant locate Module Files automaticlly!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Please select Folder in Dialog."
|
||
|
||
#Prepare Folder Browser Dialog, to choose the Directory with the .psm1 Files.
|
||
$FolderBrowserDialog = New-Object System.Windows.Forms.FolderBrowserDialog
|
||
$FolderBrowserDialog.Rootfolder = "Desktop"
|
||
$FolderBrowserDialog.Description = "Please, choose the Folder, where the Module ""$ModuleName.$ModuleFileExtension"" is stored."
|
||
$FolderBrowserDialog.SelectedPath = "$ScriptPath"
|
||
$FolderBrowserDialog.ShowNewFolderButton = $True
|
||
|
||
DO {
|
||
|
||
#Now show the Folder Browser, if neccessary in a loop
|
||
$FolderBrowserDialogShow = $FolderBrowserDialog.ShowDialog()
|
||
|
||
#By pressing the OK Button..
|
||
If ($FolderBrowserDialogShow -eq "OK") {
|
||
|
||
#Save selected folder path in the variable
|
||
$ModuleSourcePath = ($FolderBrowserDialog.SelectedPath)
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: You choose: $ModuleSourcePath"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: ...testing, if Module ""$ModuleName.$ModuleFileExtension"" can be found there."
|
||
|
||
$FileTest = (Test-Path $ModuleSourcePath\$ModuleName.$ModuleFileExtension -PathType Leaf -ErrorAction SilentlyContinue)
|
||
|
||
IF ($FileTest -eq $True) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Module seems to exist, in the selected Folder."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Now trying to load Module: $ModuleName.$ModuleFileExtension"
|
||
|
||
Try {
|
||
|
||
$Result = Import-Module $ModuleSourcePath\$ModuleName -Verbose -DisableNameChecking -Scope Global -Force:$Force -PassThru -ErrorAction SilentlyContinue
|
||
|
||
IF ("$Result" -eq "$ModuleName") {
|
||
|
||
Set-Variable -Name ModuleDefaultSourcePath -Value $ModuleSourcePath -Scope Global
|
||
|
||
$MessageBoxBody = "Module: $ModuleName.$ModuleFileExtension - successsfully loaded into current PSSession!"
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "OK"
|
||
$MessageBoxIcon = "Information"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) | Out-Null
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
$MessageBoxBody = "Module: $ModuleName.$ModuleFileExtension - cannot load into current PSSession!"
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "OK"
|
||
$MessageBoxIcon = "Warning"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) | Out-Null
|
||
|
||
} #end else
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Loading Module: $ModuleName.$ModuleFileExtension went wrong."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Exiting Script, because of this error!"
|
||
Write-Host $Error
|
||
exit
|
||
|
||
} #end catch
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Module seems not to exist, in the selected Folder."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Please, select another one! ...this Time the right!"
|
||
|
||
$MessageBoxBody = "Module seems not to exist, in the selected Folder! Please, select another one!"
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "OK"
|
||
$MessageBoxIcon = "Warning"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) | Out-Null
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
#If you didnt pressed the OK Button..
|
||
Else {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Operation cancelled by user."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Exiting Script, because of this!"
|
||
exit
|
||
|
||
} #end else
|
||
|
||
} #end do
|
||
|
||
#Variable "$?" is $True when last operation was ok
|
||
UNTIL ((($FileTest -eq $True) -and ($? -eq $True)) -or ($FolderBrowserDialogShow -eq "Cancel"))
|
||
|
||
IF (([String]::IsNullOrWhiteSpace($ModuleSourcePath) -ne $True) -and ([String]::IsNullOrEmpty($ModuleSourcePath) -ne $True)) {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Should ModuleSourcePath written to Windows Registry?"
|
||
|
||
$MessageBoxBody = "Would you like to save the ModulePath to the Windows Registry?"
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "YesNo"
|
||
$MessageBoxIcon = "Question"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon)
|
||
|
||
IF ($MessageBox -eq 'Yes') {
|
||
|
||
$PathTest = (Test-Path -Path "$ModuleHKLMRegistryPath" -ErrorAction SilentlyContinue)
|
||
$MessageBox = $NULL
|
||
|
||
IF ($PathTest -eq $False) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Registry Key seems not to exist."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Trying to write ModuleSourcepath to HKLM."
|
||
|
||
Try {
|
||
|
||
New-Item -Path "$ModuleHKLMRegistryPath" -Force -ErrorAction Stop | Out-Null
|
||
New-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Host $Error
|
||
$MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Check your access rights! To bypass this issue you can write the ModuleSourcePath to User Registry (HKCU). Would you?"
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "YesNo"
|
||
$MessageBoxIcon = "Question"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon)
|
||
|
||
} #end catch
|
||
|
||
} #end if
|
||
|
||
ELSEIF ($PathTest -eq $True) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Registry Key seems to exist."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Trying to write ModuleSourcepath to HKLM."
|
||
|
||
Try {
|
||
|
||
Set-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Host $Error
|
||
$MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Check your access rights! To Bypass Error: Write ModuleSource Path to User Registry (HKCU)?"
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "YesNo"
|
||
$MessageBoxIcon = "Question"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon)
|
||
|
||
} #end catch
|
||
|
||
} #end elseif
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Something went wrong, by getting the ModuleSourcePath!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Exiting Script, because of this!"
|
||
exit
|
||
|
||
} #end else
|
||
|
||
#Block for trying to write the ModuleSourcePath to the User Registry, if System Registry failed.
|
||
IF ($MessageBox -eq 'Yes') {
|
||
|
||
$PathTest = (Test-Path -Path "$ModuleHKCURegistryPath" -ErrorAction SilentlyContinue)
|
||
$MessageBox = $NULL
|
||
|
||
IF ($PathTest -eq $False) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Registry Key seems not to exist."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Trying to write ModuleSourcepath to HKCU."
|
||
|
||
Try {
|
||
|
||
New-Item -Path "$ModuleHKCURegistryPath" -Force -ErrorAction Stop | Out-Null
|
||
New-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Host $Error
|
||
$MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Not even to User Registry! Check your access rights! Exiting now.."
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "OK"
|
||
$MessageBoxIcon = "Warning"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon)
|
||
exit
|
||
|
||
} #end catch
|
||
|
||
} #end if
|
||
|
||
ELSEIF ($PathTest -eq $True) {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Registry Key seems to exist."
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Trying to write ModuleSourcepath to HKCU."
|
||
|
||
Try {
|
||
|
||
Set-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Host $Error
|
||
$MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Not even to User Registry! Check your access rights! Exiting now.."
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "OK"
|
||
$MessageBoxIcon = "Warning"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon)
|
||
exit
|
||
|
||
} #end catch
|
||
|
||
} #end elseif
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Something went wrong, by getting the ModuleSourcePath!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Exiting Script, because of this!"
|
||
exit
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: You choose, not to save the ModuleSourcePath to the Windows Registry!"
|
||
|
||
$MessageBoxBody = "You choose, not to save the ModuleSourcePath to the Windows Registry!"
|
||
$MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Load-PowerShellModule"
|
||
$MessageBoxButtonType = "OK"
|
||
$MessageBoxIcon = "Warning"
|
||
$MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon)
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Something went wrong, by getting the ModuleSourcePath!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Exiting Script, because of this!"
|
||
exit
|
||
|
||
} #end else
|
||
|
||
} #end else
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host ""
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Required Variables (ScriptName and ScriptPath) were not set!"
|
||
Write-Host "DEBUG Info - Load-PowerShellModule: Module Import is unvailable without this Variables!"
|
||
|
||
} #end else
|
||
|
||
} #end foreach
|
||
|
||
} #end process
|
||
|
||
} #end function
|
||
|
||
Function Start-SQLDB-StoredProcedure-withLogging {
|
||
|
||
<#
|
||
.SYNOPSIS
|
||
Private function to execute a sql procedure
|
||
|
||
#>
|
||
|
||
Param (
|
||
[Parameter(Position=0,Mandatory=$true,HelpMessage='Give the SQL Connection string')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[STRING]$ConnString,
|
||
|
||
[Parameter(Position=1,Mandatory=$true,HelpMessage='Give the SQL Procedure name')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[STRING]$ExportSQLProcedure,
|
||
|
||
[Parameter(Position=2,Mandatory=$true,HelpMessage='Give the first parameter of the procedure')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[STRING]$ExportSQLProcedure_STACK_ID,
|
||
|
||
[Parameter(Position=3,Mandatory=$False,HelpMessage='Give the second parameter of the procedure')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[STRING]$ExportSQLProcedure_FILE_NAME
|
||
|
||
) #end param
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "Starting sql procedure, the parameter given are:"
|
||
Write-Logfile -LogLine "$ConnString"
|
||
Write-Logfile -LogLine "$ExportSQLProcedure"
|
||
Write-Logfile -LogLine "$ExportSQLProcedure_STACK_ID"
|
||
Write-Logfile -LogLine "$ExportSQLProcedure_FILE_NAME"
|
||
|
||
$sqlConnection = new-object System.Data.SqlClient.SqlConnection $ConnString
|
||
$sqlConnection.Open()
|
||
|
||
$sqlCmd = new-object System.Data.SqlClient.SqlCommand("$ExportSQLProcedure", $sqlConnection)
|
||
|
||
$sqlCmd.CommandType = [System.Data.CommandType]"StoredProcedure"
|
||
|
||
$sqlCmd.Parameters.AddWithValue("@pSTACK_ID", $ExportSQLProcedure_STACK_ID) | out-null
|
||
$sqlCmd.Parameters.AddWithValue("@pFILE_NAME", $ExportSQLProcedure_FILE_NAME) | out-null
|
||
|
||
$sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int") | out-null
|
||
$sqlCmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue"
|
||
|
||
$sqlCmd.ExecuteNonQuery() | out-null
|
||
$sqlConnection.Close()
|
||
|
||
$ReturnValue = [int]$sqlCmd.Parameters["@ReturnValue"].Value
|
||
Return $ReturnValue
|
||
|
||
} #end function
|
||
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
########################################### preparing part ############################################
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
|
||
#Clear Console Content
|
||
Clear-Host
|
||
|
||
#Load external Modules - use Force ( -Force) Parameter, to reload in every run
|
||
FOREACH ($Module in $Modules) {
|
||
|
||
$Result = Load-PowerShellModule -ModuleName $Module -Force
|
||
|
||
IF ($Result -eq $False) {
|
||
|
||
Write-Host "DEBUG Info: Module: $Module was not succesful been loaded!"
|
||
Write-Host "DEBUG Info: Please load the Module and try again, running this Function/Module!"
|
||
Write-Host "DEBUG Info: Exiting, because of this Issue."
|
||
Write-Host $Error
|
||
EXIT
|
||
|
||
} #end if
|
||
|
||
} #end foreach
|
||
|
||
#Read ConfigFile and load its content into a Object
|
||
[OBJECT]$ConfigFileContent = Read-ConfigFile2 -ConfigFile $ConfigFile
|
||
|
||
#Allocate Variable Values, depending on the read ConfigFile
|
||
[ARRAY]$LogPaths = $ConfigFileContent."LogPath_$($ConfigFileContent.LogPath[0])"
|
||
[STRING]$LogFileKeepTime = $ConfigFileContent."LogFileKeepTime_$($ConfigFileContent.LogFileKeepTime[0])"
|
||
|
||
[STRING]$DBType = $ConfigFileContent."DBType_$($ConfigFileContent.DBType[0])"
|
||
[STRING]$DBConnString = $ConfigFileContent."DBConnString_$($ConfigFileContent.DBConnString[0])"
|
||
|
||
TRY {
|
||
|
||
#Create Object for Profile, the most Propertys of it will be rewritten in the main loop
|
||
[OBJECT]$Profile = New-Object PSObject -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name Counter -Value 0 -Force -ErrorAction Stop
|
||
|
||
#Check if Script was called with arguments
|
||
If (($PSBoundParameters.values | Measure-Object | Select-Object -ExpandProperty Count) -gt 0) {
|
||
|
||
Write-Host "DEBUG Info: Running Script in 'Argument call mode'"
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name Count -Value 1 -Force -ErrorAction Stop
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Host "DEBUG Info: Running Script in 'Settings file mode'"
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name Count -Value "$($ConfigFileContent.Profile[0])" -ErrorAction Stop
|
||
|
||
} #end else
|
||
|
||
} #end try
|
||
|
||
CATCH {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "Error creating Object for: Profile"
|
||
Write-Logfile -LogLine "Property Value is missing or invalid!"
|
||
|
||
#Removeing incomplete Object
|
||
Remove-Variable -Name Profile -Force -ErrorAction SilentlyContinue
|
||
|
||
} #end catch
|
||
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
############################################# main part ###############################################
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
|
||
#Clear Error Variable
|
||
$Error.Clear() | Out-Null
|
||
|
||
#Clear Console Output
|
||
Clear-Host
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "********************************************************************************"
|
||
Write-Logfile -LogLine "Program Startup: $ScriptName on $env:COMPUTERNAME,"
|
||
Write-Logfile -LogLine "from Account $env:USERDOMAIN\$env:USERNAME."
|
||
Write-Logfile -LogLine "********************************************************************************"
|
||
|
||
Write-Logfile -LogLine "To avoid error messages about 'Invoke-Sqlcmd',"
|
||
Write-Logfile -LogLine "make sure the Module is installed with admin rights!"
|
||
Write-Logfile -LogLine "Powershell-Command: 'Install-Module -Name SqlServer'"
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "Found $($Profile.Count) Profile(s) to process."
|
||
|
||
#Start main part, if there is at least one profile defined
|
||
IF ($Profile.Count -gt 0) {
|
||
|
||
#Loop for every Profile configured. Every Profile is one line in the ConfigFile.
|
||
DO {
|
||
|
||
$Profile.Counter++ | Out-Null
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "--------------------------------------------------------------------------------"
|
||
Write-Logfile -LogLine "This is Profile: $($Profile.Counter) of $($Profile.Count)"
|
||
Write-Logfile -LogLine "--------------------------------------------------------------------------------"
|
||
|
||
TRY {
|
||
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ProfileType -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportPath -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportFilename_Template -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportFilename -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportSQLCheckQuery -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportSQLCheckQuery_Result -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportSQLProcedure -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportSQLProcedure_Result -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportSQLGetQuery_Template -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportSQLGetQuery -Value $NULL -Force -ErrorAction Stop
|
||
Add-Member -InputObject $Profile -MemberType NoteProperty -Name ExportSQLGetQuery_Result -Value $NULL -Force -ErrorAction Stop
|
||
|
||
} #end try
|
||
|
||
CATCH {
|
||
|
||
Write-Logfile -LogLine "Cannot build Profile."
|
||
Write-Logfile -LogLine "Exiting because of this Issue!"
|
||
Write-Logfile -LogLine "$Error"
|
||
exit
|
||
|
||
} #end catch
|
||
|
||
#If argument call is used...
|
||
IF (($PSBoundParameters.values | Measure-Object | Select-Object -ExpandProperty Count) -gt 0) {
|
||
|
||
IF ($pProfileType -and $pExportPath -and $pExportFilename -and $pExportSQLCheckQuery -and $pExportSQLProcedure -and $pExportSQLGetQuery) {
|
||
|
||
$Profile.ProfileType = $pProfileType
|
||
Write-Logfile -LogLine "Argument called ProfileType is: $($Profile.ProfileType)"
|
||
|
||
$Profile.ExportPath = $pExportPath
|
||
Write-Logfile -LogLine "Argument called ExportPath is: $($Profile.ExportPath)"
|
||
|
||
$Profile.ExportFilename_Template = $pExportFilename
|
||
Write-Logfile -LogLine "Argument called ExportFilename_Template is: $($Profile.ExportFilename_Template)"
|
||
|
||
$Profile.ExportSQLCheckQuery = $pExportSQLCheckQuery
|
||
Write-Logfile -LogLine "Argument called ExportSQLCheckQuery is: $($Profile.ExportSQLCheckQuery)"
|
||
|
||
$Profile.ExportSQLProcedure = $pExportSQLProcedure
|
||
Write-Logfile -LogLine "Argument called ExportSQLProcedure is: $($Profile.ExportSQLProcedure)"
|
||
|
||
$Profile.ExportSQLGetQuery = $pExportSQLGetQuery
|
||
Write-Logfile -LogLine "Argument called ExportSQLGetQuery is: $($Profile.ExportSQLGetQuery)"
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine "Cannot build Profile. Argument call is missing Parameter or/and Parameter Values!"
|
||
Write-Logfile -LogLine "Exiting because of this Issue!"
|
||
Write-Logfile -LogLine "$Error"
|
||
exit
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
#If argument call is not used...
|
||
ELSE {
|
||
|
||
$Profile.ProfileType = ($ConfigFileContent."Profile_$($Profile.Counter)")[0]
|
||
Write-Logfile -LogLine "Configured ProfileType is: $($Profile.ProfileType)"
|
||
|
||
IF ($Profile.ProfileType -eq "BulkExport") {
|
||
|
||
Write-Host "DEBUG Info: ProfileType is valid."
|
||
|
||
$Profile.ExportPath = ($ConfigFileContent."Profile_$($Profile.Counter)")[1]
|
||
Write-Logfile -LogLine "Configured ExportPath is: $($Profile.ExportPath)"
|
||
|
||
$Profile.ExportFilename_Template = ($ConfigFileContent."Profile_$($Profile.Counter)")[2]
|
||
Write-Logfile -LogLine "Configured ExportFilename_Template is: $($Profile.ExportFilename_Template)"
|
||
|
||
$Profile.ExportSQLCheckQuery = ($ConfigFileContent."Profile_$($Profile.Counter)")[3]
|
||
Write-Logfile -LogLine "Configured ExportSQLCheckQuery is: $($Profile.ExportSQLCheckQuery)"
|
||
|
||
$Profile.ExportSQLProcedure = ($ConfigFileContent."Profile_$($Profile.Counter)")[4]
|
||
Write-Logfile -LogLine "Configured ExportSQLProcedure is: $($Profile.ExportSQLProcedure)"
|
||
|
||
$Profile.ExportSQLGetQuery_Template = ($ConfigFileContent."Profile_$($Profile.Counter)")[5]
|
||
Write-Logfile -LogLine "Configured ExportSQLGetQuery_Template is: $($Profile.ExportSQLGetQuery_Template)"
|
||
|
||
#Check if every Mandatory Value is set.
|
||
IF ($Profile.ExportPath -and $Profile.ExportFilename_Template -and $Profile.ExportSQLCheckQuery -and $Profile.ExportSQLProcedure -and $Profile.ExportSQLGetQuery_Template) {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "All required Variables are set for this Profile."
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "Not all required Variables are set for this Profile."
|
||
Write-Logfile -LogLine "Skipping Profile because of that reason!"
|
||
continue
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine "Unknown Profile type given! Valid are:"
|
||
Write-Logfile -LogLine "BulkExport, "
|
||
exit
|
||
|
||
} #end else
|
||
|
||
} #end else
|
||
|
||
Test-Path-withLogging -Path $($Profile.ExportPath) | Out-Null
|
||
|
||
IF ((Test-Path -PathType Container -Path $($Profile.ExportPath)) -eq $true) {
|
||
|
||
Write-Logfile -LogLine " "
|
||
$Profile.ExportPath = $Profile.ExportPath.Replace("%Timestamp1%",$Timestamp1)
|
||
$Profile.ExportPath = $Profile.ExportPath.Replace("%Timestamp2%",$Timestamp2)
|
||
$Profile.ExportPath = $Profile.ExportPath.Replace("%Timestamp3%",$Timestamp3)
|
||
Write-Logfile -LogLine "Prepared export path: $($Profile.ExportPath)"
|
||
|
||
IF ($DBType -eq "MSSQL") {
|
||
|
||
Try {
|
||
|
||
Write-Logfile -LogLine "Getting lines from db (undone files):"
|
||
$DBConnString = $DBConnString.Replace("\;",";")
|
||
$DBConnString = $DBConnString.Replace('"','')
|
||
$Profile.ExportSQLCheckQuery_Result = Invoke-Sqlcmd -ConnectionString $DBConnString -Query $($Profile.ExportSQLCheckQuery) -ErrorAction Stop
|
||
Write-Logfile -LogLine "Found $($Profile.ExportSQLCheckQuery_Result.count) line(s) in db"
|
||
|
||
} #end try
|
||
|
||
Catch {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "SQL Query failed."
|
||
Write-Logfile -LogLine "Skipping Profile because of that reason!"
|
||
Write-Logfile -LogLine $Error
|
||
continue
|
||
|
||
} #end catch
|
||
|
||
IF ($($Profile.ExportSQLCheckQuery_Result.count) -gt 0) {
|
||
|
||
#loop for every found stackid in db
|
||
FOREACH ($DBline in $($Profile.ExportSQLCheckQuery_Result)) {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "########### Processing StackID: $($DBLine[0]) ###########"
|
||
|
||
$Timestamp1 = $(Get-Date -Format 'ddMMyyyy')
|
||
$Timestamp2 = $(Get-Date -Format 'ddMMyyyy_HHmmss')
|
||
$Timestamp3 = $(Get-Date -Format 'ddMMyyyy_HHmmssffff')
|
||
|
||
$Profile.ExportFilename = $Profile.ExportFilename_Template
|
||
$Profile.ExportFilename = $Profile.ExportFilename.Replace("%StackID3%",$($DBLine[0]))
|
||
$Profile.ExportFilename = $Profile.ExportFilename.Replace("%Timestamp1%",$Timestamp1)
|
||
$Profile.ExportFilename = $Profile.ExportFilename.Replace("%Timestamp2%",$Timestamp2)
|
||
$Profile.ExportFilename = $Profile.ExportFilename.Replace("%Timestamp3%",$Timestamp3)
|
||
Write-Logfile -LogLine "Prepared export filename: $($Profile.ExportFilename)"
|
||
|
||
$Profile.ExportSQLProcedure_Result = Start-SQLDB-StoredProcedure-withLogging -ConnString $DBConnString -ExportSQLProcedure $($Profile.ExportSQLProcedure) -ExportSQLProcedure_STACK_ID $($DBLine[0]) -ExportSQLProcedure_FILE_NAME $($Profile.ExportFilename)
|
||
|
||
If ($($Profile.ExportSQLProcedure_Result) -eq 0) {
|
||
|
||
$Profile.ExportSQLGetQuery = $Profile.ExportSQLGetQuery_Template
|
||
$Profile.ExportSQLGetQuery = $Profile.ExportSQLGetQuery.Replace("%StackID%",$($DBLine[0]))
|
||
$Profile.ExportSQLGetQuery = $Profile.ExportSQLGetQuery.Replace("%ExportFilename%",$($Profile.ExportFilename))
|
||
$Profile.ExportSQLGetQuery_Result = Invoke-Sqlcmd -ConnectionString $DBConnString -Query $($Profile.ExportSQLGetQuery) -ErrorAction Stop
|
||
|
||
IF ($Profile.ExportSQLGetQuery_Result[0] -gt $null) {
|
||
|
||
Write-Logfile -LogLine "Exporting to: $($Profile.ExportPath)\$($Profile.ExportFilename)"
|
||
$Profile.ExportSQLGetQuery_Result[0] | Out-File "$($Profile.ExportPath)\$($Profile.ExportFilename)"
|
||
|
||
Write-Logfile -LogLine "########### StackID is done ###########"
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "SQL Query failed."
|
||
Write-Logfile -LogLine "Skipping Profile because of that reason!"
|
||
Write-Logfile -LogLine $Error
|
||
continue
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "SQL Procedure failed."
|
||
Write-Logfile -LogLine "Skipping Profile because of that reason!"
|
||
Write-Logfile -LogLine $Error
|
||
continue
|
||
|
||
} #end else
|
||
|
||
Write-Logfile -LogLine " "
|
||
|
||
} #end foreach
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine "So here is nothing to do!"
|
||
|
||
} #end else
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "DBType $DBType is currently not implemented!"
|
||
Write-Logfile -LogLine "Skipping Profile because of that reason!"
|
||
continue
|
||
|
||
} #end else
|
||
|
||
|
||
|
||
} #end if
|
||
|
||
ELSE {
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "ExportPath cannot be created!"
|
||
|
||
} #end else
|
||
|
||
} UNTIL ($($Profile.Counter) -eq $($Profile.Count))
|
||
|
||
} #end if
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "--------------------------------------------------------------------------------"
|
||
Write-Logfile -LogLine "Checking for old LogFiles."
|
||
Write-Logfile -LogLine "--------------------------------------------------------------------------------"
|
||
|
||
Remove-Item-withLogging -Path $LogPath -FileKeepTime $LogFileKeepTime -FileBaseName $ScriptName
|
||
|
||
Write-Logfile -LogLine " "
|
||
Write-Logfile -LogLine "********************************************************************************"
|
||
Write-Logfile -LogLine "Program Completed: $ScriptName on $env:COMPUTERNAME,"
|
||
Write-Logfile -LogLine "from Account $env:USERDOMAIN\$env:USERNAME."
|
||
Write-Logfile -LogLine "********************************************************************************"
|
||
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
########################################### finishing part ############################################
|
||
#-----------------------------------------------------------------------------------------------------#
|
||
|
||
Remove-Variable -Name pProfileType -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name pExportPath -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name pExportFilename -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name pExportSQLCheckQuery -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name pExportSQLProcedure -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name pExportSQLGetQuery -Force -ErrorAction SilentlyContinue
|
||
|
||
Remove-Variable -Name ScriptName -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name ScriptPath -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name ConfigFile -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name ConfigFileContent -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Timestamp1 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Timestamp2 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Timestamp3 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Timestamp4 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Item -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Items -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name VersionSeperator -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name FileDelayAge -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name FileTest1 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name FileTest2 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name FileTest3 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name PathTest -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name PathTest2 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name PathTests -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Result -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Counter1 -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name LogFile -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name LogFileKeepTime -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name LogPath -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name LogPaths -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Module -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Modules -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name ModuleOverrideSourcePath -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name ModuleDefaultSourcePath -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name ModuleHKLMRegistryPath -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name ModuleHKCURegistryPath -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name Profile -Force -ErrorAction SilentlyContinue
|
||
|
||
Remove-Variable -Name DBType -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name DBLine -Force -ErrorAction SilentlyContinue
|
||
Remove-Variable -Name DBConnString -Force -ErrorAction SilentlyContinue
|
||
|
||
$error.clear() |