Working from home is stressful for everyone these days, and if you are running Alteryx workflows that import large amounts of data, you might find yourself feeling extra inefficient on top of the rest! With a bogged down home internet connection, and overloaded VPN connections, that workflow that puts Alteryx data on your SQL server that only took 5-10 minutes in the office (you had to grab another cup of coffee anyways!), might be taking as long as 2 hours at home. If this sounds familiar to you, it's time for you to try out the Alteryx SQL server bulk load option in the Alteryx output tool.
There’s not a lot out there about this handy option when you try googling it, but my Continuus coworkers have some background info for us. John Heisler explains that using an Alteryx SQL bulk loader is great for when you need to load more than 500 rows, and Daemian Morscher adds that it takes advantage of the SQL batch load process, and does not create logs, a potential issue if you need to roll back the changes.
First, set up your ODBC connections on your PC to the server, with one key change. If you aren’t familiar with ODBC connections, check with your Database Administrator first! On the third page of the setup screen, change the default database to the database you need to load your data to. If you need to use this trick for multiple databases on the same server, Heisler shares that you can set up the connection as many times as you need for each database, just name them appropriately!
Once your ODBC connection is set up, open your Alteryx workflow. Drag the output data tool onto the page, and under Data Sources, choose the Bulk Option on Microsoft SQL Server.
Choose the appropriate ODBC connection, and enter any credentials that might be required.
Enter the table name when prompted, then set up the rest of the output tool how you would for a standard SQL server input.
Once you are ready, hit Run! You’ll be back to your in office (Alteryx) efficiency in no time.
If you plan on re-using this option frequently, we also suggest setting it up as a saved connection by following these instructions, so that you can become even more efficient!