Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Use single connection to insert into multiple tables

felipeecst
7 - Meteor

Hi everyone,

 

I'm new to Alteryx and I'm trying to build a simple importation workflow that will read data from several distinct worksheets and save each of them into a different table in the same database. 

 

What I am doing now is to set an Input Data Tool (to read the worksheet) and an Output Data Tool (to insert data into table) for each worksheet and configuring a new database connection for each one (the database is the same, but the tables are different). The problem with this approach is that if I need to change the database connection string, I would need to change every single Output Data tool. So, I wonder if there is a better approach for doing that.

 

Can anyone help me doing that?

 

Thanks in advance!

11 REPLIES 11
michael_treadwell
ACE Emeritus
ACE Emeritus

In order for the solution below to work, the data in each worksheet is going to need to be of a similar structure. Same column names, types, etc. If that is not the case, I believe this is still possible but it would require quite a bit more development and testing.

 

First, you would need to use the dynamic input tool to read all of the input worksheets.

 

Then, you could set up an input tool inside of a batch macro. Use interface tools to configure the connction string and have the control parameter be the tables to which you need to write.

bsharbo
11 - Bolide

Can I assume that the reason you need to change the database connection string is that you are first writing to at "DEV" or "UAT" database, and then you want to change the code so that it Points to a "production" database?

 

If so I have run into your exact question before, and I solved it using the following methodology :-)

 

#1) Put your input and output connections inside a  batch macro.

#2) add one control Parameter to the batch macro. This will be your input to the macro of "database Connection".

#3) Connect an update (action tool) to each of your output tools pointing to the same control Parameter.

#4) Dynamically change the database connection string by replacing the portion that has the Server + database hard-coded in it with whatever you pass into the batch Macro through the action tool.

#5) Make a secondary workflow that calls this batch macro, and passes in the server name + database connection to your control paramater.

 

 

By doing this, you could have any number of outputs to the database and the control paramater would then update each one for you, so you would only have to update the control paramater in one location.

 

 

If you are doing this as an analytic application there are other ways to solve this problem without using a batch macro (you can simply use a text Box tool for example).  

 

If you need me to post some example code let me know and I can do!

 

 

 

 

KaneG
Alteryx Alumni (Retired)

It looks like @bsharbo & @michael_treadwell have given you ideas to help you output the data in this fashion. I would like to mention though that you might want to check out setting up an Alias for the connection. You can do this in "Options > Advanced Options > Alias Manager > Standard Connections". That way, if you change the connection string, you only need to change it there.

 

And then that alias can be referenced in the Input/Output Data tools as such (Sorry about the blotchy boxes for redacted text):

 

Image 001 - 20160519 - 170547.png

 

You can also set up a similar process using a workflow constant. Thats what I use for share drives.

 

Kane

felipeecst
7 - Meteor

Thanks everyone for the answers!

 

The alias option seems to be exactly what I need! But I'm having some problems trying to use it. When I copy the exact same string that works on the Output Data tool to the alias, I'm getting a login error.

 

Here's the connection string I'm using:

odb:Provider=SQLOLEDB.1;Password=__EncPwd1__;Persist Security Info=True;User ID=user;Initial Catalog=XX;Data Source=XXXX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXX;Use Encryption for Data=False;Tag with column collation when possible=False

 

And on the Output Data tool I'm using: aka:AliasName|TableName. Alteryx is translating it correctly, but I can't stop receiving the login error! I suspect that is something related to the 'Allow saving password' option (even when I don't use Alias, I need to keep this option checked for the login to work)...

 

Can anyone help me figure out what could be wrong?

 

Thanks in advance.

KaneG
Alteryx Alumni (Retired)

Hi,

 

Becaue you have copied the string exactly, you have copied the password as '__EncPwd1__'. In the Alias manager, with you connection string highlighted, click 'Password' and enter the credentials there. That should then save the actual password. (Note, the connection sting will still say '__EncPwd1__', but underneath it will be the actual password). 

 

By design of course, because there is no point in encrypting a password if someone can just copy it and use it.

 

Kane

felipeecst
7 - Meteor

Thanks Kane,

 

One more thing. Very frequently I receive the message: Unable to translate aka:DbConn (which is the name of my alias) when I'm trying to save my workflow. I don't know what causes this, but eventually it stops and the alias works. Do you have any clue?

KaneG
Alteryx Alumni (Retired)

Sorry about the delay in replying,

 

It could be many things, but most likely is a connection issue whereby the connection is dropping out.

 

Kane

OceanBass
5 - Atom

Hi bsharbo,

 

 

 

bsharbo
11 - Bolide

Hello @OceanBass.   I could provide an example of my earlier solution, however as I have learned more about the AlterYX tool, I actually do not like that solution anymore Smiley Very Happy

 

 

Instead i would highly recommend using the alias option  from the Alias Manager.  This allows for you to set a connection string in ONE place and have it filter to as many workflows as you want :-) You simply go to Options > Advanced Options > Alias Managers > Standard Connections.

 

Then you add a new alias and you put your connection string here.

 

A MASSIVE benefit of this is it allows you to point to UAT / PROD systems when exporting your workflows between your Local machine and your server (gallery).

 

For Example: Say you wanted to point to a DEV database when you are developing your code on your local machine, you would set up a connection with an alias of "Widget Project".   In this alias you would point to your DEV database.

 

Then on your production server you would set up the same Alias namd "Widget Project" but you would point to the production server.  Now when you release your app from your local machine to your production system, you have efficitively migrated your code to point to the correct server without you having to touch anything!  

 

 

I would not personally do my original suggestion at this point, as this alias system perfectly does the same thing only much easier :-)

 

If you need to see an example of simply using one input to pass into mulitiple workflow tools (say you are reading files from different network folders and you want to update the root location of the folders) then I can post that example, as it is basically the same idea!  let me know if that is still helpful for you.

Labels