SQL database

Help Activities
, ,
1

I have a scenario and need relevant solution to it

  1. bot logs on 1 SQL server, downloads data in format of excel
  2. reads the data and executes 1 query in SQL
  3. returns the output in excel
  4. bot copies this returned output to a VLOOKUP excel to generate the order number in column H
  5. bot filters on column I and whatevr is N/A , those order number have to be copied and again step 2 has to be executed
  6. then this process shd continue maximum thrice and then send email to support team.
  7. if at first chance only there is no NA in step 5, the bot has to send successful email
    Can anyone help me?
2

You are looking for a full solution? or just any error or activity-related query

3

a crisp resolution
like what all can be done

1 Like
4
  1. use Connect to Database activity to log on SQL server
  2. Run Query you will get datatable as output and use WriteRange to write in excel
  3. use vLookup activity to perform vlookup
  4. filter activity to use filter data
  5. use any loop activity to perform it thrice and use smtp or send outlook mail message activity
1 Like
5

but how will it loop to step 2 or 3?

6

just keep those activity in loop body and max iteration to 3 or make condition which counts numbers of time loop executed

1 Like
7

Hello,

  1. Use acitivty connet to database( Configure connection and then add Dsn, uid, pwd, provider name values ) and save the output to that acitivty.

  2. Use the Run Query activity:
    Set the Existing Database Connection property to the variable created in the previous step. Type your SQL query.The result will be returned as a DataTable. Save it in a variable.

  3. Now you have the required data in the datatable. You can either apply the next steps directly on the datatable and then save it to an Excel file using Write Range Workbook activity or save the data first and then apply further steps.

  4. Use the VLOOKUP activity in UiPath

  5. Use any of these methods to filter rows:
    Use Filter Data Table activity to filter rows where column I contains “N/A”. Or jsut use a LINQ query or For Each Row activity with an If condition to find rows with “N/A”.

  6. After step 5, you have rows with NA values only, count those rows, if the count is 0 then just send a successful email. Else repeat the steps which you mentioned.

  7. If your SQL query in Step 2 requires order numbers, extract all order numbers from column H (use a delimiter like a comma or any format that suits the query).

Also as @chandreshsinh.jadeja mentioned , keep those activity in loop body and max iteration to 3.

If you want to know anything in more details please let me know.
Thank You.

8

1. Log on to SQL Server & Download Data

  • Activities:
    • Use the “Connect” activity from the Database package to establish a connection to your SQL Server.
    • Use the “Execute Query” activity to run the query and fetch the data.
    • Use the “Write Range” activity to save the query result into an Excel file.

2. Read the Data & Execute Query in SQL

  • Activities:
    • Use the “Excel Application Scope” to open the downloaded Excel file.
    • Use the “Read Range” activity to read the data into a DataTable.
    • Iterate through each row using a “For Each Row” activity:
      • Construct the SQL query dynamically based on the row values.
      • Use the “Execute Query” activity again to run the query.

3. Return the Output in Excel

  • Activities:
    • After executing the SQL query, store the result in a DataTable.
    • Use the “Write Range” activity to export the results to a new Excel file.

4. VLOOKUP to Generate Order Numbers

  • Activities:
    • Use the “Excel Application Scope” to open the VLOOKUP Excel template.
    • Use the “Write Range” activity to copy the SQL query results into the designated sheet.
    • Use a formula (VLOOKUP) in the required column (H) using the “Write Cell” activity or UiPath’s inbuilt Excel formula capabilities.

5. Filter N/A Values in Column I

  • Activities:
    • Use the “Read Range” activity to load the updated data into a DataTable.
    • Use the “Filter Data Table” activity to filter rows where column I equals "N/A".
    • For the filtered rows, get their order numbers (column H).

6. Re-Execute Step 2 for N/A Values

  • Logic:
    • If filtered rows exist:
      • Repeat Step 2 for the N/A order numbers.
    • Track the iteration count in a variable (initialize to 1, increment in each loop).

7. Limit the Process to Three Iterations

  • Activities:
    • Use a While activity to ensure the process runs for a maximum of 3 iterations.
    • If all rows are resolved (no N/A in column I):
      • Exit the loop early and proceed to send a success email.

8. Send Emails Based on the Outcome

  • Activities:
    • Use the “If” activity to check whether:
      • Success Email: If no N/A values remain after the first iteration, send a success email to the support team using the “Send SMTP Mail Message” or similar email activity.
      • Failure Email: After three iterations, if N/A values still exist, send a failure email with details of the remaining N/A order numbers.

Workflow Structure

  1. Main Process
  • Login to SQL Server and download data.
  • Read the data and execute queries.
  • Process the results in Excel.
  1. Reattempt Loop
  • Filter for N/A rows and re-execute SQL queries for them.
  • Break the loop if no N/A values remain or after 3 attempts.
  1. Email Notification
  • Check the result and send a success or failure email.

Tips for Implementation

  • Use Global Variables to track:
    • The iteration count.
    • N/A rows for reprocessing.
  • Ensure all Excel file paths and SQL queries are parameterized for flexibility.
  • Log progress using the “Log Message” activity for easier debugging.
2 Likes