SSIS Problem with Buffer and BLOB data type when in Migration

Introduction

In this article we are trying to discuss a about a common memory related problem at least all SSIS developer faced. This type of problem makes developer life little disturbed …just joking.

What is the Warning Message that we faced

We must remember that warning sometimes convert to Error. That’s the reason, I am writing this warning message in red color. I personally treat warning as an error as I try to remove warning from SSIS package.

[DTS.Pipline] Information: The buffer manager detected that the system was low virtual memory, but was unable to swap out any buffer. 4 buffer was considered and 4 was locked………

Danger … Danger … Danger …The SSIS package can stop within any moment of time.

Lot of the developer has the same experience when they are working with BLOB data type in SSIS package.

How we solved it

There are lot of process to solve this issue. But the thing is that which approach is best suitable for your SSIS package that you have to decide it.

Anyway, here I am providing three approach to solve it.

Approach - 1

The approach is increasing physical memory or virtual memory - can solve the problem. But it is not possible every time due to tight schedule, budget etc.

So, as a developer we have to provide solution within all this constraint and hence my next two approach is come into the picture.

Approach - 2

The BESTapproach that I recommended is load the data step by step. Means not loading the entire data at a time but go to the incremental load approach.

We must retrieve a limited amount of data at apoint of time from source not retrieve entire source data. It depends on our logic, how you retrieve data from source step by step way.

If the no of record count is limited this type of error generally not occurs. But it also depends on data type and size.

If anyone ask me for how many number of records can be retrieved at a time then my answer to him is I have no idea. Check your source table, data type …data volume, physical REM, virtual memory.

Approach - 3

Now SSIS is also provide us something to solve this problem.

SSIS provide two properties that can solve problem related to Virtual memory buffering problem.

The data flow task have this property named BufferTempStorageParh and BLOBTempStoragePath.

[Right Click the Data Flow task and choose Property… In the property window… in the MISC section you can find those two property named BufferTempStoragePath and BLOBTempStoragePath.]

By default the value of this property is BLANK.

Working with BufferTempStoragePath:

The property named BufferTempStoragePath, works when the problem of virtual memory comes.

I mean to say that in the condition of Low Virtual Memory we have to set the BufferTempStoragePath property values.

Here in the BufferTempStoragePath, we have to specify a path where the memory is dumped when low virtual memory problem arise. For Example E:\MyMemeoryDump.

Working with BLOBTempStoragePath

There is another property is equivalent to important when we are migrating data from one Server DB to another server DB with Binary Large Object (BLOB) exists in the source DB.

To working with BLOB data type data we must understand how SSIS works with it.

It just four steps activity.

Step-1: Read the BLOB contents from Source.

Step-2: Write the BLOB contents to the temporary file.

Step-3: Read the BLOB from temporary file.

Step-4: Write the BLOB contents to the destination.

So by default SSIS used C:\Windows\temp location for this activity.

Recommendation

Drive C: is active for Operating system, so it is not a good idea to put the path for property named BufferTempStorageParhandBLOBTempStoragePath in drive C:

So I recommended that don’t use the drive C and the drive that contains the Database. Use any locale good size drive for that purpose.

Hope you like it.

Posed by: MR. JOYDEEP DAS