Bulk Loading with Microsoft SQL Server

Overview of Bulk Loading Setup

Bulk loading is supported for SAS/ACCESS Interface to Microsoft SQL Server. Bulk loading enables you to insert a large number of rows into a Microsoft SQL Server table. The setup steps and options that you use depend on your data source.

Bulk Loading to Most Microsoft SQL Server Databases

You can enable bulk loading to most Microsoft SQL Server databases by specifying options in the odbc.ini file (for UNIX platforms) or by using the ODBC Administrator (for Windows platforms). For more information, see the ODBC driver information about bulk loading available in your Progress DataDirect documentation.

To bulk load data to most Microsoft SQL Server databases, use the INSERTBUFF= LIBNAME option or INSERTBUFF= data set option to configure bulk loading with Microsoft SQL Server.

Bulk Loading to Azure Synapse Analytics

When you bulk load data into Azure Synapse Analytics (SQL DW), SAS/ACCESS first loads the data files into an Azure Data Lake Storage (ADLS) Gen2 location. SAS/ACCESS then moves the files from the external storage account into Azure Synapse Analytics using the COPY INTO command.

To bulk load data into Azure Synapse Analytics, use the AZUREAUTHCACHELOC= and AZURETENANTID= system options to control access to Azure. For details, see Authentication for Bulk Loading to Azure.

When you are bulk loading data into Azure Synapse Analytics, these options are available:

• BL_ACCOUNTNAME= LIBNAME option and data set option
• BL_APPLICATIONID= LIBNAME option and data set option
• BL_COMPRESS= LIBNAME option and data set option
• BL_DEFAULT_DIR= LIBNAME option and data set option
• BL_DELETE_DATAFILE= LIBNAME option and data set option
• BL_DELIMITER= LIBNAME option and data set option
• BL_DNSSUFFIX= LIBNAME option and data set option
• BL_FILESYSTEM= LIBNAME option and data set option
• BL_FOLDER= LIBNAME option and data set option
• BL_IDENTITY= LIBNAME option and data set option
• BL_LOG= LIBNAME option and data set option
• BL_MAXERRORS= LIBNAME option and data set option
• BL_NUM_DATAFILES= data set option
• BL_OPTIONS= LIBNAME option and data set option
• BL_SECRET= LIBNAME option and data set option
• BL_TIMEOUT= LIBNAME option and data set option
• BL_USE_ESCAPE= LIBNAME option and data set option
• BL_USE_LOG= LIBNAME option and data set option
• BULKLOAD= LIBNAME option and data set option

Authentication for Bulk Loading to Azure

For bulk loading, additional authentication is required due to the process that is described in Bulk Loading to Azure Synapse Analytics.

Use device-code authentication to access Azure. The user ID that is used to authenticate must be assigned Contributor and Storage Blob Data Contributor roles in the ADLS account configuration.

• Use the following system options:

  AZURETENANTID= is required.

  AZUREAUTHCACHELOC= is optional.

• The following options are all required, either as LIBNAME or data set options:

  BL_ACCOUNTNAME=

  BL_APPLICATIONID=

  BL_FILESYSTEM=

  BL_IDENTITY=

  BL_SECRET=

• On your first attempt to access data by using device-code authentication, an error message is printed in the SAS log. You must authenticate with Microsoft. That process is explained in the documentation for the required AZURETENANTID= system option.