ADO.NET source in SSIS giving error

rajanisqldev-42 206 Reputation points


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?


SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
{count} votes

Accepted answer
  1. Suba Balaji 11,186 Reputation points


    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.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful