Archive

Archive for September, 2009

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 5 (of 5)

September 24, 2009 Leave a comment

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;

        }

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 4 (of 5)

September 24, 2009 Leave a comment

Steps 3 – Use a “Execute SQL Task” to retrieve the list

I just use this: select * from dbo.SharePoint_FolderList

Steps 4 – Use a “For Each Loop” to loop through each foler

Steps 5 – Within the for each loop, use a script component to extract permission and write to the SQL table

Add the reference to the Microsoft.SharePoint and add the following in your code

using Microsoft.SharePoint;

using System.Data.SqlClient; //This is required for "SqlConnection" 

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

September 24, 2009 Leave a comment

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;

        }

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 2 (of 5)

September 24, 2009 Leave a comment

Basically, I create 2 tables: “SharePoint_FolderList” and “SharePoint_FolderPermissionList”.

CREATE TABLE [dbo].[SharePoint_FolderList](

      [SP_FolderName] [nvarchar](100) NOT NULL,

      [SP_FolderPath] [nvarchar](350) NOT NULL

)

 

CREATE TABLE [dbo].[SharePoint_FolderPermissionList](

      [SP_FolderName] [nvarchar](100) NOT NULL,

      [SP_FolderPath] [nvarchar](350) NOT NULL,

      [SP_FolderPermission] [nvarchar](100) NOT NULL

)

There are a few steps in the SSIS package:

Steps 1 – Truncate the above2 tables

Steps 2 – Write the folder list to the staging table (SharePoint_FolderList)

(Thanks to Shantha Kumar’s Retrieve all folders with sub-folders from SharePoint List. I took his code to retrieve folder list.)

As I run the package on MOSS Server locally, I use SPWeb. (If you want to run the package remotely to the MOSS Server, you have to modify my code to use the SharePoint web services. Check “Retrieving folders from document library”: http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/16a2d993-2f5e-4242-8e5a-451a78c064a3). In the script component, add the reference to “Microsoft.SharePoint” and add the following line: using Microsoft.SharePoint;

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 1 (of 5)

September 24, 2009 Leave a comment

This article applies to:

  • SharePoint 2007 SP1
  • Microsoft SQL Server 2008 SP1

In my recent BI project, we use SharePoint 2007 as the report publishing platform. We have a document library which has 200+ folders (and sub folders) within it. Each folder has its own customized security applied (break the security inheritance from its parent folder.)

Yesterday, the client wants me to extract the information about what permissions/roles had been assigned to each folder for audit purpose.

I’ve developed the following SSIS package to automatically loop through each folder and extract its permission information and write to a SQL database table.

FW: Weighted Average: from requirements to implementation

September 22, 2009 Leave a comment
Categories: SQL Analysis Services

Thoughts Regarding “Kimball University: Five Alternatives for Better Employee Dimension Modeling”

September 7, 2009 Leave a comment

Today I read the article "Kimball University: Five Alternatives for Better Employee Dimension Modeling"

The basic employee dimension structure is quite common. However my personal opinion is that in most cases, employees change a lot while roles (company structure) are relatively stable. Hence, I prefer at the DW layer, separate the employees from the roles and make roles a self-referencing table. e.g. DimEmployee, DimRoles (or even DimEmployee, BridgeEmployeeRoles,Dimroles if you need many to many relationships). By doing this, when an SCD type 2 update from the top to bottom (let’s say CEO is changed) happens, you don’t have to replicate the whole tree. As even the person of CEO is changed, the position is not changed. People below CEO now is still under CEO. The only thing changed is that now the CEO is not XXX. He is YYY at the moment.

By the way, the Alternative 5: The PathString Attribute is quite interesting.

Categories: SQL Analysis Services