July 10, 2024 at 8:34 pm
I am fairly new to SSIS and working on executing the existing package which is very complex. The last step of this package executes a child package, which reads the data from the source, adds some logic, and gets the data into the staging. All the steps are running fine except when the last step is run. I see a bunch of 'A buffer failed while allocating bytes' related errors. The system reports 30 percent of memory load' The only setting I have changed is set the AutoAdjustBufferSize to true. I am not sure how to troubleshoot this issue. Can anyone guide me?
SSIS server has 16 GB of memory
Dest server has 256 GB of memory.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 10, 2024 at 8:55 pm
The only buffer error we ever had was SSIS trying to buffer in a location it didn't have access to.
We corrected this by setting the BufferTempStoragePath property on the data flow task to a location the proxy account already had access to.
Our troubleshooting to find the issue was to use procmon on the server while running the job.
But the error was different I think, that was years ago, haven't run into that for a long time. We also have a rule of only one primary data flow task per package. That was mostly to make things easier to manage and cut down on loading times.
Also is this server running SQL and SSIS? If SQL is also running on it I'd look at the memory allocation for SQL and make sure you're leaving 6-8GB for OS and other things like SSIS (based on 16GB)
July 10, 2024 at 9:14 pm
Also is this server running SQL and SSIS? If SQL is also running on it I'd look at the memory allocation for SQL and make sure you're leaving 6-8GB for OS and other things like SSIS (based on 16GB)
SSIS and SQL, both run on different servers.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 10, 2024 at 11:18 pm
look at this thread as it gives a good explanation - better to turn that setting off - https://stackoverflow.com/questions/59505490/ssis-2019-autoadjustbuffersize-causes-buffer-allocation-error
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply