locked
SSIS Packages Being to Fail RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for your help in advance.  I have three SSIS packages that run nightly to import flat 3 flat files dropped into a folder by a legacy system.  The packages have been working perfectly for approximately one year.  However, in the last week, the three packages have started to fail nightly with Event ID 6517 and the log message:

    SSISDB.dbo[runtime].14 Could not load file or assembly 'System, 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)

    After researching the error, I found comments regarding possible CLR inconsistencies based on updates or patches.  So I restarted the server, which resolved the problem for five days, but am now getting the failures again.  I'm not sure where to go from here.  Any help would be appreciated.

    Thursday, April 2, 2015 12:23 PM

All replies

  • User-2010311731 posted

    It looks like you are running out of disk space on the server.

    Matt

    Thursday, April 2, 2015 3:18 PM
  • User1122355199 posted

    Thanks for the response.  Not likely.  4.5 TB free only 912 GB used.  So I don't see how it is a disk space error.

    Thursday, April 2, 2015 3:25 PM
  • User77042963 posted


    BlobTempStoragePath and BufferTempStoragePath Properties of your SSIS Dataflow

    Check these links:

    http://blogs.msdn.com/b/sqlperf/archive/2007/05/01/set-blobtempstoragepath-and-buffertempstoragepath-to-fast-drives.aspx
    http://www.mssqltips.com/sqlservertip/1867/sql-server-integration-services-ssis-performance-best-practices/
    http://www.bimonkey.com/2008/04/blobtempstoragepath-and-buffertempstoragepath/

    Thursday, April 2, 2015 4:04 PM
  • User1122355199 posted

    Thanks for the response.  I'm not sure I'm completely following.  On the first import task, neither BLOBTempStorgae nor BufferTempStorage have anything set in them.  None of the disks on this server are remotely full, so this might be overflowing the allocated SQL Server memory in RAM.  Is this correct?  Is this also why the reboot casued things to wrk again for a period?  Why does it not affect other SSIS packages on this server that seem to be still running correctly?

    Thanks again for the help.

    Thursday, April 2, 2015 4:46 PM
  • User77042963 posted

    Have yo checked the files' size in question?

    My guess is that these files are significantly large compared to other files in other SSIS packages.

    Thursday, April 2, 2015 4:49 PM
  • User1122355199 posted

    4 MB.  Not much for modern machines.  The working SSIS packages are backup and export functions.

    Thursday, April 2, 2015 5:00 PM
  • User77042963 posted

    If I were you, I would follow the suggestions from the links I posted early and assign value to BufferTempStoragePath a drive that is large enough and test the package to see whether this is the right path to solve the problem.

    Thursday, April 2, 2015 5:16 PM
  • User1122355199 posted

    Again thanks for the response.  I made changes to the BufferTempStoragePath to point to a folder with plenty of space.  The package now executes in Visual Studio, but when I go to deploy the SSIS package, the deployment wizard fails with the same not enough space error.  I'm not sure what to do next.  I also checked in the task manager looking for any clues and see that SQL Server is using 3,587 MB of memory which seems extremely high.  I'm not sure where to go next.

    Thursday, April 2, 2015 9:46 PM
  • User1711366110 posted

    The package now executes in Visual Studio, but when I go to deploy the SSIS package, the deployment wizard fails with the same not enough space error.  I'm not sure what to do next.  I also checked in the task manager looking for any clues and see that SQL Server is using 3,587 MB of memory which seems extremely high.  I'm not sure where to go next.

      As per your case, the following link which may guide you to fix this issue :

    https://technet.microsoft.com/en-gb/library/cc966529.aspx

    --
    with regards,
    Edwin

    Friday, April 3, 2015 5:19 AM
  • User1122355199 posted

    OK.  I made the changes to the package, which ran for two nights, but is now failing again.

    Monday, April 6, 2015 8:39 AM
  • User1649953660 posted

    Solved.

    I know this is an old issue, but it came to me this week. The amount of data on one of the DataFlows increased absurdly.

    The SQL SSIS Server has only 4 MB RAM memory. It has enough disk space.  I tried decreasing the Data Flow buffer size, buffer max rows,  disabling Lookup Caches and nothing worked.

    Finally I decreased Engine Threads and: Gotcha ! It worked perfectly on the server.

    Hope it helps someone.

    Regards,

    Mauricio Bastos 

    Thursday, March 8, 2018 6:05 PM