Archive for the ‘SQL Integration Services’ Category

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.