Populating SQL Server Table from Excel Spreadsheet

[Karl-D]

I'm new to Alteryx Designer, using version 2020.2.3.27789.

I inherited a .yxmd file with a simple flow that simply takes a single sheet from an Excel workbook and loads it into a SQL Server table.

When I run the workflow, it works as expected.

When one of my associates (whom I believe has same SQL Server Permissions as me) attempts to run it, it reports success, but is not really updating the SQL Server table.

It's difficult to troubleshoot here, because no errors are reported when my associate runs it.

Results - Workflow - Messages states:

0 Errors | 0 Conv Errors | 0 Warnings

Designer x64 - Started running

Output Data (31) - ODBC Driver version: 03.52

Input Data (3) - 5757 records were read from ...

Output Data (31) - 5757 records were written to 32bit:odbc:DSN=…

Designer x64 - Finished running … 2.2 seconds.

But when we go to the database, the table does not appear to have been truncated, or repopulated.

When I run the same workflow, it works perfectly.

What additional steps can I perform to help troubleshoot this?

[echuong1]

Is your associate able to access the DB from Alteryx in general? I suggest creating a simple input that reads in a table from the DB to test.

Also, for you input, are you using an Alias or the actual connection string there?

[Karl-D]

Figured it out. 

• First, we tried a new workflow, and purposely tried writing to a database where he did not have permission to do so.  As expected, this failed, and alteryx gave an appropriate error message.
• Then we tried writing to the production datasource.  This got us back to alteryx reporting success, but no data written to the expected table.
• I switched from the ODBC driver we were using to the Native SQL and that worked for him.  But I was still curious as to why the ODBC driver worked for me but not him.
• I later came to realize that his SQL Server ODBC connection had the default database on the server sent to a different database.  So alteryx was writing, just not to the database we thought it was. 

Since I know very little about alteryx, I just assumed we were doing something wrong there, but it was actually just a simple ODBC misconfiguration.

That being said, there's a lot to keep track of when using ODBC connections and shared .yxmd files.

We'll need to ensure that our ODBC connections have the same name and configuration AND ensure that the data sources in our workflows have the same name and configuration for things to work right when using ODBC.

[echuong1 · Accepted solution]

That can be tricky!

You should see something along the lines of "alias translated to" in your results window. This will give you the full connection path in place of the alias if you want to double check to make sure the correct DB is used.

You can also use the full connection string instead of the alias in the input tool to avoid this issue you had.