Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

SQL Server Bulk Loader - how to install DSN

npsmith
8 - Asteroid

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!

 

14 REPLIES 14
CharlieS
17 - Castor
17 - Castor

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
7 - Meteor

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
8 - Asteroid

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
6 - Meteoroid

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
6 - Meteoroid

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
8 - Asteroid

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
6 - Meteoroid

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
8 - Asteroid

Hi Ravi, thanks for the response.

yes it is SQL server

 

can you provide me with more information please

 

regards

Steve

 

ravisiddharth
6 - Meteoroid

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.