December 21, 2018 at 6:50 am
Hi All,
I have a simple SSIS package, Which take the data from Oracle (OLEDB) source and puts in SQL server.
I call the package using the control M job " \Program Files (x86)\Microsoft SQL Server\130\DTS\Binn"
I use SQL server 2016. I am getting the out of memory issue. My server is having 128 GB ram and i have allocated 100 GB to sql server.
I also tried with option of setting default buffer max rows but still the job fails, after multiple runs job succeeds.
When i run the package via the BIDS studio , It goes fine.
Can anyone please help me.
Error message as below.
[exec] Description: The Execute method on the task returned error code 0x80070008 (Could not load file or assembly 'System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)). The Execute method must succeed, and indicate the result using an "out" parameter.
[exec] End Error
[exec] Error: 2018-12-20 14:51:37.73
[exec] Code: 0xC0047011
[exec] Description: The system reports 97 percent memory load. There are 137155264512 bytes of physical memory with 3279581184 bytes free. There are 4294836224 bytes of virtual memory with 139726848 bytes free. The paging file has 158599798784 bytes with 18602225664 bytes free.
[exec] Description: The Data Flow task failed to create a buffer to call PrimeOutput for output "OLE DB Source" (216) on component "OLE DB Source Output" (227). This error usually occurs due to an out-of-memory condition.
[exec] Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Thanks
Bharath
December 21, 2018 at 7:41 am
Try starting it from the program files directory instead of program files (x86)
ssis memory errors
December 21, 2018 at 8:51 am
For me its a 32 bit solution so need to use 86 cannot use normal program files
December 21, 2018 at 9:00 am
why can't your SSIS be the 64 bit one?
are you using the Integration Services object model directly on your code?
or are you calling a external DLL from a Script task within SSIS and that DLL only works on 32 bit? if so talk to the vendor to get a 64 bit version of it.
Control-M if calling DTEXEC on a process can use either 32 or 64 bit version of it as it does not invoke the object model - its just a standard shell invoke with redirection of input/output buffers.
December 21, 2018 at 9:35 am
Hi All,
I just tried with 64 bit. But its running for ages now.
2 million record used to take 5 minutes , Now its running since 45 minutes.
Buildfile: G:\path\CSIPVT_build.xml
run_ssis_pkg_pwrpvt:
[echo] exec_dir === F:\Program Files\Microsoft SQL Server\130\DTS\Binn
[echo] package === G:\Path\bin\PKG_Name.dtsx
December 21, 2018 at 9:37 am
Control M is call the .bat script and inside the .bat script i am calling the SSIS package.
December 21, 2018 at 9:43 am
It loads one million rows and then gets stucks there without loading anything
December 21, 2018 at 9:47 am
frederico_fonseca - Friday, December 21, 2018 9:00 AMwhy can't your SSIS be the 64 bit one?
are you using the Integration Services object model directly on your code?
or are you calling a external DLL from a Script task within SSIS and that DLL only works on 32 bit? if so talk to the vendor to get a 64 bit version of it.Control-M if calling DTEXEC on a process can use either 32 or 64 bit version of it as it does not invoke the object model - its just a standard shell invoke with redirection of input/output buffers.
I just tried with 64 bit. But its running for ages now.
2 million record used to take 5 minutes , Now its running since 45 minutes.
Buildfile: G:\path\CSIPVT_build.xml
run_ssis_pkg_pwrpvt:
[echo] exec_dir === F:\Program Files\Microsoft SQL Server\130\DTS\Binn
[echo] package === G:\Path\bin\PKG_Name.dtsx
Control M is call the .bat script and inside the .bat script i am calling the SSIS package.
December 21, 2018 at 10:10 am
being slow is a different issue.
look at the logging of that package (if running from the command line as you are, /reporting EWIC should be on the command line), and check to see if the package is indeed still processing data.
Do you have a SQL Enterprise or Standard? If you have Enterprise you should be using the Attunity driver for Oracle as it is faster that any other driver from Microsoft/Oracle.
And on the load to SQL make sure you are using the fast load option - and eventually change the batch/commit size.
December 21, 2018 at 10:45 am
When using the oracle oledb driver, make sure to try some batch/buffer memory sizes. Currently working with batchsize 5000 instead of the default.
December 21, 2018 at 10:37 pm
frederico_fonseca - Friday, December 21, 2018 10:10 AMbeing slow is a different issue.
look at the logging of that package (if running from the command line as you are, /reporting EWIC should be on the command line), and check to see if the package is indeed still processing data.Do you have a SQL Enterprise or Standard? If you have Enterprise you should be using the Attunity driver for Oracle as it is faster that any other driver from Microsoft/Oracle.
And on the load to SQL make sure you are using the fast load option - and eventually change the batch/commit size.
The package itself is getting stuck for hours, I will try with the driver but need to get lot of permission to install any. I am using all the option of fast load etc.
I am using lookup inside the package which is caching 9 million rows and due to which its throwing memory error.
Yesterday i re-calculated te bufferrowmax it turned to be 21000 but still it was failing, When i gave bufferrowmax as 99 it worked.
I also tried to apply partial cache in lookup but it didnt, So not sure how many time i have to deploy the pakcgae after changing bufferrowmax size.
So looking for any of your expertise in this .
December 21, 2018 at 10:39 pm
Jo Pattyn - Friday, December 21, 2018 10:45 AMWhen using the oracle oledb driver, make sure to try some batch/buffer memory sizes. Currently working with batchsize 5000 instead of the default.
I am using lookup inside the package which is caching 9 million rows and due to which its throwing memory error.
Yesterday i re-calculated te bufferrowmax it turned to be 21000 but still it was failing, When i gave bufferrowmax as 99 it worked.
Batchsize with greater value is failing, Even 150 is not working
I also tried to apply partial cache in lookup but it didnt, So not sure how many time i have to deploy the pakcgae after changing bufferrowmax size.
So looking for any of your expertise in this .
December 22, 2018 at 2:45 am
if you need to cache that many rows you should probably redesign the package so that those joins are done in SQL Server itself and not on SSIS.
even if it means creating staging tables do do that process.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy