Download files from Sharepoint 365 using SSIS

Question

I have a document library (Sharepoint 365) named "S & P" which contains many folders & sub-folders. My requirement is to download all the files exists in these folders/sub-folders to my local/server folder by using SSIS package. This can be done by either using a script task (C# or VB code) or execute process task(Powershell or Batch script) but I am not able to pull any file. So far I have tried to use powershell - failed & C# code - failed. All the code used was copied from internet (don't know much of C# & Powershell).

Any solution to retrieve these files from sharepoint to my local folder would be accepted (can't use 3rd party or unauthorized tools)

SharePoint URL structure is like below- https://xyzcompany.sharepoint.com/sites/dm/S%20%20P/Forms/AllItems.aspx

Note: All folders & subfolder are in S & P document library

Please help!

Answer 1

About downloading all the files from SharePoint Document library via C# code, here is a demo for your reference and i have tested successfully.

    /// <summary>
    /// download all files from Document Library
    /// </summary>
    /// <param name="context"></param>
    /// <param name="docLibName">MyDocumentLibrary</param>
    /// <param name="path">C:\\folder\\</param>
    public static void DownloadAllFilesFromDocLib(ClientContext context, string docLibName, string path)
    {
        if (!path.EndsWith("\\"))
        {
            path = path + "\\";
        }
        Web web = context.Site.RootWeb;

        List doclib = web.Lists.GetByTitle(docLibName);

        context.Load(doclib);

        context.Load(web);
        context.ExecuteQuery();

        FileCollection filesInRootFolder = doclib.RootFolder.Files;
        FolderCollection subfolders = doclib.RootFolder.Folders;
        context.Load(filesInRootFolder);
        context.Load(subfolders);
        context.ExecuteQuery();

        //download files from root folders
        foreach (Microsoft.SharePoint.Client.File file in filesInRootFolder)
        {
            FileInformation fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(context, file.ServerRelativeUrl);               
            System.IO.Stream fileOutputStream = fileInfo.Stream;
            System.IO.Stream fileInputputStream = new FileStream(path + file.Name,FileMode.OpenOrCreate, FileAccess.ReadWrite);
            byte[] bufferByte = new byte[1024 * 100];

            int len = 0;
            while ((len = fileOutputStream.Read(bufferByte, 0, bufferByte.Length)) > 0)
            {
                fileInputputStream.Write(bufferByte, 0, len);
                fileInputputStream.Flush();
            }
            fileInputputStream.Close();
            fileOutputStream.Close();

        }

        //download files from sub folders
        foreach (Microsoft.SharePoint.Client.Folder folder in subfolders)
        {
            //Remove the default folder "Forms"
            if (folder.Name == "Forms")
            {
                continue;
            }
            //create folder in local disk
            Directory.CreateDirectory(path+folder.Name);
            context.Load(folder.Files);
            context.ExecuteQuery();

            foreach (Microsoft.SharePoint.Client.File file in folder.Files)
            {
                FileInformation fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(context, file.ServerRelativeUrl);
                System.IO.Stream fileOutputStream = fileInfo.Stream;
                System.IO.Stream fileInputputStream = new FileStream(path+folder.Name + "\\" + file.Name, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                byte[] bufferByte = new byte[1024 * 100];

                int len = 0;
                while ((len = fileOutputStream.Read(bufferByte, 0, bufferByte.Length)) > 0)
                {
                    fileInputputStream.Write(bufferByte, 0, len);
                    fileInputputStream.Flush();
                }
                fileInputputStream.Close();
                fileOutputStream.Close();                  
            }
        }
    }

Screenshots of my test result: