SQL Connector Set Up Guide

News Knowledge Base
1

SQL connector set up

Connector guide:

  • Make sure that the steps here are followed in order to successfully use a git repository
  • For the error shared - that is a common error coming from GIT - please see here a possible solution for it
  • See below for more in detail steps on how to set up the SQL connector successfully - also a very good video tutorial is available on the UiPath Academy Course

“If you want to try out building a new SQL Connector, copy the template connector that has sample data and sample structure for a SQL Connector. If you would like to use your own data, you can use the template as a reference for how to structure your own files. It is advised to store your connector in a version control system, for example, GitHub.”

❶ Create a Git repository using the template connector (or create a new connector using your own data).

  • Create a GitHub account
  • Create a new repository with your own data or use the sample data provided by UiPath at GitHub - UiPath/ProcessMining-devkit-connector
  • Select Use this template
  • Create a Repository name (ex. UiPathProcessMining-sample-connector)
  • Click Create repository from template

❷ Access your Process Mining server

  • Launch your Process Mining server
  • Login with credentials

❸ Configure the Git repository under the Settings tab. (this is the step you are referring to)

  • Download & Install Git Bash
  • Generate SSH keys and add to GitHub
  • Save public and private key files to the Process Mining server databases > keys folder
  • Obtain the computer name and admin credentials for the server with SQL installed
  • Open Remote Desktop Connection
  • Click Show Options
  • Enter computer name
  • Fill in admin user name
  • Click connect
  • Enter admin password
  • Open File Explorer
  • Navigate to C:/UiPathProcessMining/databases/
  • Open/Create a keys folder
  • Copy and paste public and private keys into folder

In Process Mining training server > Settings > Repositories > Add GitHub Repository

❹ Upload the TemplateOne.mvtag file under the Releases tab in the Superadmin interface.

  • Login to the Process Mining Superadmin interface
  • Go to Releases
  • Check to see if there’s a release for TemplateOnev1.0.0.mvtag, if yes, skip the following steps
  • Download the TemplateOnev1.0.0.mvtag file
  • External users: The TemplateOne.mvtag file will be shared with you by UiPath
  • Go back to the Superadmin interface and click the Hamburger menu under the Releases tab
  • Select Upload a release to this server...
  • Select mvtag file from your downloads

❺ Create a new app , point to your repository, and select the release uploaded in the previous step as a Base App.

  • In the Workspaces tab > click the hamburger menu > New…
  • Repository: Select your Git repository
  • Base App: Select the TemplateOne release uploaded in the previous step
  • Name: Name your app

Note: If you get an error that states “Failed to retrieve list of SSH authentication methods: Failed getting response” when creating your new app, regenerate the keys again in Git Bash using this command: ssh-keygen -t ed25519 -C "your_email@example.com" and upload to the UiPathProcessMining > databases > keys folder

❽ ❻ Clone the new branch from the Git repository to your local machine.

  • Open Git Gui
  • Clone Existing Repository
  • Source Location: SSH code from GitHub repository
  • Go to GitHub > Click on picture in top right > Your repositories
  • Select the repository you wish to use or create a new one
  • Click the Code button > SSH tab > Copy the full git@github.com....git address
  • Target Directory: A new folder in any location

❼ Create a database and schema using SQL Server Management Studio(SSMS).

  • Go to Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS)
  • Scroll to Download SSMS and click the Download link
  • Open .exe file and Install
  • Open SSMS
  • Configure connection
  • Server name: your SQL server
  • Authentication: SQL Server Authentication
  • Sign in with server credentials
  • Create a database
  • Right click Databases > select New Database…
  • Type a Database name and click OK
  • Create a schema
  • Unfurl your new database
  • Unfurl Security folder
  • Right click Schemas > select New Schema…
  • Type a Schema name and click OK

❽ Install CData and launch it.

❾ Create a user for your CData Sync.

  • Launch CData Sync Admin Console
  • Create a password for the admin user

❿Login to CData with the user created in the previous step.⓫ Create a source connection in CData

  • Click Connections in the top menu
  • Under the Sources tab > select CSV
  • Create a name (ex. TemplateOneSource)
  • Go to the directory of the cloned Git repository > open the sample_data folder and unzip the data folder
  • Copy the path of the unzipped data folder
  • Paste file path into the URI field
  • Click Test Connection to verify it’s successful
  • Click Advanced
  • FMT: TabDelimited
  • Type detection scheme: None
  • Click Save

⓬ Create a destination connection in CData - make sure to provide the correct database name and credentials.

  • Click Connections in the top menu
  • Under the Destinations tab > Select SQL Server
  • Create a name (ex. TemplateOneDestination)
  • Server: your SQL Server name
  • Database: the database you created in the previous step
  • Enter the user name and password used to log into the SQL Server
  • Click Test Connection to verify it’s successful
  • Click Save

⓭ Create a job, point to the source and destination connection.

  • Click Jobs
  • Click Add Job...
  • Create a Job name (ex. CsvToSqlTemplateOne)
  • Select your Source and Destination created in the last steps
  • Click Create

⓮Add custom query, or add tables and point to the right schema.

  • Click Add Custom Query
  • navigate to your GitHub repository
  • Select extractors/load-from-file folder
  • Select instructions.md
  • Copy the custom query under Configuration:

REPLICATE [Change_log_raw] SELECT * FROM [Change_log];

REPLICATE [Invoices_raw] SELECT * FROM [Invoices];

REPLICATE [Purchase_order_approvals_raw] SELECT * FROM [Purchase_order_approvals];

REPLICATE [Purchase_orders_raw] SELECT * FROM [Purchase_orders];

REPLICATE [Purchase_orders_status_raw] SELECT * FROM [Purchase_orders_status];

REPLICATE [Users_raw] SELECT * FROM [Users];

  • Navigate back to Cdata > paste query > click Add
  • Click Save Changes
  • Under Job Settings click Advanced
  • Under Destination Schema put the schema created in the previous step
  • Check the box for Drop Table
  • Click Save Changes

⓯ Open the project you cloned in step number 8, in Visual Studio Code.

  • Download and Install Visual Studio Code
  • Navigate to your project folder in File Explorer
  • Right click and select Open with Code

⓰ Update the dbt_project.yml file in Visual CodeSelect the dbt_project.yml file within the transformations folder and update the schema_sources to point to your schema

The Academy course is UiPath Process Mining SQL Connector Deep Dive.