I'm having this problem with Dynamic Input for Sql Server. I want to dynamically input a data source and a Select statement but when I run my workflow this error appears:
My input Data Source Template is:
odbc:DSN=Server1;UID=99999;PWD=__EncPwd1__
And I also input a table to select for test:
Idk what I'm doing wrong, is there a away to solve this issue?
Solved! Go to Solution.
Hi @amanda04
I'm not so sure if you can pass a connection string to Dynamic Input with user and password in it, since the password is encrypted.
https://help.alteryx.com/11.7/index.htm#PasswordEncryption.htm?Highlight=encrypt
Do you really need to change the connection to the database?
Cheers,
I'm using the password and the user name as a parameter and replace them on my data source template. The problem is the select statement I guess.
Yes, I can see that, that's not the point. I'm saying this might be the cause of the error.
You're changing full path of the connection string, including username and password through a Formula Tool. In the connection string, username and password are encrypted. I'm not so sure if this is the best way to do this.
Have you tried to use a regular Input Tool, with the parameter you're passing in the Formula Tool as a reference and do a simple select statement over there?
Cheers,
I don't know how can I use then simple Input data with variables because I will need to configure a connection to input. Can you give me example to do that?
Tks a lot for the help!
That's just to test your select statement and connection you're trying to get through the Dynamic Input Tool.
What I'm saying is: first, test in a regular Input Tool using your parameter as a reference and do a select statement there.
If it works fine, then it probably doesn't have to do with the statement itself, and it could be something to do with how you're using the Dynamic Input tool.
I haven't had this scenario before and I'm curious to see the output. @CharlieS or @danilang , did you guys do something similar in the past?
Cheers,
Undertood, I will try!
If you just want to change the table/query and keep the same connection, the Dynamic Input will take care of that. You'll need to change the configuration to "Modify SQL Query" to make those changes.
In this case, your ask was to modify the connection settings too. Since that isn't a part that the Dynamic Input is ready to modify, you'll have to do it yourself by editing the XML (which mean macro time). I'll see if I can put something together as an example here shortly.
That second part I'm curious about. I had an idea that Dynamic Input tool doesn't fit in this scenario of changing connection credentials.
Cheers,
The "Modify SQL Query" options of the Dynamic Input tool, the "replace string" is only applicable to the query context, not the connection. The connection can be modified, but only via XML, so I typically build a quick batch to do that.
Check out the attached workflow that shows how to use a Dynamic Input to modify the table/query, and also a batch macro to dynamically modify the table/query and the connection parameters via XML.