We are in the final days to enter our Data and Analytics - "What's your why?" raffle with SparkED!

Reply with your "why" in text/video format to our forum post by January 28 to be entered! 

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

I also use v11 and it works well for SSVB.  We use Sql Server Management Studio (SSMS) v17 which installed Sql Server Native Driver v11 as well.

HenrietteH
Alteryx
Alteryx

@npsmith  @ravisiddharth @CharlieS 

 

I just tested with Alteryx Designer 2019.2 and the ODBC Driver 13 for SQL Server (Version 2015.130.811.168) and I was able to use the bulk loader without any problems. 

SQL Server Native Client 11 works as well. 

Are you getting an error when using the ODBC Driver 13?

Henriette Haigh
Technical Product Manager - Data Connectors
Alteryx, Inc.

npsmith
8 - Asteroid
@HenrietteH We are seeing both drivers work as well. Thanks!
clorenz
5 - Atom

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

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