dpulley, What RDBMS do you use? I had a similar issue where I didn't want to risk large SQL result sets causing issues and wanted to write straight to a file. We use Oracle with stored procedures that return sys_refcursors as OUT parameters for capturing query output for writing to files (they can also be used as source objects for a crystal report). A sys_refcursor is basically a pointer to a result set. The stored procedure can be called from a job containing a powershell script task where I pass the procedure name and other parameters and the powershell script connects to the database, runs the procedure, removes double-quotes and writes to the path passed in using a stream-writer for efficiency. You could create a stored procedure that just selects all rows from the table and use the same mechanism. I tried to make it as generic as possible, using parameters for everything.
Caveat: I know enough Powershell to be dangerous and I cobbled this together from various examples so I'm sure there are improvements that could be made.
Powershell script:
######################################################################################################
##
## Generic powershell code to run the passed-in stored procedure and write the results to a file.
##
## The stored procedure should simply open a cursor as (select ...)
##
## Call this job from a VC task, passing all the parameters as job variables.
##
## This is needed as it seems VC does not handle data sets coming back from stored procedures in
## sys_refcursors. The stored procedure can be used as a data source in a Crystal report.
##
######################################################################################################
Param(
[string] $STORED_PROCEDURE, # Name of the stored procedure, with schema
[string] $REF_CURSOR_NAME, # Name of the SYS_REFCURSOR argument to the above
[string] $OUT_FILENAME, # Full path to the output file
[string] $DELIMITER, # Delimiter character
[int] $SKIP, # Rows to skip as column headers are included by default
[string] $DATASRC,
[string] $CONNSTR
)
[void][System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
$connectionstring = ($CONNSTR -f $DATASRC)
$dt = New-Object System.Data.DataTable
$command = New-Object Oracle.DataAccess.Client.OracleCommand($STORED_PROCEDURE,$connectionstring)
$command.CommandType = [System.Data.CommandType]::StoredProcedure
$params = New-Object Oracle.DataAccess.Client.OracleParameter($REF_CURSOR_NAME, [Oracle.DataAccess.Client.OracleDbType]::RefCursor, 0) `
-property @{Direction = [System.Data.ParameterDirection]::Output; Value = $value}
$command.parameters.add($params)
$dataAdapter = New-Object Oracle.DataAccess.Client.OracleDataAdapter($command)
[void]$dataAdapter.Fill($dt)
#To export to csv
$writer = New-Object System.IO.StreamWriter($OUT_FILENAME)
$dt | ConvertTo-Csv -Delimiter "$DELIMITER" -NoTypeInformation | Select -skip "$SKIP" |
ForEach-Object {
$writer.writeline($_ -replace '"', "") # Remove double-quotes. Data better not contain any!
}
$dt.Dispose
# End powershell script
Params:
datasource is from the TNSNAMES.ora file.
Example call:
Example stored procedure that is inside a package:
PROCEDURE get_SQL_output(prc OUT SYS_REFCURSOR) IS
BEGIN
OPEN prc FOR
select level
from dual
connect by level <= 10;
END get_SQL_output;
Edited by user
2019-01-30T20:21:20Z
|
Reason: Not specified