SQL Server Bulk Loader - how to install DSN

[npsmith]

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!

[CharlieS · Accepted solution]

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!

[ravisiddharth]

Hi,

I'm stuck with a terribly slow load to a sql server table too. Can you explain the steps to switch to the native driver please?

Thanks,
Ravi

[ravisiddharth]

Ok I had to install ODBC 11 driver for SQL server for the bulk loader to work. 13 which was the default did not work with the bulk loader.

Best,

Ravi

[SteveKnapper]

Hello all, I am having the same situation in that reads and transformation is relatively quickly running my workflow in designer and watching, and when it gets to the SQL write its painfully slow.

The question I have here is, I have designer locally and a gallery on a server which I am publishing to.

Do I setup the ODBC connection on my server and then connect to it via an in-database connection (which I have never used)

any suggestions would be much appreciated.

steve

[ravisiddharth]

Hi Steve,

Is the server that you are writing to a SQL server? did you try downloading the ODBC 11 driver and selecting the bulk loader option?

Regards,

Ravi

[SteveKnapper]

Hi Ravi, thanks for the response.

yes it is SQL server

can you provide me with more information please

regards

Steve

[ravisiddharth · Accepted solution]

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.