I've a some packages in SSIS 14 in order to extract the data from a lot of operational databases and load into my Data Warehouse.

I've the following virtual machine:

  • Windows Server R2 Standard
  • RAM: 24 GB Processor: 4 processors & 2.52
  • GHZ OS: 64-bit Operating System

I've one package for each operation database and each packages extract in average 3 million records by day...

I am getting very worried and desperate because I can not load without getting the following error (or similar):

Error: The system reports 55 percent memory load. There are 25769267200 bytes of physical memory with 11377299456 bytes free. There are 4294836224 bytes of virtual memory with 1408303104 bytes free. The paging file has 71665074176 bytes with 57322270720 bytes free.

In order to avoid this error I already do:

  • Add more drives to the machine (one for TempDB and another for Logs)
  • Changes the SSIS paths to the new drives
  • Try to insert only one day by execution

But I'm always getting the same error... This is one of my data flow:

enter image description here

I'm getting desperate because my customers are asking about the data and I can't create a good solution using SSIS...

What can I do in order to solve this problem?

  • Have you try to configure batchsize and commit size in you ´´Ole DB Destination´´? – Krismorte Jun 5 '17 at 23:04
  • If you move all that diabolical processing into the source SQL statement all your worries will be over. – Nick.McDermaid Jun 5 '17 at 23:17
  • Most of the errors occurs in Lookup object. It's normal? – SaCvP Jun 6 '17 at 7:44
  • You've added drives to the machine - have you considered adding memory? Noting that the error says something about memory. Of course, I'm not sure why a system would error out when it's reporting that almost half the memory is still available. Remember that SSIS does not use SQL Server's memory. Consider running the SSIS package on a machine that's not also running SQL Server itself. – RDFozz Jun 6 '17 at 15:59
  • Is better use SSIS than T-SQL in this case? – SaCvP Jun 6 '17 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.