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

        {

            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;

        }

Advertisements
  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: