Functions/NewSsisVariable.ps1
|
Function New-ssisVariable { <# .Synopsis Drops Environment variable if it exists and creates an environment variable .Description Internal function Checks if a variable exists, and if it does then it drops and creates a new one. Used where we need to create a new variable, or where data type and value have changed at same time (easier to drop and re-create when this has occured). .Parameter sqlConn connection to SSIS Server .Parameter ssisVar The target variable we want to create/re-create .Parameter ssisProp The properties (ie folder/project/environment name) .Example See PublishSsisVariables for full context New-SsisVariable -sqlConn $sqlConnection -ssisVar $ssisVariable -ssisProp $ssisProperties #> [CmdletBinding()] param ( [System.Data.SqlClient.sqlConnection] $sqlConn, [PSCustomObject] $ssisVar, [hashtable] $ssisProp ) $sqlPublishssisVar = " IF EXISTS ( SELECT * FROM CATALOG.environment_variables variable WHERE variable.environment_id = ( SELECT environment.environment_id FROM CATALOG.environments environment INNER JOIN CATALOG.folders folder on folder.folder_id = environment.folder_id WHERE environment.NAME = @0 AND folder.NAME = @2 ) AND variable.NAME = @1 ) BEGIN EXEC CATALOG.delete_environment_variable @folder_name = @2 ,@environment_name = @0 ,@variable_name = @1 END EXEC CATALOG.create_environment_variable @folder_name = @2 ,@environment_name = @0 ,@variable_name = @1 ,@data_type = @3 ,@sensitive = @4 ,@value = @5 ,@description = @6" Write-Verbose "Creating $($ssisVar.variableName)..." -Verbose try { $sqlCmdPublishVar = New-Object System.Data.SqlClient.SqlCommand($sqlPublishssisVar, $sqlConn) $sqlCmdPublishVar.Parameters.AddWithValue("@0", $ssisProp.ssisEnvironmentName) | Out-Null $sqlCmdPublishVar.Parameters.AddWithValue("@1", $($ssisVar.variableName)) | Out-Null $sqlCmdPublishVar.Parameters.AddWithValue("@2", $($ssisProp.ssisFolderName)) | Out-Null $sqlCmdPublishVar.Parameters.AddWithValue("@3", $($ssisVar.dataType)) | Out-Null $sqlCmdPublishVar.Parameters.AddWithValue("@4", $($ssisVar.isSensitive)) | Out-Null Switch ($($ssisVar.dataType)) { Boolean {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::Bit).Value = [System.Convert]::ToBoolean($($ssisVar.value))} Byte {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::TinyInt).Value = ($($ssisVar.value))} DateTime {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::DateTime).Value = $($ssisVar.value)} Double {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::Float).Value = $($ssisVar.value)} Int16 {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::smallint).Value = $($ssisVar.value)} Int32 {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::int).Value = $($ssisVar.value)} Int64 {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::bigint).Value = $($ssisVar.value)} UInt32 {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::Variant).Value = $($ssisVar.value)} UInt64 {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::Variant).Value = $($ssisVar.value)} Single {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::Real).Value = $($ssisVar.value)} String {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::NVarChar).Value = $($ssisVar.value)} Decimal {$sqlCmdPublishVar.Parameters.Add("@5", [System.Data.SqlDbType]::Decimal).Value = $($ssisVar.value)} Default {"Data type for $($ssisVar.variableName) is not currently supported. Either contact developer of module or alter Set-EnvironmentVariableValue Function to support data type $($ssisVar.dataType)." Throw} } $sqlCmdPublishVar.Parameters.AddWithValue("@6", $($ssisVar.description)) | Out-Null $sqlCmdPublishVar.ExecuteNonQuery() | Out-Null Write-Verbose "SQL Script Succeeded. Checking variable exists..." -Verbose } catch { Write-Verbose "Publishing $($ssisVar.variableName) failed." -Verbose Write-Error $_.Exception Throw } } |