Reputation: 43
I'm working on a script that is expected to perform the following:
*Directly running the (sub)queries on the Oracle database was out of option since the query being performed on the Oracle database (raw data) takes about 5 hours so raw data is imported to Microsoft access first for processing, and then running the (sub)queries.
I have already created the Powershell script for each one of the items and one master script which calls and supplies the arguments for each. They have been unit tested.
1 - 01.dbquery.ps1 [Parameter: NONE; Returns: RAW DATA]
2 - 02.mdbimport.ps1 [Parameter: RAW DATA(from 1),MDB FILE Return: NONE]
3 - 03.mdb export.ps1 [Parameter: EXCEL FILE, MDB FILE; Return: NONE]
master script.ps1
However, when integrating script 1 and 2, Im getting the error System.Management.Automation.ParameterBindingException: A positional parameter cannot be found that accepts argument 'System.Data.DataRow'.
Note that script 1 returns System.Data.Datatable value from the queried result on the Oracle DB and I'm trying to figure out why script 2 does not seem to accept this value. I've also tried stating the type System.data.datarow
on the parameter declaration but still to no avail as I'm getting the error
Cannot process argument transformation on parameter 'RawData'. Cannot convert the "System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow" value of type "System.String" to type "System.Data.DataRow".
Below are the code snippets for each script
master script.ps1
## Setup reusable variables (e.g. MDB, excel templates)
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
$Date=(get-date).tostring('MMddyyy')
$MdbFile = $scriptPath+"\Pts on Spans Analysis "+$date+".mdb"
$ExcelFile = $scriptPath+'\Pts on Spans Action '+$date+'.xls'
## 01. Connect to Oracle DB and run main query for PS raw data
write("**Starting Oracle database query and storing results... -- "+(get-date))
$01dbquery = $scriptPath+"\01dbquery.ps1"
$01ret = Invoke-Expression "& `"$01dbquery`""
write("**Finished querying Oracle DB. -- "+(get-date)); write-host
## 02. Import raw data to MDB template (Microsoft access) -- ImportedPtsonSpans
write("**Starting import of PS raw data to MDB... -- "+(get-date))
$02mdbimport = $scriptPath+"\02mdbimport.ps1"
Invoke-Expression "& `"$02mdbimport`" -RawData $01ret -MdbFile `"$MdbFile`""
write("**Finished import of PS raw data. -- "+(get-date)); write-host
## 03. Process MS Access data and export into excel
write("**Starting export of processed PS data into Excel... -- "+(get-date))
$03mdbexport = $scriptPath+"\03mdbexport.ps1"
Invoke-Expression "& `"$03mdbexport`" -ExtractName `"$ExcelFile`" -MdbFile `"$MdbFile`""
write("**Finished data export. Pts and Span Extract Completed -- "+(get-date)); write-host
01dbquery.ps1
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
function Get-DataTable
{
Param(
[Parameter(Mandatory=$true)]
[System.Data.OracleClient.OracleConnection]$conn,
[Parameter(Mandatory=$true)]
[string]$sql
)
$cmd = New-Object System.Data.OracleClient.OracleCommand($sql,$conn)
$da = New-Object System.Data.OracleClient.OracleDataAdapter($cmd)
$dt = New-Object System.Data.DataTable
[void]$da.Fill($dt)
return ,$dt
}
## Initiate database credential variables
<more codes here>
## Setup database connection
<more codes here>
## Perform database queries and store results
$QueryPath = $scriptPath+"\queries\main\Pts Span Qry.txt"
$Query = get-content $QueryPath | out-string
# *** Entry Point to Script ***
$RawData = Get-DataTable $conn $Query ; $RawData
02mdbimport.ps1
param
(
[Parameter(Mandatory=$true)]$RawData,
[Parameter(Mandatory=$true)]$MdbFile
)
$ErrorActionPreference = 'Stop'
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
Function Connect-Database($MdbFile, $Tables)
{
<codes>
} #End Update-Records
Function Update-ImportedPtsonSpans
{
<codes>
} #End Update-ImportedPtsonSpans
# *** Entry Point to Script ***
## Perform import of raw data into Microsoft Access Database (MDB)
$Tables = "ImportedPtsonSpans"
# Test if raw data is passed
write-host Start Test raw data and MdbFile
$RawData
$MdbFile
write-host End Test raw data and MdbFile
#
Connect-DataBase -MdbFile $MdbFile -tables $Tables
03mdbexport.ps1
param
(
[Parameter(Mandatory=$true)]$ExtractName,
[Parameter(Mandatory=$true)]$MdbFile
)
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
Function Export-Data($Queries,$MdbFile,$ExtractName)
{
<codes> }
} #End Export-Data
Function Export-To-Excel($ExtractName,$QueryName,$CurResult)
{
<codes> } # End Export-To-Excel
# *** Entry Point to Script ***
$QueryPath = $scriptPath+"\queries\*.txt"
$Queries = Get-ChildItem $QueryPath
Export-Data -Queries $Queries -MdbFile $MdbFile -ExtractName $ExtractName
Thanks in advance!
Upvotes: 1
Views: 408