Export Data from SQL Server to Workday through SSIS

SQL Server databases are commonly used to store enterprise records. It is often necessary to move this data to other locations. The CData SSIS Task for Workday allows you to easily transfer Workday data. In this article you will export data from SQL Server to Workday.

Getting Started

---

Add Source and Destination Components

To get started, add a new ADO.NET Source control and a new Workday Destination control to the data flow task.

Configure the ADO.NET Source

Follow the steps below to specify properties required to connect to the SQL Server instance.

1. Open the ADO.NET Source and add a new connection. Enter your server and database information here.
2. In the Data access mode menu, select "Table or view" and select the table or view to export into Workday.
3. Close the ADO NET Source wizard and connect it to the destination component.

Create a New Connection Manager for Workday

Follow the steps below to set required connection properties in the Connection Manager.

1. Create a new connection manager: In the Connection Manager window, right-click and then click New Connection. The Add SSIS Connection Manager dialog is displayed.
2. Select CData Workday Connection Manager in the menu.
3. Configure the connection properties.

   To connect to Workday, users need to find the Tenant and BaseURL and then select their API type.

   Obtaining the BaseURL and Tenant

   To obtain the BaseURL and Tenant properties, log into Workday and search for "View API Clients." On this screen, you'll find the Workday REST API Endpoint, a URL that includes both the BaseURL and Tenant.

   The format of the REST API Endpoint is: https://domain.com/subdirectories/mycompany, where:

   • https://domain.com/subdirectories/ is the BaseURL.
   • mycompany (the portion of the url after the very last slash) is the Tenant.
   For example, in the REST API endpoint https://wd3-impl-services1.workday.com/ccx/api/v1/mycompany, the BaseURL is https://wd3-impl-services1.workday.com and the Tenant is mycompany.

   Using ConnectionType to Select the API

   The value you use for the ConnectionType property determines which Workday API you use. See our Community Article for more information on Workday connectivity options and best practices.

   API	ConnectionType Value
   WQL	WQL
   Reports as a Service	Reports
   REST	REST
   SOAP	SOAP

   
   

   Authentication

   Your method of authentication depends on which API you are using.

   • WQL, Reports as a Service, REST: Use OAuth authentication.
   • SOAP: Use Basic or OAuth authentication.

   See the Help documentation for more information on configuring OAuth with Workday.

Configure the Workday Destination

In the destination component Connection Manager, define mappings from the SQL Server source table into the Workday destination table and the action you want to perform on the Workday data. In this article, you will insert Workers entities to Workday.

1. Double-click the Workday destination to open the destination component editor.
2. In the Connection Managers tab, select the connection manager previously created.
3. In the Use a Table, menu, select Workers. In the Action menu, select Insert.
4. On the Column Mappings tab, configure the mappings from the input columns to the destination columns.

Run the Project

You can now run the project. After the SSIS Task has finished executing, data from your SQL table will be exported to the chosen table.