{"id":6116,"date":"2019-01-18T18:03:12","date_gmt":"2019-01-18T18:03:12","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6116"},"modified":"2022-12-27T20:29:26","modified_gmt":"2022-12-27T20:29:26","slug":"read-write-shopify-data-ssis-rest-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-write-shopify-data-ssis-rest-api\/","title":{"rendered":"Read \/ Write Shopify data in SSIS (REST API)"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In our previous article, we saw how to call REST API in SSIS<\/a>. Now in let’s use that knowledge and learn how to read\/write Shopify data in SSIS. If you are not aware of Shopify<\/a> then its one of the most popular eCommerce platforms out there for small shops who sell online. Shopify provides a total framework to sell\/fulfill orders online including tools to build\/customize your own web store.<\/p>\n

In this article we will see few examples on how to call basic REST API calls to read shopify data, delete shopify data and write shopify data using various SSIS Components.<\/p>\n

 <\/p>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Create Shopify Custom App for Shopify Admin API<\/h2>\n

    Before accessing Shopify API you must create Custom App (and then obtain Access Token)<\/p>\n

    New approach (Custom App) to generate App Credentials for Rest API Call<\/h3>\n

    Before you call Shopify REST API, your first step is to obtain credentials. We will need Access Token or Storefront Access Token. We will use it in later section to call Shopify REST API.<\/p>\n

      \n
    1. First, in Shopify, go to sore Settings (Click Gear Icon) >\u00a0 Apps\u00a0and\u00a0sales channels<\/strong> > click\u00a0Develop apps for your Store<\/strong>
      \n<\/strong><\/strong> OR just navigate to URL<\/p>\n
      https:\/\/{your-store}.myshopify.com\/admin\/settings\/apps\/development<\/pre>\n
      \"Shopify<\/a>
      Shopify App and Sales Channel<\/p><\/div>\n
      \u00a0<\/strong><\/li>\n
    2. In App development<\/strong>, click on Create an app
      \n<\/strong><\/strong><\/strong><\/strong><\/p>\n
    3. Provide the appropriate name for your custom app.\n
    4. Once we created the app name, we have 2 options to create Admin API<\/strong> or Storefront API<\/strong> in the Overview tab.\"\"<\/a>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Shopify App Scopes and Configuration<\/li>\n
    5. Click on Configure Admin API scopes or Configure Storefront API scopes based on your need as shown in the above screenshot.<\/li>\n
    6. If click on\u00a0Configure Admin API scopes, then select the appropriate scopes for your Admin API.<\/li>\n
    7. Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.<\/li>\n
    8. In addition, change the products, variants, and collections\u00a0to read and write if you need to create, update and delete products.\n
    9. After saving it, it asks to install app and generate Shopify Access Token for your Admin API.\n
    10. If click at\u00a0Configure Storefront API scopes, then select the appropriate scopes for your Storefront API.<\/li>\n
    11. Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.<\/li>\n
    12. In addition, change the products, variants, and collections\u00a0to read and write if you need to create, update and delete products.\n
    13. After saving it, it asks to install app and generate Shopify Storefront Access Token for your Admin API shown above screenshot.\n
    14. \u00a0We have created Admin API Access Token and Storefront API\u00a0Access Token to access the Shopify APIs.\n
      \"\"<\/a>
      Shopify API Credentials<\/p><\/div><\/li>\n<\/ol>\n
      Old approach (Private App) to generate App Credentials for Rest API Call<\/h3>\n
      Before you call Shopify REST API, your first step is to obtain credentials. We will need API Key and Password. We will use it in later section to call shopify REST API.<\/p>\n
        \n
      1. First, in Shopify, go to Apps<\/strong> and click Manage private apps<\/strong>.\n
      2. \u00a0Secondly, In Private apps, click the Create a new private app:<\/strong><\/strong><\/strong>\n
      3. Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.<\/li>\n
      4. In addition, change the products, variants, and collections\u00a0to read and write if you need to create, update and delete products.\n
      5. Finally, you will have an API key, a password, an URL and a shared\u00a0 secret that you will use in the next section:<\/li>\n<\/ol>\n
        \"Shopify<\/a>
        Shopify API key<\/p><\/div>\n
         <\/p>\n
        Read Shopify data in SSIS (Get Orders Example)<\/h2>\n
        So in this section we will learn how to call Shopify REST API.\u00a0 For example purpose we will call Shopify Orders Endpoint<\/a>. For all other REST API endpoints check this link<\/a>. Read carefully which parameters you can pass for each API call. So let’s read shopify orders using sample API call.<\/p>\n
        Configure SSIS JSON Source to read from Shopify<\/h3>\n
        Now, that we created an App in Shopify, the rest is easy.<\/p>\n
          \n
        1. First, in an SSIS project, drag and drop the Data Flow and double click it.\n
          \"Drag
          Use the Data Flow<\/p><\/div><\/li>\n
        2. Secondly,\u00a0in the Data Flow, drag and drop the ZS JSON Source.\n
          \"SSIS
          SSIS JSON Source – Drag and Drop<\/p><\/div><\/li>\n
        3. Double-click on JSON Source to configure it.If you are not familiar with ZS JSON Source, check this link.<\/a>
          \nNew approach to establish the HTTP Connection Manager<\/strong><\/p>\n
          Using Admin API Access\u00a0token<\/strong><\/p>\n
            \n
          1. Enter URL for the API you like to call. For Example, you can enter\n
            https:\/\/{your-store}.myshopify.com\/admin\/api\/2022-10\/products.json<\/pre>\n<\/li>\n
          2. Check Use Credentials and click on NEW-HTTP connection as shown below,<\/li>\n
          3. On HTTP Connection Manager you can enter some URL (it will be ignored and use JSON Source URL above)<\/li>\n
          4. Choose Credentials Type as Static Token \/ API Key<\/strong><\/li>\n
          5. In the Token \/ Key text box enter your Admin API Key<\/strong> (obtained from the previous section)<\/li>\n
          6. In the Auth Header Name enter X-Shopify-Access-Token<\/span><\/li>\n
          7. In the Auth Scheme<\/strong> select {none}<\/strong><\/strong> \n
          8. Also, press the Select Filter<\/strong> button and press the orders node:\n
          9. Now go to Pagination Tab and select RFC5988 Pagination mode<\/strong> as below. Check this link for more info on Shopify Paginated API calls<\/a>.\n
          10. Finally,\u00a0make sure to the Use Credentials option and select the http\u00a0connection of the step 2 and press the Preview button to see the data:<\/li>\n<\/ol>\n
            \"Get<\/a>
            SSIS preview Shopify data<\/p><\/div>\n
             <\/p>\n
            Load Shopify data into SQL Server \/ Other Target<\/h3>\n
            ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n
            Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n
              \r\n
            1. Drag and drop the Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n
            2. Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n
              \r\n\r\n\"\"<\/a>\r\n
              SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n
              Step 2: Configure Target Connection<\/h3>\r\n
                \r\n
              1. Double-click the Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n
              2. Under Connection<\/strong>, select an existing target connection or click NEW<\/strong> to create a new connection.\r\n