Error when saving workflow with In-DB tools to Alteryx Gallery

[dallen]

When I try saving a workflow to my Gallery I receive two errors: (1) resulting from a dynamic input tool and (2) resulting from an In-DB tool. These issues do not occur when I run them on the local machine, only when I try saving it and allow Alteryx to 'validate workflow then save'.

Additional detail about these issues: 

Issue (1): This workflow downloads zip files containing CSV files from a site, extracts the CSVs from the zips, extracts the records from the CSVs, transforms the data, and ultimately pushes the data to my database. I'm using the Dynamic Input tool to handle the data extraction from the individual CSVs. This issue did not occur before I tried implementing new tools mentioned below in issue (2). The CSV template needed for the Dynamic Input tool is saved on my local machine and the flow runs without issue locally, however, when I try to save to the gallery I receive a "File Not Found" error.

Issue (2): I was previously using a normal input tool with a summarize tool to get the last RecordID from the table I'm pushing data to in order to generate the correct RecordID with each run. This configuration caused the flow to lag as it DB table grew, so I chose to implement a new solution with the In-DB tools. Initially, I had connectivity issues with the 'Connect In-DB' tool but was able to resolve those by creating a system connection (as opposed to a user connection) and selecting the correct driver. Since then, I have had no trouble running the flow locally, only when I attempt to save to the gallery and then I receive an "Error opening '[query]': No columns returned" error.

Can you all help me determine what might be causing these issues and how to resolve them?

[MarqueeCrew]

For the first issue (file not found), have you saved using:

WORKFLOW OPTIONS:

 - Manage workflow assets

  + Make sure that the dynamic input template file is checked.

I don't think that I have enough data to help you on #2.

cheers,

Mark

[dallen]

Thanks for the response, @MarqueeCrew 

I followed your instructions below and hit another roadblock. The assets manager shows that the template is missing, however, when I go to the path where the template is stored the template is there. Any ideas on how to solve this?

[MarqueeCrew]

@dallen ,

is the path set with UNC (universal naming convention) or as a mapped name (eg. z:\)?  It must be UNC.

Cheers,

mark

[dallen]

@MarqueeCrew  - The path is set as a mapped name (i.e., C:\Desktop\...). Based on the research I've done, I would need to create a shared network drive in order to use UNC as you're suggesting. Is creating a shared network drive the only way to get around this?

[MarqueeCrew]

This is an example post where I would WebEx with you. Unfortunately, I'm working from a client's office.  Theoretically, if you check the asset it should get copied to an _externals subdirectory on the server.  If this is NOT happening and the file is not found during the save to gallery, I'm surprised.  I generally have access to that shared network drive and keep the file dynamic in the event that things should change in the future.

At minimum, please try moving the template file to where you would be saving the workflow.  Then change the ADVANCED Option for workflow dependencies to RELATIVE.  Then try saving it to the server.  Desktop is really something like:  C:\users\mfrisch ..... if you are using desktop, it might be the issue.

Cheers,

Mark

[dallen]

@MarqueeCrew - Moving the file to the "Documents" folder resolved the issue. Now when I go to the asset manager, I am able to check the template to be included in the package. Thank you for that suggestion!

I'm still experiencing an issue with the In-DB tool mentioned in the original post. Now when I try saving the workflow to the gallery I only get the one No columns returned error. You mentioned that you might need extra data to try resolving this. What exactly did you have in mind?

[MarqueeCrew]

@dallen ,

Ok.  I'm 1/2 helpful today.  Got it.

What database are you accessing?  (e.g. Oracle).

Explained to me like I am not database savvy, what are you trying to get out of the query?

I'll see if I can figure something out for you.

Cheers,

Mark

[dallen]

@MarqueeCrew - I'm accessing a SQL DB, but I don't think this is a query issue. I've seen similar error messages before when the field names/schema between the database I was reading from and the database I was writing to were not the same. This error seems to be different, though.

Simply put, the In-DB version of these tools was implemented because I don't need to pull all of the data into Alteryx Designer just to the get the last RecordID. That considered, I could technically just write a query that gets the maximum integer from the RecordID column, but I'm not confident that'll solve my issue because the flow runs just fine locally.

[MarqueeCrew]

ok.  let's assume that this is a CONNECTION STRING issue.  

Can you run any query from a job saved to the server?