Export Data Via Deploying an SSIS Package

Overview

In this topic you can learn how to export data that retains the Insights data model structure by deploying a SQL Server Integration Services (SSIS) package. Unless scheduled to run more frequently, this is a one-time export to flat files that can be used as data sources for reporting tools. There will be a file generated for each month per table (jobs, queues, etc).

Note:

Custom variables from robot logs and queues are not yet supported.

Prerequisites

Integration Services (SSIS) must be installed on your SQL Server (check Install Integration Services (SSIS)).

Aggregated Metrics

The following metrics are aggregated and cannot be exported at runtime, but can be computed from other data in the export. Please use the query/aggregate function in your destination tool to surface these metrics as they are found in Insights.

Metric	Query/aggregate function	Description
Processes Ran	sum ( job id)	The number of processes that have been executed.
Successful Jobs	sum ( job_state = “successful“)	The number of jobs that have been successfully executed.
Faulted Jobs	sum ( job_state = “faulted“)	Processes executed with faulted jobs.
Success Rate	1.0 * ${sum (job_state = "successful")} / NULLIF($sum (job id),0)	The percentage of successfully executed jobs.
Faulted Rate	1.0 * ${sum (job_state = "faulted")} / NULLIF($sum (job id),0)	The percentage of faulted jobs compared to all jobs.
Total Run Time in Seconds	sum (RuntimeInSeconds)	The total processing time of all jobs in seconds.
Total Suspended Time in Seconds	sum (SuspendedTimeInSeconds)	The time of jobs, in seconds, spent in a suspended state (check Job States). You can use this measure to calculate the total time spent on a particular process by subtracting the time spent in a suspended state, in long-running workflows.
Time in Pending in Seconds	sum (PendingTimeinSeconds)	The amount of time all jobs spent in the Pending and Resumed states, meaning how long it took from the moment the job was queued until it ran on the robot (check Job States).
Queue Item Count	sum (queue id)	The number of queue items processed on the tenant.
Queue Count	sum (distinct (queue id))	The number of queues with data processed on the tenant.

Deploy SSIS Package to SQL Server

Deploy the SSIS package to SQL to export Insights data.

The SSIS package UiPathInsightsDataExport.zip is bundled with the Insights release files (check Insights installation). It contains 3 files: a manifest file, and 2 XML files.

1. Create the manifest file that comes with the release packages (check Insights installation).

2. Select the manifest file to start the installation wizard.

3. Choose SQL Server deployment. Alternatively, you can choose the File System deployment option if that is preferable.

   

4. Specify the target SQL Server (e.g., (local)). To deploy it to remote machines, select Use SQL Server Authentication.

   

5. Select the location where you want to deploy the SSIS package. You may want to create a new folder for this purpose. Please create the folder prior to this step and then select it as the destination.

6. In the Configure Packages step, fill in the connection string for the source database and the root destination path where you'd like to export the data.

   

Execute SSIS Package to Export Insights Data

To export Insights data you need to execute the package utility.

1. Sign in to SQL Server Integration Services.

2. Navigate to the folder from step 5 above where you deployed the package.

3. Find the package that you have deployed and select Run Package.

   

Schedule SSIS Package to Export Insights Data

If you would like to run the data export on a periodic basis, you can schedule the execution of the SSIS package using various options. For instructions on how to do so please visit the Microsoft documentation.