Build Data Flows from SQL Server to ADP 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 ADP allows you to easily transfer ADP data. In this article you will export data from SQL Server to ADP.

Add Source and Destination Components

To get started, add a new ADO.NET Source control and a new ADP 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 ADP.
3. Close the ADO NET Source wizard and connect it to the destination component.

Create a New Connection Manager for ADP

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 ADP Connection Manager in the menu.
3. Configure the connection properties.

   Connect to ADP by specifying the following properties:

   • OAuthClientId: The client Id of the custom OAuth application you obtained from ADP.
   • OAuthClientSecret: The custom OAuth application's client secret.
   • SSLClientCert: Set this to the certificate provided during registration.
   • SSLClientCertPassword: Set this to the password of the certificate.
   • UseUAT: The connector makes requests to the production environment by default. If using a developer account, set UseUAT = true.
   • RowScanDepth: The maximum number of rows to scan for the custom fields columns available in the table. The default value will be set to 100. Setting a high value may decrease performance.

   The connector uses OAuth to authenticate with ADP. OAuth requires the authenticating user to interact with ADP using the browser. OAuth access can be configured in ADP through ADP API Central. For more information, refer ADP's API Central Quick Start Guide and the OAuth section in CData's Help documentation.

Configure the ADP Destination

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

1. Double-click the ADP 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. 👁 The destination table and action to be performed.
   
4. On the Column Mappings tab, configure the mappings from the input columns to the destination columns. 👁 The mappings from the SQL Server source to the SSIS destination component.
   

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.