In the fast-paced world of business technology, staying ahead means leveraging the best tools for automation and data management. Univate has SMEs for the best of these SaaS/PaaS tools, like Appian.

Appian, with its robust integration capabilities, offers a seamless way to automate the synchronization of employee data using Microsoft SQL Server as a connected system. This article provides a high-level tutorial on setting up an automated process that checks for employee details daily from an API endpoint and updates an SQL table accordingly.

Step 1: Setting Up the Connected System To begin, you’ll need to set up a connected system in Appian to interact with Microsoft SQL Server. This involves:

1. Navigating to the Appian Designer in Appian.
2. Selecting Connected Systems and clicking on New Connected System.
3. Choosing Microsoft SQL Server from the list of available systems.
4. Configuring the connection details, including the server name, database name, authentication method, and credentials.

Step 2: Configuring a Record Type with Sync Enabled Once the connected system is in place, the next step is to configure a Record Type that uses a table from SQL Server as its source:

1. Go to the Appian Designer and create a new Record Type.
2. Select Database as the source and choose the connected system you’ve set up.
3. Pick the table that contains employee data.
4. Enable Sync to ensure that the data is regularly updated according to a schedule you define.

Step 3: Creating an Integration Object With the Record Type configured, you’ll create an Integration object to retrieve employee information from the API endpoint:

1. In the Appian Designer, create a new Integration.
2. Set the Type to HTTP Integration.
3. Configure the Request section with the GET method and the URL “https://dummy.restapiexample.com/api/v1/employees”.
4. Define any necessary headers, parameters, and authentication details.

Step 4: Orchestrating the Process Model The automation comes together in a process model, which you’ll set up to run on a daily schedule:

1. Create a new Process Model in Appian.
2. Use a Start Event configured with a Timer to trigger the process every 24 hours.
3. Add a Script Task to call the Integration object and retrieve the latest employee data.
4. Include a Write Records node to update the SQL table with the new information.
5. Use Error Handling mechanisms to manage any exceptions that occur during the process.

Step 5: Testing and Deployment Before deploying the automated process, thorough testing is crucial:

1. Use the Process Modeler’s debugging tools to simulate the daily run and verify the data flow.
2. Check the SQL table to ensure that the employee details are updated correctly.
3. Once satisfied, deploy the process model and monitor its execution through the Appian Process Analytics.

Conclusion By following these steps, you can establish a reliable and automated system for synchronizing employee data between an external API and an SQL Server database within Appian. This setup not only streamlines data management but also ensures that your employee records are always up-to-date, allowing for more informed decision-making and operational efficiency.

Please note that this article provides a conceptual overview and does not contain detailed technical instructions related to your specific configuration. For complete and detailed information, refer to the official Appian documentation and resources provided by Microsoft for SQL Server integration.

I hope this guide serves as a helpful starting point for your Appian automation journey. If you have any further questions or require assistance, feel free to reach out for more in-depth support. Happy automating!