I have a folder of .sql scripts I want to load into Toad, execute the script, and then export the data table to a .txt file. My sequence of activities to load, execute, and export the data table are working as expected for the first script in the folder, but I can’t get the “For Each” to iterate through the other scripts in the folder and perform the same set of activities (load, execute, & export data).
You are reading all SQL files from a folder and storing in ‘GetScripts’ variable right. I hope this variable is of type string array.
Then pass this string array variable into ForEach loop instead of Enumerable.Range(1,9).Toarray()
GetScripts = Directory.getFiles(SPath).ToArray()
is this correct or should it be ToString() ?
Buddy Getfiles will of type collection so dont need of that toarray
Write like this:
GetScripts = Directory.getFiles(SPath)
Output is string array and no need to write . Toarray ()
Welcome to UiPATH Comunity…!
After passing the input to for each loop with variable GetScripts like
GetScripts = Directory.getFiles(SPath)
, kindly dont forget to change the typeargument type as string as you are getting as string
Thank you all for your help.
I can see the first script load, run, and export, but than nothing, so I am not sure it is iterating through the other scripts as I do not see the action being performed on screen. But, shortly after the first script finishes I receive the following message:
Buddy its not like it didnt get iterated rather it got stuck with a on element appear activity that you have added next to your first scrip load, run and export process…as it took more than 30 seconds to wait for element
Buddy may i know where and why do you use this on element appear, are you using to wait untill an page needs to be loaded or is there any specific reason…Kindly let us know buddy
This could be solved easily,you are almost done
I removed the sequence and I still get the error.
My workflow performs the following sequences:
- Gets active window of new editor window in Toad
- For each script:
performs the following activities in Toad:
gets script file
runs script (F9) hotkey
invokes get data workflow
Clears All (f7) - for new editor for next script, but the other scripts never load…I get the same error message.
Buddy can i suggest something, but i would like to know that in toad are you running any sql scripts…?
Yes, I am running the sql script in Toad.
Aahah…then fine buddy…you dont need to go for toad and type hotkeys rathe we have a activity called Execute query activity where you can pass any. sql scripts as string type and get the output table from db as datatable…use the same iteration you have now like inside for each loop remove all the activities and place this single execute query activity and pass the for each loop value as input to this which has the scripts to be run…
You can get this activity by downloading the package system.database.activities…
This will work for sure buddy…lets sort this out
Okay, I will try this again. The first time I tried the database connect activity I could never get the connection string correct to the database. Also, how can I secure my login credentials? Is there an Encrypt feature in UiPath?
Try to use the connection wizard, there select odbc for yours buddy and pass the dsn value from your system
Either you can get from orchestrator as asset and insert into the field type secure text or
you can get from your windows credential as well
In the “Execute Query”, do I reference the script variable in the sql query, like so…?
when I run the above I get the following error msg…what does this mean.!
- Buddy the script variable that you have is a path which cannot be passed as a value to the field that takes scripts in string format.
- so use a read text activity inside the same for each loop and get the output as a string variable named out_string
- Pass that out_string to the execute query.
This would work buddy
Thank you for your help. I am now confused. So, can I reference the path with scripts variable, and use “*.sql”) to read each script file?
No worries buddy i can explain you, you are actually going good, its almost done. I mean it.
So, coming to the point,
First keep as it is with your first activities as in this image.i.e., the private:connect and private:assign
Inside for each loop give the same input as scripts you are right…
Then inside the loop, first use a read text activity with input as scripts (path) and get the output variable a string datatype named out_text
Now use the same private: EXECUTE QUERY activity with same connection strings but with input as out_text (the text read from the path of the file which has the query)
Not as "Scripts"
Now if you want you can get the table from this execute query activity in a datatable type to a variable final_dt
Thats all buddy you are done
is that working buddy…