Archive for the ‘SQL Integration Services’ Category

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




                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;


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”: 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.

SSIS Checkpoint

February 19, 2009 Leave a comment

You can set
package transactions at the entire package level or at any control flow
container level or task level. Windows Distributed Transaction Coordinator
(DTC) services needs to be started on the machine for transactions to work.

SSIS Package
Restartability: To use Package Restartability, in addition to set checkpoint
settings, you also need to set FailPackageOnFailure
to True for each task or container within the package. However, if you want to rerun
any successful tasks that occur before the failure task, you need to use a
sequence container around the group of related tasks that require transactions.

Sometimes, a
series of tasks will need to be rolled back if one of the tasks fails, but you
only want to restart from where it fails. To do this, enable checkpoints in the package level, then
use a Sequence Container that holds
all the associated tasks. The Sequence Container needs to have the transactions
turned on by setting the TransactionOption
to Required at the container level.

To Check DTC: Control Panel à Administrative Tools à
Services Console
Distributed Transaction Coordinator Service.

To specify a
task should fail intentionally, set ForceExecutionResult
to Failure. This is very useful in
development stage. 

SSIS event
handlers assigned to an executable scope will propagate down to child events.
E.g. if you assign an OnError even to the package and an error even occurs at
the task, the OnError event handler would file for both the task and the
package and for any containers in between.

error information with the OnError event such as ErrorCode, ErrorDescription and SourceName (the task) etc. In
addition, they can used to audit the execution and the event handler Send Mail Task can be used for

Event handlers
can be turned off for any task or
container by setting the DisableEventHandlers
property of the Task or Container to True. In other words, if you have an even
handler defined, but you specifically do not want it to be invoked for a
specific task, then you can turn off event handlers for that task only.

the commit level is configured on a table-by-table basis, all the data flow and
control flow tasks that operate on a single task need to be grouped together in
a container, and the TransactionOption must be set to
Required” for each container. You
should also implement checkpoints on the containers, which will let you restart
the packages at the point of failure after you have resolved any problems. You
can simplify this implementation by creating a master package that has
checkpoints turned on and that uses the Execute Package Task to call child
packages for each destination table that has transactions enabled.