George's BI Blog

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;

        }