How to fix 'Could not Connect (sequence)' using Node mssql with many requests

Question

I have created a data fetching Node.js application that goes and grabs data from our vendor information servers (different types of data), and ultimately ends up as an array of objects. This app will retrieve data on a schedule and save it to a SQL Database.

On the SQL side I have sprocs that will accept a unique table name and row data, will dynamically create the table and columns if they do not exist (one sproc call), and then will run multiple requests for every object in the array to add each row of data.

My issue is I am getting a "Failed to connect to (server):1433 - Could not connect (sequence)" error when running many (over 10,000) requests at once.

I am using Bluebird.map with concurrency set at 150 to try and limit the number of open requests, but this is not solving my issue. I'm ok with it taking time, but I would like to run groups of requests if possible to maximize efficiency.

I did use Bluebird.each, and this seemed to help the issue, but slowed down the process by almost tenfold.

mssql config

const x = require( 'dotenv' ).config( { path : '../.env' } );

const dwObj = {
    domain         : process.env.DATABASE_DOMAIN,
    user           : process.env.DATABASE_USER,
    password       : process.env.DATABASE_PASSWORD,
    server         : process.env.DATABASE_SERVER,
    database       : process.env.DATABASE_NAME_DW,
    stream         : true,
    requestTimeout : 60000,
    options        : {
        encrypt : false, // Use this if you're on Microsoft Azure
    },
    pool : {
        max : 60000
    }
};

module.exports = dwObj;

executeSproc function

const mssql = require( 'mssql' );

const dwConfig = require( '../config/datawarehouse.mssql' );
const indicatorConfig = require( '../config/indicator.mssql' );

/**
 * executeSproc
 *
 * Executes a sproc . Based on a pre-defined query,
 * known as a "stored procedure", build a request with a
 * set of known parameters to execute on one of the 
 * DBs. An engineer may find out what parameters belong to
 * what procedure, but this is not presently documented.
 * When you ask for a procedure to be created, the
 * one who creates the procedure will also outline the
 * requirements for that sproc.
 *
 * ! TODO : refactor for error handling.
 * ! TODO : optimize request pooling.
 */

module.exports = function executeSproc( sprocInfo, onRow ) {

    return new Promise( async ( resolve, reject ) => {
        const { database, parameters, procedure } = sprocInfo;
        const results = [];
        let config;

        /**
         * Intelligently pick which config to use based on the
         * provided `sprocInfo`. This allows each individual
         * controller to manage its own state and we don't have
         * to worry about threads overriding env variables
         * mid-flight.
         */
        if ( database === 'indicator' ) {
            config = indicatorConfig;
        } else if ( database === 'datawarehouse' ) {
            config = dwConfig;
        }

        const pool = await new mssql.ConnectionPool( config ).connect();
        const request = await pool.request();
        request.stream = true;

        request.on( 'row', ( row ) => {
            let newRow = row;

            if ( onRow ) {
                newRow = onRow( row );
            }

            results.push( newRow );
        } );

        request.on( 'error', ( error ) => {
            console.error( error );
            console.log( sprocInfo );
            reject( error );
        } );

        request.on( 'done', () => {
            pool.close();
            return resolve( results );
        } );

        parameters.forEach( ( { name, value } ) =>
            request.input( name, mssql.NVarChar, value )
        );

        request.execute( procedure );

    } );

};

Looping function over data

const executeSproc = require( '../helpers/executeSproc' );
const prepareTableSave = require( '../helpers/prepareTableSave' );
const insertTableColumns = require( '../helpers/insertTableColumns' );
const bluebird = require( 'bluebird' );

const cleanseAndStringifyForSQLJSON = ( rowObj ) => {

    const dataObj = Object.keys( rowObj ).reduce( ( acc, cur ) => {

        const propData = rowObj[cur];
        acc[ cur ] = propData.replace( /'+/g, '' );
        return acc;
    }, {} ) || {};

    return JSON.stringify( dataObj );

};

module.exports = async ( name, data, incremental ) => {

    if ( data && data[0] ) {
        await insertTableColumns( name, data[0] );

        if ( !incremental ) {
            await prepareTableSave( name );
        }

        const sprocInfos = data.map( ( dataRow ) => {

            return {
                database   : 'datawarehouse',
                procedure  : 'DataFetch.dbo.sp0001saveRowData',
                parameters : [
                    { name : 'dataExtractName', value : name },
                    { name : 'dataRow',         value : cleanseAndStringifyForSQLJSON( dataRow ) }
                ],
            };

        } );

        // return columns;
        // Loop through sprocs and execute them
        return await bluebird.map( sprocInfos, sprocInfo => executeSproc( sprocInfo ), { concurrency : 150 } );

        // return sprocInfos[0];
    }

};