SQL Server Bulk Loader - how to install DSN

HI all,

I've read various posts about sql server bulk loading setups but I'm still a little confused. Hope someone can help me out.

I'm not clear on 2 things;

1) What's the difference between the connection I created in Connection Manager on my server for type = "Microsoft Sql Server" that leverage "ODBC Driver 13 for Sql Server" vs a Sql Server Bulk connection?

2) How do I set up the DSN for a Sql Server Bulk connection? I need to do this so that the user will pass the name of the DSN and their credentials in the workflow to control user authentication (ex odbc: DSN=XXXX; UID=XXXX; PWD=XXXX

Thanks in advance for the help with this. I have a slow load to a sql server that I need to improve considerably.

Thanks!

Output

Database Connection

Accepted answers

CharlieS

I've found that the SQL Server Bulk Connection is only support by a DSN that is configured with the Native Client driver (not ODBC Driver for SQL Server).

ravisiddharth

The SQL Server bulk loader is meant to improve the performance of data loads but it did not work with the default ODBC 13 Driver. So I downloaded the ODBC 11 driver from this link https://www.microsoft.com/en-us/download/details.aspx?id=36434

and then selected the "SQL Server Bulk Loader (ssvb) option for the "File Format" field in the output tool. That did the trick for me.

All comments

CharlieS

I've found that the SQL Server Bulk Connection is only support by a DSN that is configured with the Native Client driver (not ODBC Driver for SQL Server).

akshatoz

Hello,

PFB the steps:

1. Determine if your machine is 32bit or 64bit

2. For 32-bit machine

got to the following location -C:\Windows\System32\odbcad32.exe
Then Create a User DSN. You need to plug in the user name, password, connection string etc of the database here and test out the connection.
Name this connection as "AAA" and save it.
In the alteryx environment , if you configure an ODBC connection you will be able to see "AAA"

3. For a 64 bit machine

got to the following location -C:\Windows\SysWOW64\odbcad32.exe
Repeat steps mentioned in Step 2 above.

4. Alternatively you can ask your Windows admin to configure a User DSN/System DSN for you to use in Alteryx.

npsmith

So now I see.....thanks for pointing that out. I was thinking there was some special driver needed that I didn't have. Once i switched to the native driver for the setup, it worked perfectly- took a 14 min process down to 14 seconds....

Thanks!

clorenz

So happy I saw this post! To help others in their search and to remove ambiguity, these are the connection strings I used to connect to an Azure SQL Data Warehouse via a System DSN connection string.

Note: replace everything after each '=' including the curly brackets

Read/Write via ODBC:

odbc:DSN={Replace with DSN name};Uid={Replace with Username};Pwd={Replace with Password};

Write via Bulk Loader:

ssvb:DSN={Replace with DSN name};Uid={Replace with Username};Pwd={Replace with Password};

salbol1

I am running an older 2020.3 version, and have noticed that when writing with 'Data Stream In' via ODBC that header fields need to be quoted out in order to avoid an error of "Incorrect syntax near 'varchar/char/whatever data type'..." showing up regardless if 'Quoted' or 'None' being setup in the tool.

Utilizing ssvb in the initial setup (we are utilizing Integrated Windows Authentication), only the connection type and created BULK_PKD I setup in User in the ODBC Admin interface are created for the connection string.

setup ssvb.PNG

When going onto the 'Write' tab, I just indicated 'Same as Read Driver' as indicated below, and upped the transaction size.

Using "Quoted" or "None" here did not impact being able to create, drop, append, etc.

ssvb write.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703