Creating Table in MS SQL Server is slow using ODB connection

[kirk_kenery]

I am developing a tool to upload text files from a folder to MS SQL Server. It needs to be dynamic so that the user can pick the SQL Server and Database name, so I chose to use an output tool with an ODB connection. Unfortunately it takes about 4 minutes for about 40,000 records with only 2 columns. Here is my connection string:

odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YeeOldDatabase;Data Source=YeeOldSqlServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=FizzleWizzle;Use Encryption for Data=False;Tag with column collation when possible=False|||tblTemp

I tried different transaction sizes from 1 to a 20,000 and I don't see a difference in runtime.

I tried switching to ODBC and it takes 15 seconds but the user needs to create a DSN on their machine manually. I need a quick way for the user to pick the sql server name and database. Any ideas?

[kirk_kenery · Accepted solution]

Resolved: I changed my connection string to this and its much faster, and I'm still able to change the database and server name within a macro.

odbc:DRIVER={ODBC Driver 17 for SQL Server};DATABASE=YeeOldDatabase;SERVER=YeeOldSqlServer;Trusted_Connection=yes