{"id":10902,"date":"2023-12-29T12:49:39","date_gmt":"2023-12-29T12:49:39","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=10902"},"modified":"2025-12-11T12:52:13","modified_gmt":"2025-12-11T12:52:13","slug":"upload-files-sharepoint-online-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/","title":{"rendered":"How to upload files on SharePoint Online using SSIS"},"content":{"rendered":"

Introduction<\/strong><\/h2>\n

\"sharepoint<\/a>In today’s data-driven world, the ability to seamlessly integrate and transfer files between different platforms is crucial for efficient business operations. If you’re a user of SQL Server Integration Services (SSIS) and looking to integrate SharePoint Online into your data workflow, you’re in luck. ZappySys SSIS PowerPack offers a powerful SharePoint Online Connector<\/a> that makes uploading files to SharePoint Online a breeze. In this guide, we’ll walk you through the process of uploading files to SharePoint Online using SSIS and the ZappySys SSIS PowerPack SharePoint Online Connector<\/a>.<\/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

    Upload files to SharePoint Online with SSIS:<\/h2>\n

    In this section, we will learn how to configure and use SharePoint Online Connector in the API Source to upload files on SharePoint Online.<\/p>\n

      \n
    1. Begin by opening Visual Studio and creating a new project.<\/li>\n
    2. Select Integration Service Project and in the new project window set the appropriate name and location for the project. Click OK.<\/li>\n
    3. In the new SSIS project screen you will find the following:\n
        \n
      1. SSIS ToolBox on the left sidebar<\/li>\n
      2. Solution Explorer and Property Window on the right bar<\/li>\n
      3. Control flow, data flow, event Handlers, Package Explorer in tab windows<\/li>\n
      4. Connection Manager Window at the bottom<\/li>\n<\/ol>\n
      5. Now, Drag and Drop the SSIS Data Flow Task from the SSIS Toolbox. Double-click on the Data Flow Task to see the Data Flow Designer.
        \n        \"\"<\/a><\/li>\n
      6. From the SSIS toolbox drag and drop API Source (Predefined Templates) on the data flow designer surface, and double-click on it to edit it:
        \n        \"\"<\/a><\/li>\n
      7. Select New Connection<\/strong> to create a new connection:
        \n        \"\"<\/a><\/li>\n<\/ol>\n

        Configuring the SharePoint Online Connector<\/h2>\n
          \n
        1. Use a preinstalled SharePoint Online Connector from the Popular Connector List or press the Search Online radio button to download SharePoint Online Connector. Once downloaded simply use it in the configuration:\n
        2. Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed.<\/li>\n
        3. After configuring all the required properties in the API Connection Manager, please click on the “Test Connection” button to confirm that the connection is established successfully.
          \n

          Method1: Connect to SharePoint Online using User Credentials [OAuth]<\/h3>\n

          You may press a link Steps to Configure<\/strong><\/a> which will help set certain parameters.<\/p>\n

          \"SSIS<\/a>

          SSIS ZS API Connection Manager – Generate Token – SharePoint Online with User Credentials<\/p><\/div>\n

          Method2: Connect to SharePoint Online using Application Credentials [OAuth]<\/h3>\n

          You may press a link, Steps to Configure<\/strong><\/a>, to help set specific parameters.<\/p>\n

          \"SSIS<\/a>

          SSIS API Source – Generate Token – SharePoint Online with Application Credentials<\/p><\/div><\/li>\n<\/ol>\n

          How to upload a local file to SharePoint Online<\/h2>\n

          In the API Source Component, we’ve chosen the API Connection Manager. Next, we need to configure the remaining properties of the component to upload a local file to SharePoint Online.<\/p>\n

            \n
          1. Choose the Upload file<\/strong> endpoint from the “Select Table\/Endpoint” dropdown menu.<\/li>\n
          2. Select the desired Drive ID from the DriveId<\/strong> drop-down.<\/li>\n
          3. Specify the desired source file path for the file being uploaded by setting the DiskFilePath<\/strong>\u00a0parameter.<\/li>\n
          4. Specify the desired Target Path by setting the SharePointFilePath<\/strong> parameter.<\/li>\n<\/ol>\n

            Please refer to the sample screenshot below.<\/p>\n

            \"SSIS<\/a>

            SSIS API Source \u2013 SharePoint Online \u2013 Upload Single File<\/p><\/div>\n

            Once you have configured all the mentioned properties above, please click on the “Preview Data” button to initiate the API call. With just a few clicks, we have successfully configured the call to SharePoint Online using the ZappySys SharePoint Online Connector<\/strong> for uploading a local file to SharePoint Online.<\/p>\n

            How to upload multiple files to SharePoint Online<\/h2>\n

            In the API Source Component, we’ve selected the API Connection Manager. Next, we need to configure the remaining properties of the component to upload multiple files to SharePoint Online. Additionally, we should utilize a Foreach Loop Container to iterate through each file one by one in a loop.<\/p>\n

              \n
            1. Create 3 SSIS variables as described below.
              \n\n\n\n\n\n\n
              Variable Name<\/th>\nDatatype<\/th>\nValue<\/th>\n<\/tr>\n
              varFileLists<\/td>\nObject<\/td>\nSystem.Object<\/td>\n<\/tr>\n
              varFileName<\/td>\nString<\/td>\nfake.extension<\/td>\n<\/tr>\n
              varFullFilePath<\/td>\nString<\/td>\nC:\/fake\/fake.extension<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n
            2. Go to Control Flow, From the SSIS toolbox drag and drop ZS Advanced File System Task<\/strong> on the control flow designer surface, and double click on it to edit it:
              \n\"SSIS<\/a><\/li>\n
            3. Select the “Get file list as ADO.NET Data Table” action from the Action drop-down menu. Set the file path in the Path textbox. Choose the variable (Object type) where you want to store the file list, and then click the OK button to save the settings.\n
            4. Now, drag and drop the “Foreach Loop Container” from the SSIS Toolbox and connect it with the “ZS Advanced File System Task.” Double-click on the “Foreach Loop Container” to edit its properties.\n
            5. In the Foreach Loop Editor’s “Collection” tab, choose the Enumerator as “Foreach ADO Enumerator.” In the “ADO object source variable” field, select your SSIS object-typed variable. Select the “Rows in the first table” radio option.\n
            6. In the Foreach Loop Editor’s “Variable Mappings” tab, select the variables that you want to map to the values from the collection, and then click the “OK” button to save your selections.\n
            7. Inside the Foreach Loop Container, drag and drop the Data Flow Task, and then rename it according to your preference.\n
            8. Double-click on the Data Flow Task to see the Data Flow Designer, From the SSIS toolbox drag and drop API Source (Predefined Templates) on the data flow designer surface, and double-click on it to edit it:
              \n              \"SSIS<\/a><\/li>\n
            9. Double-click on the API Source<\/strong> component to configure it for uploading multiple files to SharePoint Online.\n
                \n
              1. Choose the Upload file<\/strong> endpoint from the “Select Table\/Endpoint” dropdown menu.<\/li>\n
              2. Please specify the desired SSIS Variable [varFileName] containing the uploaded file name by setting the SharePointFilePath<\/strong> parameter.<\/li>\n
              3. To specify the desired local SSIS variable [varFullFilePath], which holds the file path, set the DiskFilePath<\/strong> parameter.
                \nWhen configuring the SSIS variable directly in the Raw Edit, JSON may become invalid due to the requirement of slashes within the local file path. To address this, we use a JSONENC Placeholder Function for proper escaping.
                \n<<{{User::varUserFullFilePath}},FUN_JSONENC>><\/span><\/li>\n
              4. If you wish to upload the local file to a specific folder in SharePoint Online, please set the folderName in the\u00a0SharePointFilePath <\/strong>parameter.<\/li>\n<\/ol>\n

                Please refer to the sample screenshot below.<\/p>\n

                \"SSIS<\/a>

                SSIS API Source \u2013 SharePoint Online \u2013 Upload Multiple files configuration<\/p><\/div>\n

                To configure dynamic filenames and file paths, you can set variables. Click on “Raw Edit,” and in the “Edit Parameters” popup, you can use SSIS variables, as shown in the above image.<\/p>\n

                Once you have configured all the mentioned properties above, please click on the “Preview Data” button to initiate the API call.
                \nAfter that, you can execute the SSIS Package and check the execution to upload multiple files on SharePoint Online. Below is the sample screenshot attached.<\/p>\n

                \"Multiple<\/a>

                Multiple files uploaded on SharePoint Online using SSIS<\/p><\/div>\n

                With just a few clicks, we have successfully configured the call to SharePoint Online using the ZappySys SharePoint Online Connector<\/strong> for uploading multiple files to SharePoint Online.<\/li>\n<\/ol>\n

                Conclusion<\/h2>\n

                In this blog, we learned how to connect with SharePoint Online using SSIS API Connection Manager and upload single or multiple files to SharePoint Online using a combination of SSIS components, including the SSIS API Source component<\/a>, SSIS ZS Advanced File System Task<\/a>, Foreach Loop Container, and SSIS variables.<\/p>\n","protected":false},"excerpt":{"rendered":"

                Introduction In today’s data-driven world, the ability to seamlessly integrate and transfer files between different platforms is crucial for efficient business operations. If you’re a user of SQL Server Integration Services (SSIS) and looking to integrate SharePoint Online into your data workflow, you’re in luck. ZappySys SSIS PowerPack offers a powerful SharePoint Online Connector that […]<\/p>\n","protected":false},"author":6,"featured_media":3731,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[616,16,8],"tags":[618,632,12,79,617,634,633],"yoast_head":"\r\nHow to upload files on SharePoint Online using SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Upload files to SharePoint Online using SSIS without coding. Learn to use the ZappySys REST API Task and Microsoft Graph API for secure, automated uploads.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to upload files on SharePoint Online using SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Upload files to SharePoint Online using SSIS without coding. Learn to use the ZappySys REST API Task and Microsoft Graph API for secure, automated uploads.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2023-12-29T12:49:39+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-12-11T12:52:13+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"225\" \/>\r\n\t<meta property=\"og:image:height\" content=\"225\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/\",\"name\":\"How to upload files on SharePoint Online using SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\",\"datePublished\":\"2023-12-29T12:49:39+00:00\",\"dateModified\":\"2025-12-11T12:52:13+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35\"},\"description\":\"Upload files to SharePoint Online using SSIS without coding. Learn to use the ZappySys REST API Task and Microsoft Graph API for secure, automated uploads.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\",\"width\":225,\"height\":225,\"caption\":\"sharepoint icon\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to upload files on SharePoint Online using SSIS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"url\":\"https:\/\/zappysys.com\/blog\/author\/hshah\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to upload files on SharePoint Online using SSIS | ZappySys Blog","description":"Upload files to SharePoint Online using SSIS without coding. Learn to use the ZappySys REST API Task and Microsoft Graph API for secure, automated uploads.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to upload files on SharePoint Online using SSIS | ZappySys Blog","og_description":"Upload files to SharePoint Online using SSIS without coding. Learn to use the ZappySys REST API Task and Microsoft Graph API for secure, automated uploads.","og_url":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/","og_site_name":"ZappySys Blog","article_published_time":"2023-12-29T12:49:39+00:00","article_modified_time":"2025-12-11T12:52:13+00:00","og_image":[{"width":225,"height":225,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","type":"image\/jpeg"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/","url":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/","name":"How to upload files on SharePoint Online using SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","datePublished":"2023-12-29T12:49:39+00:00","dateModified":"2025-12-11T12:52:13+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35"},"description":"Upload files to SharePoint Online using SSIS without coding. Learn to use the ZappySys REST API Task and Microsoft Graph API for secure, automated uploads.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","width":225,"height":225,"caption":"sharepoint icon"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/upload-files-sharepoint-online-using-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to upload files on SharePoint Online using SSIS"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g","caption":"ZappySys"},"url":"https:\/\/zappysys.com\/blog\/author\/hshah\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/10902"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=10902"}],"version-history":[{"count":8,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/10902\/revisions"}],"predecessor-version":[{"id":11206,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/10902\/revisions\/11206"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3731"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=10902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=10902"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=10902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}