Level of Difficulty: Intermediate – Senior.
This post will assist in understanding what SQL user-defined table types (UDTTs) are and how they can be used within UiPath as a part of a Data Exchange Architecture (DEA). Following explanations of UDTTs and DEA approaches, we will do a practical implementation walkthrough.
User-Defined Table Types (UDTTs)
User-defined table types are table schema definitions that may be created by users in order to store temporary data. In essence, this means that you can send a table through to a stored procedure as a parameter, the same way you would an integer or string value, without having to write the data to a table first. The benefit of this is that you don’t need to store data on a permanent medium in order to perform SQL queries on the data (like joins or upsert merges, for example).
Data Exchange Architecture (DEA)
A Data Exchange Architecture component, in this context, refers to the use of Extract Transform Load (ETL) or Extract Load Transform (ELT) in order to maintain a constant and usable state of reliable data at any point in time. This can be implemented in many different ways, depending on the existing data architecture within your environment.
One way of implementing ETL would be to create three database schemas, each responsible for its own phase of ETL:
- Landing: (Extract)
- All raw data is landed in the relevant tables within this schema. No changes are to be made to this data (possibly done through a truncate and load process)
- Staging: (Transform)
- Data from landing tables are used to populate staging tables and data is cleaned up (possibly done either using truncate and load or using upsert merge statements)
- Loading: (Load)
- Data from staging tables are transformed according to the business rules and populated into the tables within this schema (usually done using upsert merge statements)
Another common approach is to combine Landing and Staging into one schema (usually Staging). The idea behind this approach is that the Staging tables should be the intermediate storage between data sources and data targets.
Let’s work through a practical example together. We have two datasets: an Excel file (which is a few hunderd records large) containing Azure Devops project information and a SQL table (which is a few thousand records large) containing additional project information, like who the project manager is, what the project budget is, etc. For the sake of context, not all of the projects are recorded in the Azure Devops dataset as not all projects are currently active. The SQL table contains historical project data too.
We DO want to:
We would like to join the data that exists within in Excel with the data in SQL in order to enrich our dataset needed in our UiPath process.
We DO NOT want to:
We do not want to query the Excel and SQL data into UiPath datatables and do the joins in UiPath due to the way that the UiPath “Join Datatables” activity iterates through each record in each datatable to find the join. Considering the amount of data that we are working with, the process would perform poorly in terms of time. SQL is really good at processing data quickly, so it would be a good idea to parse the Excel data into SQL but we don’t want to create a table for it if we don’t need to.
We do stuff… In SQL and in UiPath.
We use UDTTs and stored procedures. We can create a table schema definition for the Excel file in SQL (ID, name, description and URL) as a type. The UDTT create statement would look something like:
CREATE TYPE [Project_UDTT] AS TABLE ( [id] [uniqueidentifier] NOT NULL, [name] [varchar](255) NOT NULL, [description] [varchar](max) NULL, [url] [varchar](max) NOT NULL ); GO
Next, we would need a stored procedure to parse the UDTT through and retrieve the joined results, which would look something like:
CREATE PROCEDURE [JoinProjectInformation_SP] ( @Projects [Project_UDTT] READONLY ) AS BEGIN SET NOCOUNT ON; SELECT p.* -- all columns in @Projects ,pi.* -- all columns in ProjectInformation FROM @Projects p JOIN ProjectInformation pi ON p.id = pi.ProjectID END; GO
Now we go do more cool stuff in UiPath.
We will need to add an Execute Query activity (Execute Non-Query would work more or less the same, if that’s the sort of thing that you’re into) to the desired sequence. If you can’t find the Execute Query activity, make sure that you have the UiPath.Database.Activities package installed.
First, we need add the stored procedure name (“JoinProjectInformation_SP”) to the SQL input argument as well as change the CommandType to StoredProcedure. Assign the output Datatable argument to something along the lines of dtJoinedProjects.
Next, we need to add the results (of Datatable type) from “Get Projects” as a paramter to the stored procedure. We do that by configuring Parameters:
Ensure that the Parameter Name is the same as it is declared in the SQL stored procedure but without the “@” prefix. Once you’re done, select OK and run the workflow.
Did this work for you? Is there something you’d like to add? Drop a comment below or reach out to me firstname.lastname@example.org.