Wednesday, 5 June 2013

Download file from sharepoint document library using SSIS

For downloading a file from sharepoint Document Library in SSIS.
Use following C#.Net Method inside Script Task Control.

  public void DownloadExcelFromSP()
        {
          
          
                string SiteURL = Dts.Variables["User::SiteURL"].Value.ToString();
                string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString();
                string ImpersonateUserDomain = Dts.Variables["User::ImpersonateUserDomain"].Value.ToString();
                string ImpersonateUserID = Dts.Variables["User::ImpersonateUserID"].Value.ToString();
                string ImpersonateUserPWD = Dts.Variables["User::ImpersonateUserPWD"].Value.ToString();
                string SPListName = Dts.Variables["User::SPListName"].Value.ToString();
                string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();

             

                Microsoft.SharePoint.Client.ClientContext clientContext = new ClientContext(SiteURL);
                clientContext.Credentials = new NetworkCredential(ImpersonateUserID, ImpersonateUserPWD, ImpersonateUserDomain);
             
                Microsoft.SharePoint.Client.List list = clientContext.Web.Lists.GetByTitle(SPListName);
                CamlQuery camlQuery = new CamlQuery();
              

                camlQuery.ViewXml = @"<View Scope='Recursive'></View>";
                Microsoft.SharePoint.Client.ListItemCollection items = list.GetItems(camlQuery);
                clientContext.Load(
                    items,
                    i => i.Include(
                        item => item.File,
                        item => item["FileRef"]));
                clientContext.ExecuteQuery();

                foreach (Microsoft.SharePoint.Client.ListItem item in items)
                {                

                    if (item.File.Name == ExcelFileName)
                    {
                      
                        // download file from sharepoint
                        FileInformation fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, item["FileRef"].ToString());
                        using (Stream file = System.IO.File.Create(FolderPath + item.File.Name))
                        {

                            int b = fileInfo.Stream.ReadByte();
                            while ((b) != -1)
                            {
                                file.WriteByte((byte)b);
                                b = fileInfo.Stream.ReadByte();
                            }
                            file.Close();
                            file.Dispose();
                        }
                    }
                }

            }

2 comments:

  1. Good post. You should point out that Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll is required on the server running SSIS.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete