nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

DCM Bulk Load for MS SQL

gjdonaldson

I am trying to migrate from Gallery connections to DCM.

I have a Gallery connection that supports bulk loading to my MS SQL DB.

The documentation says that DCM supports bulk load for MS SQL but isn't clear on how to set it up.

When I try to create a DCM Data Source for MS SQL bulk loading MS SQL Bulk Loading isn't listed as a Technology. The only choices you have for MS SQL are: MS SQL Server ODBC and MS SQL Server Quick Connect.

Can anyone help me with how you configure bulk load for MS SQL in DCM?

Thank you,

Gregory

Database Connection

Accepted answers

All comments

robwalks

@gjdonaldson If it helps, within Designer 2023.2 if I add an Input Tool and then click "Set Up a Connection" with "use DCM Connection Manager (DCM)" ticked then go to the Data sources option, I can see "Bulk" as one of the options listed against Microsoft SQL server. However, my issue is that I can't see anywhere to configure this on the gallery under DCM Data Sources (we want to set up on server and sync one-way to Designer).

chuckleswk

If you've already got it setup in your ODBC Data Source Admin on your computer you can click the cog on the right and select that ODBC connection.

robwalks

@chuckleswk I don't think thats a DCM connection, looks like standard data connection. Or if it is, then its not v2023.2 as that cog doesn't exist

chuckleswk

This is in 2022.3, I'm not sure if they removed it for 2023.

Sangini

DCM (Data Change Management) Bulk Load for MS SQL is like uploading a bunch of files to your computer all at once. Here's a simple breakdown:

Gather Your Files: Imagine you have lots of documents on your desk. In DCM, these are your data files.
Prepare a List: Make a list of all the files you want to upload. This is like creating a checklist.
Open the Database Door: DCM is like the gatekeeper to your computer (specifically MS SQL). It opens the door for your files to come in.
Hand Over the List: You give DCM your list (files to upload). It understands the list and knows where each file should go.
Fast and Efficient Upload: DCM doesn't waste time. It takes your list and swiftly puts all your files in the right place in MS SQL. It's like a super-fast secretary organizing your documents.

In technical terms, you might use a tool like SQL Server Integration Services (SSIS) for this bulk load. You set up a package in SSIS, tell it where your files are, and it efficiently loads them into your SQL Server database.

So, DCM Bulk Load for MS SQL is essentially a quick and organized way to bring in a bunch of data files into your Microsoft SQL Server.

jereed

Hi Gregory, I am trying to get my Gallery Bulk Load connection working on the Alteryx Server. It works fine for me when Ateryx Desktop on my laptop, but when i save the flow to Alteryx Server, the flow doesnt work.

To get it to work on my laptop i had to remove the tablename from the "Write to File or Database".

Running it on the server, it just runs forever right now. So im wondering if i have something configured incorrectly.

THis is using ODBC Driver 17 for SQL Server and im using version 2023.1 for Alteryx Desktop and Alteryx Server .

Thanks for any advice.

Jeff

bmalak_98

One of my Alteryx workflows uses write-back to an MS SQL Server table on my VDI (virtual machine). It currently is a table of only about 110K rows.

It works fine in Designer…quick, <20 seconds. But when loaded to Alteryx Server (AS), it would take between 30-40 minutes. (We recently switched from self-hosted Alteryx Server to AWS hosted. Our “bandwidth” was narrowed from 10GB throughput down to a 1GB pipe, significantly affecting data transmission. Thus, previously this was not a major issue in regards to data write-back, but with the restrictive throughput, we saw significant run-time increases.)

I tried a couple of things…

Data Stream In – fast in Designer, slow on AS, 30-40 minutes
Write Data In-DB– fast in Designer, slow on AS, 30-40 minutes
Output Tool (with Bulk Load) – required some tweaking to configure, see below, then runs in about 1-2 minutes

The Output Tool

the “Bulk Load” eliminates all the transaction logging and verification of the data
increase the Transaction Size to reduce the number of read-write cycles
requires that the table be named using the [database].[schema].[table] naming conventions…other wise the table was not being found

There is nothing “custom” needing to be done with the DSN ODBC connector, other than being sure to use one of the “native” ODBC drivers. You may then connect to the data source using the preferred ODBC/AKA/DCM conventions.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703