Retrieving data from a list in SharePoint Online requires a slightly different implementation than SharePoint on-prem.  The following example uses C# and CSOM to retrieve data from a SharePoint Online list in an SSIS dataflow script component. 

1.  First you’ll need to add two references to the SSIS Script Component Visual Studio project.  To add a reference right click references and select ‘Add Reference’.

Click the .NET tab and then add the following two reference to your project:

• Microsoft.SharePoint.Client
• Microsoft.SharePoint.Client.Runtime

2.  Update the Namespaces region so it is similar to the following:

#region Namespaces  
using System;  
using System.Data;  
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;  
using Microsoft.SqlServer.Dts.Runtime.Wrapper;  
using Microsoft.SharePoint;  
using Microsoft.SharePoint.Client;  
using System.Security;  
#endregion

Note: most of the namespaces are already included by default, the bottom three were added for this implementation.

3.  Add the following variables to the class definition:

  private SecureString secureStr;
  private ClientContext cc;
  private List spList;
  private String query = "<View><Query><OrderBy><FieldRef Name=&#8217;ID&#8217;/></OrderBy></Query></View>";

The query variable stores the Caml query that will be used to retrieve all fields for all items in the list and sorts them by ID.  More information regarding Caml can be found here.

3.  When connecting to a list in SharePoint Online, you need to use the class SharePointOnlineCredentials to set the user and password credentials.  The Client Context class is used to establish a connection to SharePoint.  The following code is from the method called CreateNewOutputRows().

Some helpful notes:

• Variable.SharepointURL is a URL to the SharePoint site collection where the list resides.  Something similar to https:///sites/.
• The name of the SharePoint list being referenced is “Benchmark Category”.
• The SecureString class must be used for SharePoint Online Credentials.  cc.Credentials = new SharePointOnlineCredentials(User.SharePointUser, secureStr).
• When iterating through the items in the list, I am referencing each item based on the key associated to the list field.  In the SharePoint list we have a field with the name ‘Product Category’, but the key associated to that field is ‘Title’; therefore, I am referencing this field using item[“Title”].  We also have a field that is displayed as ‘Benchmark Category’ and the key for this field is ‘Benchmark_x0020_Category’.
• The ‘Benchmark Category’ field is defined as type ‘Choice’ in SharePoint (users can select from a list of items in a drop-down); therefore, I needed to cast item[“Benchmark_x0020_Category”] to a String array and then retrieve the item at the first index (0).