Home > SQL Integration Services > How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 3 (of 5)

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()




                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;



                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 + "’)";










            catch (Exception ex)


                MessageBox.Show(ex.Message + "—-"+ ex.StackTrace);



            Dts.TaskResult = (int)ScriptResults.Success;


  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: