<#
This script is used to Verify Table Schema for PeopleSoft system from MS SQL
server. The data is extracted in flat file
and saved to the directory specified by the user.
#>
$version = "1.0"
Clear-Host
Write-Host "Starting Extraction Script";
# Specify the server details below from which data will be extracted and the export
directory to which data will be copied
#==================================================================================
===================================
Write-Host "Setting Database server, database and Extraction Folder Path";
$SeverName = "USMDCEUDB70011\KAAP,1113"
$database = "JDE920"
$path = "C:\Temp\JDE\Schema\";
$Prefix = "PS_" # only use alphanumeric and _ or -. donot use / or \
#==================================================================================
===================================
# Parameters to identify ERP, Client, Verion and year
#==================================================================================
===================================
$ERP = "'PS'"
$VERSION = "'920'"
$CLIENT_NAME = "'Equinox'"
$YEAR = "'2024'"
$DBMS = "'MSSQL'"
#==================================================================================
===================================
#Specify BCP Paramater
#==================================================================================
===================================
Write-Host "Setting BCP Extraction Paramaters";
$packetsize = 20000
$batchsize = 15000
$format = "w"
#==================================================================================
===================================
#specify extraction paramters
#==================================================================================
===================================
Write-Host "Extracting Table Schema.....";
#==================================================================================
===================================
#Specificy the File Names for KPMG Data Extraction log and BPC Log
#==================================================================================
===================================
Write-Host "Setting KPMG record count log, BPC Log, SQL log and General Paramer Log
file Paramaters";
$RecountCountLogFileName = $path + $Prefix + "KPMG_EXTRACTION_LOG_" + (Get-
Date).Month + "_" + (Get-Date).Day + "_" + (Get-Date).year + "_" + (Get-
Date).Hour + "_" + (Get-Date).Minute + "_" + (Get-Date).Second +".txt"
# create logfile hearder record
"FILENAME#|#TABLENAME#|#RECORD_COUNT#|#DATETIME" > $RecountCountLogFileName
$BPCLogFileName = $path + $Prefix + "KPMG_BPC_LOG_" + (Get-Date).Month + "_" +
(Get-Date).Day + "_" + (Get-Date).year + "_" + (Get-Date).Hour + "_" + (Get-
Date).Minute + "_" + (Get-Date).Second +".txt"
$SQLLogFileName = $path + $Prefix +"KPMG_SQL_LOG_" + (Get-Date).Month + "_" +
(Get-Date).Day + "_" + (Get-Date).year + "_" + (Get-Date).Hour + "_" + (Get-
Date).Minute + "_" + (Get-Date).Second +".txt"
$GeneralParamterLogFileName = $path + $Prefix + "KPMG_GEN_LOG_" + (Get-Date).Month
+ "_" + (Get-Date).Day + "_" + (Get-Date).year + "_" + (Get-Date).Hour + "_"
+ (Get-Date).Minute + "_" + (Get-Date).Second +".txt"
#==================================================================================
===================================
#Generate Parameter log file
#==================================================================================
===================================
"Extraction code version: " + $version > $GeneralParamterLogFileName
"Tables: " + $Extraction_Table >> $GeneralParamterLogFileName
"Server Name: " + $SeverName >> $GeneralParamterLogFileName
"Database Name: " + $database >> $GeneralParamterLogFileName
"Extraction Path: " + $path
"Machine name from which script was run: " + $computerSystem.Name >>
$GeneralParamterLogFileName
"BPC Packet size Paramter:" + $packetsize >> $GeneralParamterLogFileName
"BPC batch size Paramter:" + $batchsize >> $GeneralParamterLogFileName
"BPC format paramter:" + $format >> $GeneralParamterLogFileName
#Generate Parameter log file
#==================================================================================
===================================
Write-Host "Extracting table Schema"
$TableName = "INFORMATION_SCHEMA.COLUMNS"
"Extracting Tabke Schema" >> $BPCLogFileName
$ExtractionFileName = $path + $Prefix + "ALL_TAB_COLS" + "_" + (Get-
Date).Month + "_" + (Get-Date).Day + "_" + (Get-Date).year + "_" + (Get-
Date).Hour + "_" + (Get-Date).Minute + "_" + (Get-Date).Second +".txt"
$ExtractionCountTemp = $path + "TempCount.txt"
$SQLHeader = "select
'ERP',
'VERSION',
'CLIENT_NAME',
'YEAR',
'DBMS',
'TABLE_SCHEMA',
'TABLE_NAME',
'COLUMN_NAME',
'DATA_TYPE',
'CHARACTER_MAXIMUM_LENGTH',
'NUMERIC_PRECISION',
'ORDINAL_POSITION',
'IS_NULLABLE',
1 as OBCol"
$SQL = "SELECT
$ERP,
$VERSION,
$CLIENT_NAME,
$YEAR,
$DBMS,
cast(TABLE_SCHEMA as nvarchar(100)),
cast(TABLE_NAME as nvarchar(100)),
cast(COLUMN_NAME as nvarchar(100)),
cast(DATA_TYPE as nvarchar(100)),
cast(CHARACTER_MAXIMUM_LENGTH as nvarchar (100)),
cast(NUMERIC_PRECISION as nvarchar (100)),
cast(ORDINAL_POSITION as nvarchar(100)),
Cast (IS_NULLABLE as nvarchar(100)),
2 as OBCol
FROM
INFORMATION_SCHEMA.COLUMNS
order by OBCol"
$finalSQL = $SQLHeader + " union " + $SQL
$finalSQL > $SQLLogFileName
"==========================================================" >>
$SQLLogFileName
bcp $finalSQL queryout $ExtractionFileName -S $SeverName -d $database -T -
$format -t"|" -b $batchsize -a $packetsize >> $BPCLogFileName
$SQLCount = "select " + "'" + $ExtractionFileName + "', '" + $TableName +
"' ," + " (select count(1) from " + $TableName + ") as count" + ", '" + (Get-
Date).Month + "/" + (Get-Date).Day + "/" + (Get-Date).year + " " + (Get-
Date).Hour + ":" + (Get-Date).Minute + ":" + (Get-Date).Second + "'"
#Write-Output SQLCount
bcp $SQLCount queryout $ExtractionCountTemp -S $SeverName -d $database -T -
$format -t"#|#" -b $batchsize -a $packetsize >> $BPCLogFileName
Add-Content -Path $RecountCountLogFileName -Value (Get-Content
$ExtractionCountTemp)
$ExtractionFileName = ""
$SQLHeader = ""
$SQL = ""
$SQLCount = ""
$TableName = ""
$finalSQL = ""
"." >> $BPCLogFileName
"." >> $BPCLogFileName
"Extracting Completed =====" >> $BPCLogFileName
Write-Host "Table Extraction schema completed";
#END Extract Table Schema
#==================================================================================
===================================
