How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 3 (of 5)
Now you can add the code. Below is the sample code I used.
public void Main()
{
try
{
string siteURL = Dts.Variables["User::siteURL"].Value.ToString();
string DealershipReportsPath=Dts.Variables["User::DealershipReportsPath"].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())
{
SPList list = web.Lists[DealershipReportsPath];
SPQuery query = new SPQuery();
//Condition to check the item type is folder or not
query.Query = "<Where><Eq><FieldRef Name=’FSObjType’/><Value Type=’Lookup’>1</Value></Eq></Where>";
//Get all the items including subfolders from the list
//query.ViewAttributes = "Scope=’RecursiveAll’";
/*if you uncomment the above line,it will traverse all sub folders. At the
moment, it DOES NOT go to sub folders.*/
//Retrieve the items based on Query
SPListItemCollection items = list.GetItems(query);
//Get the name and Url for the folder
foreach (SPListItem item in items)
{
SqlCommand myInsertCMD = connection.CreateCommand();
myInsertCMD.CommandText = "insert into dbo.SharePoint_FolderList (SP_FolderName, SP_FolderPath) values(‘" + item.Name + "’, ‘" + web.Url + "/" + item.Url + "’)";
myInsertCMD.ExecuteNonQuery();
myInsertCMD.Dispose();
}
}
connection.Close();
connection.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "—-"+ ex.StackTrace);
}
Dts.TaskResult = (int)ScriptResults.Success;
}