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();
}
}
}
}
Good post. You should point out that Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll is required on the server running SSIS.
ReplyDeleteThis comment has been removed by the author.
ReplyDelete