SSIS | Read & Update Sharepoint File List using SSIS

This blog will cover the following items

• Read a SharePoint document Lists (in my case it is a document library and has custom columns) 
• And get a handle to each file available ( which can be used to import the data from the file)
• Use CAMLQuery specific files only.
• Update value back to Sharepoint file record.

1.  the SSIS- sharepoint  adapater from CodePlex – (SharePointListAdaptersSetup.msi) - https://sqlsrvintegrationsrv.codeplex.com/releases/view/17652
2. Create a new SSIS Project and create a new “data flow Task” in “control Flow” screen and go to the “Data Flow” screen.
3. 
   
   
4.  In “Data flow” screen you should see the below components. If not then something has gone wrong at step-1. Please fix that first. Also add the "SharePointList source" and SharePointList Destination" to the Data Flow
5. 
   
   
6. Create a new "connection", by right clicking on "Connection Manager". 
7. Select the Connection Manager for Sharepoint. 
   
8. Now open the properties of "SharePointList Source" and add the sharepoint connection Manager details.
9. Now go to the "Component Properties" and set the properties as required. 
    SiteUrl = <sharepoint URL>(dont include the site name here ) eg: http://<SharepointServer>sites/  SiteListName = Enter the siteName eg. TestSite
    Includefolder = ( if you want read folders and files then set this to true
    IsRecursive = ( if want to read files from subfolders)
10. CAML QueryIf you want to select only selected files from your sharepoint List, then this is the property you can play with.
    for eg. if you want to select files only from specific folder, your CAML query will be like this.

<query>
   <where> 
      <and> 
          <contains> 
             <fieldref name="FileDirRef">
                  <value type="Text">/FolderName</value>
             </fieldref>
          </contains>
     </and>
  </where>
</query>

11. CAML Query
    If there are more than one condition then use it like this

<query>
   <where> 
      <and> 
          <contains> 
             <fieldref name="FileDirRef">
                  <value type="Text">/FolderName</value>
             </fieldref>
          </contains>
         <Eq>
           <FieldRef Name='<custom column Name>' />
               <Value Type='Text'><custom column Value></Value>
        </Eq>      
     </and>
  </where>
</query>

12. The final SSIS job looks like this