April 9, 2020 at 1:42 pm
Good morning from my home office. Hope all is well.
I have successfully configured the ODBC connection using iSeries something or other and created the data source in SSIS and all of that is working great, including some transformations. My job seems to fail when doing the load. It is going to a SQL server instance where I duplicated this particular table. The original contains 1.6 million records. I get this error (probably familiar to some) when I run this within BIDS: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 0 buffers were considered and 0 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
The SQL server does have 32GB memory, but is a shared SQL instance. Thanks for any advice on this. Maybe there is a way to stage migration of the data in chunks? Or some configuration I missed.
April 9, 2020 at 1:54 pm
Are you including any blocking transformations in your data flow?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 9, 2020 at 2:04 pm
The only transformation I have right now is a data conversion. Is that considered a blocking transformation?
April 9, 2020 at 4:33 pm
The only transformation I have right now is a data conversion. Is that considered a blocking transformation?
No, that should not be an issue.
On your dataflow, what are the values for DefaultBufferMaxRows and DefaultBufferSize?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 9, 2020 at 4:43 pm
10,000 and 10485760 (I think default values). Could this be the issue then?
April 9, 2020 at 4:47 pm
You stated that SQL Server has 32GB of memory - did you mean the server has 32GB of memory? If so - what is the max memory assigned to SQL Server and do you have lock pages in memory configured?
For a system that is hosting both SQL Server and SSIS with a server that has 32GB of memory - I would configure SQL Server with no more than 26GB of memory...and probably less than that depending on the SSIS load.
Another item to check is the OLEDB destination. Have you configure a reasonable batch and commit size - or did you leave that as the default?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 9, 2020 at 6:36 pm
If third-party tools are an option, you can take a look at our Premium ADO.NET components in SSDT using a free developers license (as we have clients reading or loading huge amounts of data). If you still continue to face errors, you can then reach out to our Support channel. Though, if you wish to connect to an AS400 database, you might need the appropriate driver.
Thanks,
KingswaySoft
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply