ADO.NET source in SSIS giving error

Question

Hi,

I am trying to query data from azure synapse link(serverless pool) and load into azure sql db using SSIS.

Everything is working fine on Dev. I have changed to connection to prod environment. I am getting error

I am using Data Flow Task in SSIS and ADO.NET Source as source

The error is

This table is big (800k rows)

If I change to small table(around 2000 rows), no issue.

What is the issue with the big file? How can I get rid of that error?

Regards

Answer 1

Hi,

Have you explored the below properties of data flow task? This helps to manage the memory while dealing with a huge data load.

BLOBTempStoragePath and BufferTempStoragePath

They define where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to BLOBTempStoragePath l, All other buffer data will be swapped to BufferTempStoragePath

If you have already tried that, then it's better to load data in chunks.