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)