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!
Solved! Go to Solution.
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.
@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?
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};
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.
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.