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?
