Getting data from SAS Dataset in MSSQL 2014 Table

[Lenvdb]

Hi all

I am trying to find the most efficient way of getting a 1.8GB SAS Dataset file into a MS SQL 2014 table.

Our scenario is as follows.

• We have a SAS Grid 9.3 on Linux Nodes.
• We have a MS SQL 2014 Database
• We only have ODBC connectivity which we use to connect to SQL Server because the SAS DataDirect Driver 7.0.1 that ships with SAS9.3 does not support MSSQL 2014. We will need to upgrade to SAS9.4 to use the DataDirect Driver.
• I discovered that I cannot use BULKLOAD option in a Proc Append because BULKLOAD is not available for Linux / Unix platforms using the ODBC MS SQL Native Driver and unixODBC Driver Manager v2.3.2.

Possible solutions:

As far as I can tell the Proc Append without the BULKLOAD=yes option is extremely inefficient. Can I use a Bulk Insert statement that may work? Has anyone tried it successfully on ODBC?

I am also trying to see if there is perhaps a libname Statement example which I can use that will use options like INSERT_SQL=YES INSERTBUFF=10000 that may speed up the process.

Is there anyone else in the whole wide world who has had success with a similar attempt?

Many thanks for your help.

Len