$ErrorActionPreference="Stop" Set-PSDebug -Strict # This is the Redshift table #CREATE TABLE mocsdw.psql_test #( # id INTEGER, # varchar_255 VARCHAR(255), # date_dt DATE, # varchar_10 VARCHAR(10), # timestamp_dt TIMESTAMP #) #DISTSTYLE EVEN; Write-Host Started at (Get-Date -Format g) [System.Boolean]$b = [environment]::Is64BitProcess if($b) { Write-Host '64bit PowerShell detected' -ForegroundColor Green } else { Write-Host '32bit PowerShell detected' -ForegroundColor Green } [string] $TableName = "psql_test" [System.Data.OleDb.OleDbConnectionStringBuilder]$strBuilder = New-Object System.Data.OleDb.OleDbConnectionStringBuilder -ArgumentList "Provider=SQLOLEDBRSNP.1;Data Source=redmocs.cbzkr8tp4hxq.us-east-1.redshift.amazonaws.com;" $strBuilder["AWS_ACCESS_KEY"] = "XYZ" $strBuilder["AWS_SECRET_KEY"] = 'ABC' $strBuilder["AWS_BUCKET"] = "mocsdw01" $strBuilder["Initial Catalog"] = "mocsdw" $strBuilder["User ID"] = "QWE" $strBuilder["Password"] = "PWD" $strBuilder["Port"] = 5439 [System.Data.OleDb.OleDbConnection]$conn = New-Object System.Data.OleDb.OleDbConnection -ArgumentList $strBuilder.ConnectionString [System.Data.OleDb.OleDbDataAdapter]$adapter = New-Object System.Data.OleDb.OleDbDataAdapter -ArgumentList "select id, varchar_255, date_dt, varchar_10, timestamp_dt from mocsdw.""$TableName"";", $conn $adapter.MissingSchemaAction = [System.Data.MissingSchemaAction]::AddWithKey $adapter.MissingMappingAction = [System.Data.MissingMappingAction]::Passthrough [System.Data.DataSet]$dataSet = New-Object System.Data.DataSet try { $conn.Open() #$adapter.FillSchema($dataSet, [System.Data.SchemaType]::Source, $TableName) $adapter.Fill($dataSet, $TableName) [System.Data.OleDb.OleDbCommandBuilder]$cmdBldr = New-Object System.Data.OleDb.OleDbCommandBuilder -ArgumentList $adapter $dr = $dataSet.Tables[$TableName].NewRow() $dr["id"] = 1 $dr["varchar_255"] = "Zxcvbnm" $dr["date_dt"] = [System.DateTime]::Parse("01/03/2019") $dr["varchar_10"] = "Asdfghjkl" $dr["timestamp_dt"] = [System.DateTime]::Now $dataSet.Tables[$TableName].Rows.Add($dr) Write-Host $dataSet.Tables[$TableName].Rows.Count; Write-Host Started Update at (Get-Date -Format g) $adapter.Update($dataSet, $TableName) Write-Host Finished Update at (Get-Date -Format g) #Write-Host $conn.State } finally { $dataSet.Clear(); $conn.Close() }