CODEWITHSUNDEEP
sql-servervisual-studio-2012ssisout-of-memorybuffer
Michael De Matos
Michael De Matos

Reputation: 3

Package runs in Visual but not in Job- Buffer Error- Sql server

My package runs perfectly from Visual Studio. I've also set up a Master Job package that essentially runs all the tasks for the ETL. When I try to run tasks from the deployed packages on the production SQL server, some pass and others fail. The Following issues occur:

The Dataflow task failed to create a buffer to call the PrimeOutput for ADO NET source: error usually occurs to out-of-memory condition

The system reports 96 percent memory load. This is strange for two reasons, it runs fine on Visual 2012 on a server with 12 gigs and it is executing the same package on a server with 24 gigs.

Upvotes: 0

Views: 491

Answers (1)

billinkc
billinkc

Reputation: 61221

Have fewer packages run concurrently

Redesign the packages to avoid asynchronous components

The production server might have 24GB of memory available but how much is allocated to SQL Server versus reserved for the OS? SSIS is going to run in the OS memory space

If any of your packages use 32 bit drivers, your entire process is going to be restricted to ~3GB of memory.

If you've bloated your string lengths to avoid the possibility of truncation errors, that's a quick way to see dtexec consume more memory than is required.

Upvotes: 2

Related Questions