How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 5 (of 5)
The below is my sample code:
public void Main()
{
try
{
string siteURL = Dts.Variables["User::siteURL"].Value.ToString();
string DealershipReportsPath = Dts.Variables["User::DealershipReportsPath"].Value.ToString();
string SP_FolderName = Dts.Variables["User::SP_FolderName"].Value.ToString();
string SP_FolderPath = Dts.Variables["User::SP_FolderPath"].Value.ToString();
string Server = ""<Your Server>";
string Database = ""<Your Database>";
string ConnectionString = "Data Source=" + Server + ";";
ConnectionString += "Initial Catalog=" + Database + ";";
ConnectionString += "Integrated Security=SSPI";
SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConnectionString;
connection.Open();
SPSite site = new SPSite(siteURL);
using (SPWeb web = site.OpenWeb())
{
SPFolder currentFolder = web.GetFolder(SP_FolderPath);
//MessageBox.Show("currentFolder= " + currentFolder.Name);
for (int i = currentFolder.Item.RoleAssignments.Count – 1; i >= 0; i–)
{
SqlCommand myInsertCMD = connection.CreateCommand();
myInsertCMD.CommandText = "insert into dbo.SharePoint_FolderPermissionList (SP_FolderName, SP_FolderPath, SP_FolderPermission) values(‘" + SP_FolderName + "’, ‘" + SP_FolderPath + "’, ‘"+ currentFolder.Item.RoleAssignments[i].Member.Name.ToString() + "’)";
myInsertCMD.ExecuteNonQuery();
myInsertCMD.Dispose();
}
}
connection.Close();
connection.Dispose();
}
catch (Exception)
{
//MessageBox.Show(ex.Message + "—-"+ ex.StackTrace);
}
Dts.TaskResult = (int)ScriptResults.Success;
}