Exporting large amount of data out of AS400

  • Brad Allison

    Hall of Fame

    Points: 3540

    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.

    • This topic was modified 6 months, 1 week ago by  Brad Allison.
  • Phil Parkin

    SSC Guru

    Points: 244780

    Are you including any blocking transformations in your data flow?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Brad Allison

    Hall of Fame

    Points: 3540

    The only transformation I have right now is a data conversion.  Is that considered a blocking transformation?

  • Phil Parkin

    SSC Guru

    Points: 244780

    Brad Allison wrote:

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Brad Allison

    Hall of Fame

    Points: 3540

    10,000 and 10485760 (I think default values).  Could this be the issue then?

  • Jeffrey Williams

    SSC Guru

    Points: 88696

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Brad Allison

    Hall of Fame

    Points: 3540

    I am checking on this article right now and will report back

  • KingswaySoft

    SSC Rookie

    Points: 38

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply