[RPA] Using SQL User-Defined Table Types in UiPath as a Component of a Data Exchange Architecture

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:

  1. 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)
  2. 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)
  3. 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.

Use-Case

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.

So we…

We do stuff… In SQL and in UiPath.

In SQL

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.

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.

User-Defined Table Types (UDTTs) in UiPath

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 jacqui.jm77@gmail.com.

Published by Jacqui Muller

I am an automation architect by current profession who enjoys dabbling in software development, RPA, IOT, advanced analytics, data engineering and business intelligence. I am aspiring to complete a Master's degree in Computer Science within the next two years. My competencies include a high level of computer literacy as well as programming in various languages. I am passionate about my field of study and occupation as I believe it has the ability and potential to impact lives - both drastically and positively. I come packaged with an ambition to succeed and make the world a better place.

8 thoughts on “[RPA] Using SQL User-Defined Table Types in UiPath as a Component of a Data Exchange Architecture

  1. Hi,
    I followed your process but i am getting this error in UIpath “Execute Non Query: The type of column ‘Material’ is not supported. The type is ‘Object’. Material is the first column of my User defined table.

    Like

  2. CREATE TYPE [dbo].[MRP_View_Dumps_Extract_4_Type] AS TABLE(
    Material nvarchar(255),
    Material_Description nvarchar(255),
    Language nvarchar(255),
    Plnt nvarchar(255),
    MTyp nvarchar(255),
    Plant_Status nvarchar(255),
    P nvarchar(255),
    Ty nvarchar(255),
    MRPCn nvarchar(255),
    Maint_status nvarchar(255),
    LS nvarchar(255),
    Reorder_point float,
    Min_lot_size float,
    Max_lot_size float,
    Max_level float,
    Fix_lot_size float,
    Rounding_val float,
    Safety_stock float,
    Prof nvarchar(255),
    SafetyTime nvarchar(255),
    SafetyTime1 nvarchar(255),
    ProcType nvarchar(255),
    SPT nvarchar(255),
    Planning_time_fence nvarchar(255),
    PDT float,
    GRT float,
    Cal nvarchar(255),
    Q nvarchar(255),
    B nvarchar(255),
    Bulk_ nvarchar(255),
    CoPr nvarchar(255),
    SMKey nvarchar(255),
    ESLoc nvarchar(255),
    Bch nvarchar(255),
    SLoc nvarchar(255),
    Av nvarchar(255),
    SM nvarchar(255),
    V nvarchar(255),
    Per_ind nvarchar(255),
    SLife float,
    RSL float,
    Ctrl_key nvarchar(255),
    BUn nvarchar(255),
    UoI nvarchar(255),
    PGr nvarchar(255),
    Srce_list nvarchar(255),
    Aut nvarchar(255),
    D nvarchar(255),
    Ef_o_date datetime,
    Followup_material nvarchar(255),
    B1 nvarchar(255),
    G nvarchar(255),
    Mx nvarchar(255),
    TT float,
    Created_on datetime,
    Created_by nvarchar(255),
    R_profile nvarchar(255),
    MRP_area_exists nvarchar(255),
    MRP nvarchar(255),
    Min_SafetyStock float,
    Min_SafetyStock1 nvarchar(255),
    PI nvarchar(255),
    OTol float,
    UTol float,
    Unl nvarchar(255),
    Consumption_mode nvarchar(255),
    Bwd_cons_per nvarchar(255),
    Fwd_cons_period nvarchar(255),
    XPlant_Stat nvarchar(255),
    SG nvarchar(255),
    Market nvarchar(255),
    SAP_Box nvarchar(255)
    )
    GO

    This is my UDTT.

    Below is my stored procedure.

    Create PROCEDURE [dbo].[Insert_INTO_MRP_View_Dumps_Extract_4]
    @tblCustomers MRP_View_Dumps_Extract_4_Type READONLY
    AS
    BEGIN
    SET NOCOUNT ON;

    INSERT INTO MRP_View_Dumps_Extract_4_bkp(Material,Material_Description,Language,Plnt,MTyp,Plant_Status,P,Ty,MRPCn,Maint_status,LS,Reorder_point,Min_lot_size,Max_lot_size,Max_level,Fix_lot_size,
    Rounding_val,Safety_stock,Prof,SafetyTime,SafetyTime1,ProcType,SPT,Planning_time_fence,PDT,GRT,Cal,Q,B,Bulk_,CoPr,SMKey,ESLoc,Bch,SLoc,Av,SM,V,Per_ind,SLife,RSL,Ctrl_key,BUn,UoI,
    PGr,Srce_list,Aut,D,Ef_o_date,Followup_material,B1,G,Mx,TT,Created_on,Created_by,R_profile,MRP_area_exists,MRP,Min_SafetyStock,Min_SafetyStock1,PI,OTol,UTol,Unl,Consumption_mode,
    Bwd_cons_per,Fwd_cons_period,XPlant_Stat,SG,Market,SAP_Box)
    SELECT Material,Material_Description,Language,Plnt,MTyp,Plant_Status,P,Ty,MRPCn,Maint_status,LS,Reorder_point,Min_lot_size,Max_lot_size,Max_level,Fix_lot_size,
    Rounding_val,Safety_stock,Prof,SafetyTime,SafetyTime1,ProcType,SPT,Planning_time_fence,PDT,GRT,Cal,Q,B,Bulk_,CoPr,SMKey,ESLoc,Bch,SLoc,Av,SM,V,Per_ind,SLife,RSL,Ctrl_key,BUn,UoI,
    PGr,Srce_list,Aut,D,Ef_o_date,Followup_material,B1,G,Mx,TT,Created_on,Created_by,R_profile,MRP_area_exists,MRP,Min_SafetyStock,Min_SafetyStock1,PI,OTol,UTol,Unl,Consumption_mode,
    Bwd_cons_per,Fwd_cons_period,XPlant_Stat,SG,Market,SAP_Box FROM @tblCustomers
    END

    Like

      1. I am reading data from an excel sheet (this excel sheet contains the Material and other columns) via Read range activity and its output Datatable I am passing it to Non execute query activity. So I cannot change the type of Material to string. I tried changing the data type of Material column in my excel sheet to “Text” but even after that i get the same error.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: