B) Reading Data from an API (GET Requests)

B) Reading Data from an API (GET Requests)

Section table of contents: Section Table of Contents

Previous article: Configuring a REST Connection Manager


In this article:

Part 1: Listing Records

Part 2: Composite Columns



Part 1: Listing Records

Step 1. Once you have created a REST Connection Manager in the bottom panel, open the SSIS Toolbox panel which is typically on the left-hand side of Visual Studio. Drag-and-drop or double-click the Data Flow Task to create a new component in the Control Flow screen.


Step 2. Go to the SSIS Toolbox and add a REST Source component and Union All component to your Data Flow screen.


Step 3. Double-click the REST Source component to open its general settings in the editor. Select your REST connection manager for your connection, select Task for this example as the resource, and use the <default> endpoint which provides a list of records for the resource we selected.


Step 4. You will need the list_id parameter for this resource endpoint. One way to find this is to go to the ClickUp UI, select a list, and copy the string at the end of the URL. This is your list identifier, which may be a 9-digit integer. Enter this parameter into the REST Source Editor's general settings in SSIS+.


Step 5. You can now retrieve the columns (fields) for this endpoint through the Columns settings of the REST Source Editor. These columns will auto-populate when you open this dialogue box, but you may need to click the Refresh button if the RCM file has changed or if the API itself has changed. Not all fields will be pre-selected, so you will want to click the checkboxes for the fields you want to see.


Step 6. Click Preview to see a table with your data. Columns with no data will show up as a list under Hidden Columns at the bottom.


Step 7. You have many options for what to do with this data using CozyRoc SSIS+. Aside from viewing data in the preview, among the simplest steps you can take is to connect a Union All component from the toolbox. Drag the arrow from the REST Source to the Union All to connect them together. A dialogue box for Input Output Selection will open, and you should select Task as the output. The input will be pre-selected.


Step 8. Click OK, then right-click the arrow and select "Enable Data Viewer". Save your work and go to the Control Flow screen. You can single-click the text in a component to change its name to something more descriptive if desired. Now right-click the Data Flow Task component and select "Execute Task". Visual Studio will begin executing the workflow, and you'll see a progress indicator on the component.


Step 9. A dialogue box will open showing a table of your data for ClickUp tasks. Here we can see the description of each task as well as a few other fields. You may select multiple rows by holding the shift key and then copy-paste the data into Microsoft Excel or other programs by clicking the Copy Data button. And there you have it, your data is ready to modify and load into other applications!



Part 2: Composite Columns

Step 1. In certain endpoints, you will have the option to unpack certain composite columns/fields to your output data stream. These fields can be selected under the Output name dropdown, allowing you to more easily work with data from arrays and more complex JSON objects.


Step 2. Once you select a composite column, Task_tags_{} for this example, you will see new Available External Columns at the bottom of the list. Check all four of these columns, and close the dialogue box.

Step 3. Delete any old connections from your REST Source component, then drag a new arrow to the Union All component. Input Output Selection will open. Select Task_tags_{} as the output.


Step 4. Right-click the connector arrow and enable data viewer. You should see "Task_tags_{}" under the arrow, representing the composite field. Right-click the component in the Control Flow screen and Execute Task. When the data viewer opens, you will see your composite tag fields broken into individual columns in the far right of the table. Rows contain null values if there is no value present for a field.


Next article: Creating Records through an API (POST Requests)


    Book a Presales Call


    Are you new to COZYROC and evaluating our solutions? 
    Book a Presales Call with us now and get 5% off SSIS+.
      • Related Articles

      • D) Updating Data through an API (PUT, PATCH Requests)

        Section table of contents: Section Table of Contents Previous article: Creating Records through an API (POST Requests) Step 1. You can copy-paste the Data Flow Task component you've used to create a record for a resource or create a new one in the ...
      • C) Creating Records through an API (POST Requests)

        Section table of contents: Section Table of Contents Previous article: Reading Data from an API (GET Requests) Step 1. You should now be proficient with setting up an Integration Services Project in Visual Studio and reading data from an API with ...
      • E) Deleting Data from an API (DELETE Requests)

        Section table of contents: Section Table of Contents Previous article: Updating Data through an API (PUT, PATCH Requests) Step 1. Copy-paste the Data Flow Task used for updating data through the API. Step 2. Open the JSON Source component and modify ...
      • Getting Started with REST API Connections in CozyRoc SSIS+: Section Table of Contents

        Guide table of contents: Guide to REST API Connections in CozyRoc SSIS+ Get acquainted with REST API connectors in CozyRoc SSIS+ so that you can integrate data sets and databases across web services and platforms. CozyRoc SSIS+ Components Suite is a ...
      • Guide to REST API Connections in CozyRoc SSIS+: Table of Contents

        Table of Contents I. Introduction II. Installing CozyRoc SSIS+ A) Installing Visual Studio and SSDT B) How to Download and Install the CozyRoc SSIS+ Components Suite C) Check Software Versions D) Licensing CozyRoc SSIS+ III. The Basics of SSIS+ IV. ...