I’m trying to get a hang of using Powershell and use it to automate ETLs through VS. Currently I’m working on moving data from Oracle into SQL server using Powershell and launching it using an Execute Process Task.

I tried the below script and it works fine on VS when the data is in range of ~5 Million rows. However, when I try to run another which has ~15 million rows it results in this error (though I am not sure if the row count is the problem). So the script is fine but not sure what is causing the error. Note that the script works fine when running on Powershell or Powershell ISE directly even when the data is ~15 million rows. The issue happens only when calling it on Visual Studio.

Executable on VS
C:WindowsSystem32WindowsPowerShellv1.0powershell.exe

The arguments I tried are (neither worked)
-F C:UsersHarryImport.ps1
OR
C:UsersHarryImport.ps1

Script:

add-type -path 'C:OracleClientODP.NETmanagedcommonOracle.ManagedDataAccess.dll';
$Orclconn = "User Id=Oracle_Net;Password=*****;Data Source=Nexus;Connection Timeout=0";
$qry = "SELECT Cust_id, Relation_id, Order_id, quantity
FROM Cust_Order ob
WHERE Pay_method = '110'
AND Set = 0";
$adapter = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter($qry, $Orclconn);
$tbl = new-object System.Data.DataTable('T_Order_Intake');
$adapter.Fill($tbl);
$sqlconn = "server=Customer_Nexus;database=Cust_Order;trusted_connection=true;Connection Timeout=0";
$sqlld = new-object system.data.sqlclient.Sqlbulkcopy($sqlconn);
$sqlld.BulkCopyTimeout = 0;
$sqlld.BatchSize = 10000;
$sqlld.DestinationTableName="dbo.T_Order_Intake";
$sqlld.WriteToServer($tbl);

Error:

Exception calling "Fill" with "1" argument(s): "Exception of type 'System.OutOfMemoryException' was thrown."
At C:UsersHarryImport.ps1:14 char:1
+ $adapter.Fill($tbl);
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], Meth
   odInvocationException
    + FullyQualifiedErrorId : OutOfMemoryException

The last column in the query “Quantity” is a varchar(4000) so I tried to cast it to smaller datatype thinking that it might be that column that’s causing the issue. I also tried to drop this column and run the script but neither worked. I tried to reduce the batchsize but it did’nt help and I’m still learning scripting and not have much idea what else can be done there.