Content

• Step-By-Step
• Properties
• Error Handling
• Settings UI
• FAQ
• References
• See also

Video Tutorial

1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
   

Here, This is Just Example of How to Insert Data into Smartsheet Using Template Transform

4. Double click on the Data Flow task to see Data Flow designer surface.
5. Create Smartsheet Account.
6. Create Smartsheet and add columns name you want to insert Data.
7. To perform this task you will need : Access Token, Sheet ID and Column ID
8. Here, In Visual Studio, drag and drop the OLEDB Source, ZS Template Transform and ZS Web API Destination in the design panel
   
9. In the above step you can use our Free ZS Dummy Data Source too. Click here for more information
10. We are going to Insert data Using SQL Server Database, connect OLEDB Connection for Data Source.
11. Let's, Right click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.
    
    Now, click on New Button for create Connection.
    
    Let's Configure Connection Manager, just Follow steps one by one as we have in Screenshot
    
    
12. Double click on OLEDB Source for configure it.
13. Lets Configure in Connection Manager, just follow below image steps.
    
14. Click Ok to Save OLEDB Source Editor UI Settings.
15. Now lets, Double click to configure SSIS ZS Template Transform to create desired ZS REST API Destination for API call.
    

    Note : This is only use for Insert Into Smartsheet.
    
    Example : This is JSON Format. 
    
    {
      "toTop": true,
      "cells": [
        {
          "columnId": 476590783129476,
          "value": "<%ID%>"
        },
        {
          "columnId": 4980190410499972,
          "value": "<%GeographyKey%>"
        },
        {
          "columnId": 2728390596814724,
          "value": "<%Name%>"
        },
        {
          "columnId": 7231990224185220,
          "value": "<%Destination%>"
        },
        {
          "columnId": 1602490689972100,
          "value": "<%Country%>"
        }
      ]
    }
    

    
16. For REST API Destination, you can Select Direct URL or URL from Variable as Request URL Access Mode., In this tutorial we do with HTTP Connection.
17. Now lets, make REST API call with HTTP Connection.
18. Let's, Configure HTTP Request properties as below
19. Right click on Connection Managers Panel to Create HTTP Connection, so you can use Source Path, and Context Menu will appear, Select New Connection from the Context Menu.
    
20. Select ZS-HTTP Connection Manager from the Connection Managers list and Click on Add Button
    
21. Now, in Connection Manager UI, Enter Web URl (Replace your Sheet ID). for now We do not need Credentials type so select Not Set and You can select if you need it.

    https://api.smartsheet.com/2.0/sheets/YOUR-SHEET-ID/rows
    

    
    
22. Double click on ZS REST API Destination for configure it.
23. While configure REST API Destination we need to do below settings.
    • In the Select Connection select HTTP Connection.
    • In the input column for Body select TemplateOutput.
    • For Body you can enter Sample Body if you wish to Test at design time else leave it blank
    • Select Body Content Type as application/json.
    • In the Headers enter Authorization Header like

      Example :  Replace Your Access Token
      
      Authorization: Bearer YOUR-TOKEN-GOES-HERE
      

    
24. Go to Batch Setting mode. Change following settings
    • Check on Enable submitting multiple records
    • For Body Header enter [
    • For Body Footer enter ]
    • For Body Row separator enter , (i.e comma)
    • You can change Body Batch Size to desired value (100 is recommended)
    
25. Click Ok to Save REST API Destination UI Settings.
26. That’s it run your package to test the entire flow.
    

Properties

Setting UI