test.ps1
|
Import-Module .\MSBITools.psd1 -Force $SqlServerConnection = New-SqlServerConnection -ServerName 'localhost' $SqlServerConnection.Open() #$sqlCmd = "EXEC [SSISDB].[catalog].[create_execution] @package_name @folder_name @project_name @use32bitruntime @reference_id" $FolderName = "DEV" $ProjectName = "Asset_Load" $PackageName = "Asset_Load.dtsx" $Synchronized = $true $sqlCmdText = "[SSISDB].[catalog].[create_execution]" $props = @{TypeName = ' System.Data.SqlClient.SqlCommand'; ArgumentList = ($SqlCmdText, $SqlServerConnection) ErrorAction = 'Stop'; ErrorVariable = 'ErrorVar'} try { Write-Verbose "Creating SQL command" Write-Debug "Command properties : $($props | Out-String)" $sqlCommand = New-Object @props } catch { Write-Error "Error creating SQL command : $ErrorVar" } Write-Verbose "Creating execution value parameter set for executing package" $sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure $sqlCommand.Parameters.AddWithValue("folder_name", $FolderName) | Out-Null $sqlCommand.Parameters.AddWithValue("project_name", $ProjectName) | Out-Null $sqlCommand.Parameters.AddWithValue("package_name", $PackageName) | Out-Null $sqlCommand.Parameters.Add("use32bitruntime", [System.Data.SqlDbType]::Bit).Value = $RunIn32Bit.IsPresent $sqlCommand.Parameters.Add("execution_id", [System.Data.SqlDbType]::BigInt).Direction = [System.Data.ParameterDirection]::Output $sqlCommand.ExecuteNonQuery() | Out-Null [int64]$execId = $sqlCommand.Parameters["execution_id"].Value $execId if ($Synchronized) { $sqlCmdText = "[SSISDB].[catalog].[set_execution_parameter_value]" $props = @{TypeName = ' System.Data.SqlClient.SqlCommand'; ArgumentList = ($SqlCmdText, $SqlServerConnection) ErrorAction = 'Stop'; ErrorVariable = 'ErrorVar'} try { Write-Verbose "Creating SQL command" Write-Debug "Command properties : $($props | Out-String)" $sqlCommand = New-Object @props } catch { Write-Error "Error creating SQL command : $ErrorVar" } Write-Verbose "Creating execution value parameter set for executing package '$($package1.Name)' synchronously" $sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure $sqlCommand.Parameters.AddWithValue("execution_id", $execId) | Out-Null $sqlCommand.Parameters.AddWithValue("object_type", 50) | Out-Null $sqlCommand.Parameters.AddWithValue("parameter_name", "SYNCHRONIZED") | Out-Null $sqlCommand.Parameters.AddWithValue("parameter_value", 1) | Out-Null $sqlCommand.ExecuteNonQuery() | Out-Null } $sqlCmdText = "[SSISDB].[catalog].[start_execution]" $props = @{TypeName = ' System.Data.SqlClient.SqlCommand'; ArgumentList = ($SqlCmdText, $SqlServerConnection) ErrorAction = 'Stop'; ErrorVariable = 'ErrorVar'} try { Write-Verbose "Creating SQL command" Write-Debug "Command properties : $($props | Out-String)" $sqlCommand = New-Object @props } catch { Write-Error "Error creating SQL command : $ErrorVar" } Write-Verbose "Starting execution of package '$($package1.Name)' " $sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure $sqlCommand.CommandTimeout = 300 $sqlCommand.Parameters.AddWithValue("execution_id", $execId) | Out-Null $sqlCommand.ExecuteNonQuery() | Out-Null Get-SsisCatalogExecution -SqlServerConnection $SqlServerConnection $execId #$param = $sqlCommand.Parameters.Add("@ExecutionId", System.Data.SqlDbType.VarChar ) #retval.Direction = ParameterDirection.ReturnValue; |