SSIS tutorial: How to import data from Alchemer

[daniel]

Introduction

In this article, we will walk you through how to import and export data from Alchemer (formerly SurveyGizmo) into SQL Server Integration Services using ZappySys SSIS Components. Alchemer is a powerful survey platform that helps businesses collect feedback and gain valuable insights from survey responses. With ZappySys SSIS components, you can automate the process of transferring survey data to and from SQL Server, Excel, or other business intelligence tools, allowing for seamless reporting and analysis.

Prerequisites

• Download and install the ZappySys SSIS PowerPack.
• A valid Alchemer account.

Steps

Step 1: Obtain Alchemer API credentials

1. Visit Alchemer Login and sign in with your credentials.

2. To generate an API Key and API Secret Key, administrative users should navigate to Security > API Access and click Create an API Key.
   

   

3. Since API keys are generated per user, you will need to select the user for whom you wish to generate the keys. Make sure to generate keys for each user who will access the API. You can check the API documentation here.
   

   

Step 2: Add the Azure Table Storage Source

1. In SSIS, go to the Variables tab and create two string variables for storing the keys: one for the API key ({{User::API_key}}) and the other for the API secret key ({{User::API_secret_key}}).

2. Add a Data Flow Task to your control flow and double-click it to enter the Data Flow tab.

3. Drag and drop the ZappySys JSON Source component into the Data Flow Task.
   

   

4. Double-click the component to configure it.

5. In the URL field, enter the following URL using the variables for the API key and secret:
   https://restapi.surveygizmo.com/v5/survey?page=1&resultsperpage=100&api_token={{User::API_key}}&api_token_secret={{User::API_secret_key}}
   

   

6. Go to the Pagination tab and select URL Parameter Mode.

7. Use page as the number indicator and set it to increment by 1. For more information on pagination, refer to our pagination article.

8. For the last page detection, use Detect based on missing row with the condition set to True.
   

   

9. In the Filter field, use $.data[*] to extract the survey responses. Click Preview to verify the data and press OK to save the configuration.
   

   

Step 3: Add the Destination Component

1. Add a destination connection and link it to the ZappySys JSON Source. You can use the trash destination to preview the results.

2. Run the SSIS package to verify that the data from Alchemer is being successfully retrieved and stored in your destination.
   

   

Conclusion

By integrating Alchemer with the ZappySys SSIS PowerPack, you can automate the process of importing and exporting survey data for better reporting, analysis, and decision-making. This integration allows you to easily interact with Alchemer’s REST API, enabling you to efficiently manage survey data directly within SQL Server, Excel, or other SSIS-compatible tools.

Visit our official page to explore more SSIS components, automation features, and real-time data integration tools included in ZappySys SSIS PowerPack.

References

• JSON source
• JSON source documentation
• Blog articles
• SSIS PowerPack

Contact us

If you encounter any issues or have specific requirements, feel free to reach out to our support team via chat or support ticket.