How to iterate through a List of SQL scripts, execute, and export data table to .txt file

Help
1

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).

image
image713×436 27.8 KB
ScriptList

2

@CScruggs

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()

3

Yes

Variables
Variables1095×143 21.7 KB

4

GetScripts = Directory.getFiles(SPath).ToArray()

is this correct or should it be ToString() ?

5

Buddy Getfiles will of type collection so dont need of that toarray

Cheers…

1 Like
6

@CScruggs

Write like this:

GetScripts = Directory.getFiles(SPath)

Output is string array and no need to write . Toarray ()

1 Like
7

Hi @CScruggs,

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

Here…

image
image821×425 10.9 KB

Cheers…!

8

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:

ErrorMsg

9

@CScruggs
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

Cheers…

10

I removed the sequence and I still get the error.

My workflow performs the following sequences:

  1. Gets active window of new editor window in Toad
  2. For each script:
    performs the following activities in Toad:
    opens folder
    gets script file
    loads script
    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.
11

Buddy can i suggest something, but i would like to know that in toad are you running any sql scripts…?

12

Yes, I am running the sql script in Toad.

13

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

Cheers…

14

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?

15

Buddy

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

Cheers…!

16

In the “Execute Query”, do I reference the script variable in the sql query, like so…?

runscripts
runscripts544×736 31.4 KB

when I run the above I get the following error msg…what does this mean.!

odbcError

17

@CScruggs

  1. 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.
  2. so use a read text activity inside the same for each loop and get the output as a string variable named out_string
  3. Pass that out_string to the execute query.

This would work buddy

Cheers.

18

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?

19

No worries buddy i can explain you, you are actually going good, its almost done. I mean it.

So, coming to the point,

  1. First keep as it is with your first activities as in this image.i.e., the private:connect and private:assign

    runscripts
    runscripts544×736 31.4 KB

  2. Inside for each loop give the same input as scripts you are right…

  3. 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

  4. 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”

  5. 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

Cheers…

20

is that working buddy…

Cheers.