SQL Server bulk connection using SQL Authentication method

[Mahadeva]

Hi All,

I want to connect Alteryx 10.5.9 to SQL Server 2016 database to load the output data. The connection that needs to be established is "New SQL Server Bulk Connection" using SQL Authentication method.

I have successfully created ODBC connections using "SQL Server Native Client 11.0" driver in the ODBC Data Source Administrator, both “User DSN” and “System DSN” using SQL Authentication method as shown in the attachment.

However when tried running the Alteryx workflow using this connection, the output gives an error as "Login failed for user" as attached in the doc.

Request your support on this or steps on how to use SQL Server Bulk connection using SQL Authentication method.

[MattH · Accepted solution]

Hi @Mahadeva,  

Can you add your credentials again to the Alteryx Bulk Loader Window and try running the workflow again?  With certain connections like SQL Server, the password is not stored in the DSN.

[Mahadeva]

Hi Matt,

Appreciate your quick response. Its working fine now, thank you.

[jordancarson18]

This feature is not available in version 11.0, unless I am doing something incorrect I do not see it as an option within the input tool. Did alteryx remove this feature?

[MattH]

Hi @jordancarson18,

The SQL Server Bulk Loader option is only available in the Output tool and still exists through 11.5.

With the Input tool you can just use the regular Microsoft SQL Server connection in the dropdown list.  If you have any problems connecting, let us know.

[srivatsack]

---

@MattH

1. I need to use the SQL Server Bulk option to connect to our DB, so that we can upload few thousands of rows in less than 25s per iteration.

2. The catch is that I have to distribute the Alteryx workflow file and it would be very cumbersome to ask all the users to install the relevant ODBC drivers on their systems.

3. As you might have imagined, normal SQL upload is not an option because of its slowness.

Please let me know how I can connect to SQL Server Bulk without any ODBC dependency. 

Regards,

Srivatsa

[Bharatpai11]

Hi,

I was able to perform SQL server bulk load without having the need to setup an odbc connection.

To do this below are the settings I used:

1) connection string:  odbc:DRIVER={SQL Server Native Client 11.0};UID=user_name;PWD=**********;DATABASE=db_name;SERVER=server_name(hostname)

2) File format: changed it to SQL Server Bulk Loader (ssvb:)

Thats it! It worked. So this set up doesn't need you to set up an ODBC connection.

Regards,

Bharat

[mtexna]

I tried to connect Alteryx 2019 with SQL Server 2016 and in this version I can not see the "Bulk" option.