Monday, August 1, 2011

Pulling SharePoint List using SSIS 2008

Do you want to pull SharePoint List data into SQL database using SSIS? If your answer is Yes then you can go for SharePoint List Source. You can download the setup from Microsoft SQL Server Community Sample: Integration Services: here is the link: SharePointListAdaptersSetup

This component has two controls - SharePoint List Source and SharePoint List Destination. The Specific Features of the included SSIS Components are:

SharePoint Source Component
  • Auto-detects all of the public fields from a SharePoint List or view of the list.
  • Supports removal of fields from the output flow, which optimizes the query against SharePoint for increased performance.
  • Supports restricting SharePoint data by a custom CAML query, which can be provided through a variable expression for fine tuned filtering over large lists.
  • Provides a tuning feature for the # of rows to return at a time. Often with SharePoint, large lists need to be brought over in smaller batches than wide (many field) lists, or else the Webservice will die.

SharePoint Destination Component

  • Auto-detects all of the editable fields from a SharePoint List
  • Only fields bound to columns from the input flow will be ‘updated’, for performance.
  • Supports deleting rows by ID

System Requirements
  • .Net 3.5 Framework
  • SQL Server Integration Services 2008

 How to Use
  • Download the sample from SharePointListAdaptersSetup
  • Run SharePointListAdaptersSetup.msi to install the SSIS component. This installer places pre-built binaries into the Global Assembly Cache (GAC) and into the C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents directory (if present).
  • A good way to explore the sample is to try using it, understand its behavior, and then begin reviewing the source code for the component. If you do not see the SharePoint adapters in your toolbox automatically, then follow these instructions:
    • Open Business Intelligence Development Studio, open a package, and then click Choose Toolbox Items on the Tools menu.
    • In the Choose Toolbox Items dialog box, click the SSIS Data Flow Items tab, and then check SharePoint Destination and SharePoint Source.
    • Click Ok.
  • The SharePoint source and destination components should now appear in the toolbox for the data flow task. You can add the source and destination components to the data flow of the package.

Tuesday, July 5, 2011

Table Partition

Table partitioning is a data organization scheme in which table data is divided across multiple data partitions or ranges according to values in a table column.

Benefits of table partitioning

There are numerous benefits of table partitioning:
  • To improve the scalability and manageability of large tables and tables in Database and Data Warehouse
  • Database and Data Warehouse that would benefit from easier roll-in and roll-out of table data
  • Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations.
  • A table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns.
 The steps for creating a partitioned table include the following:
  1. Create a partition function to specify how a table or index that uses the function can be partitioned.
  2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
  3. Create a table or index using the partition scheme.
 
Below are the steps to creation Horizontal Table Partition

-- Create the partition function

CREATE PARTITION FUNCTION PartitionFunctionMonthly (int)
AS RANGE RIGHT
FOR VALUES (20100101, 20100201, 20100301)
GO

-- Add the partition scheme
CREATE PARTITION SCHEME PartitionSchemaMonthly
AS PARTITION PartitionFunctionMonthly
ALL TO ( [PRIMARY] )
GO

 -- Create a simple table
CREATE TABLE PartitionTable (
   DateKey int NOT NULL,
   CustomerKey int NOT NULL,
   SalesAmt money,
CONSTRAINT PKPartitionTable PRIMARY KEY NONCLUSTERED
   (DateKey, CustomerKey)
)
ON PartitionSchemaMonthly(DateKey)
GO

------------------------------------
-- Unit Testing of Partitions
------------------------------------
-- Add some rows
INSERT INTO PartitionTable (DateKey, CustomerKey, SalesAmt)
SELECT 20091201, 1, 5000 UNION ALL
SELECT 20100101, 2, 3000 UNION ALL
SELECT 20100215, 7, 6000 UNION ALL
SELECT 20100331, 5, 3000 UNION ALL
SELECT 20100415, 8, 6000
GO

-- A query accesses the entire table, exactly as you'd expect.
SELECT * FROM PartitionTable
GO

--Query partition contents
SELECT
$partition.PartitionFunctionMonthly(DateKey) AS [Partition#],
COUNT(*) AS RowCount,
Min(DateKey) AS MinDate,
Max(DateKey) AS MaxDate
FROM PartitionTable
GROUP BY $partition.PartitionFunctionMonthly(DateKey)
ORDER BY [Partition#]
GO

Saturday, June 4, 2011

Moving Database Files From One Drive to Another

In practice, database files grows everyday. Sometimes it occupy the complete disk and we may end up in unsufficeint mamroy - leading to unexpected results.

In this blog, I'll explain how to transfer database files from one drive to another. To explain, I'll create a test database TestDB. Here is the command:

USE [master]
GO

IF DB_ID(N'TestDB') IS NULL
BEGIN
CREATE DATABASE [TestDB] ON PRIMARY
(
   NAME = N'TestDB'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.mdf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 1024KB
)
LOG ON
(
   NAME = N'TestDB_log'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.ldf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 10%
)
END
GO

Now check existing files location:

USE TestDB
GO
SELECT name, physical_name, state_desc
FROM TestDB.sys.master_files
WHERE database_id = DB_ID(N'TestDB')
 
 
 
 
 
 
 
 
 
Now the database is created, you can create some tables and enter some data into the database, if you wish, Otherwise proceed like this:
 
Step1: Make the database OFFLINE.

USE [master]
GO
ALTER DATABASE TestDB SET OFFLINE

Step2: Move file Physically
Now you need to move that file physically from existing folder to the new the location. Open the parent folder (Here 'C:\MSSQL\DATA') , You can see both mdf and ldf files', make sure that you cut the appropriate file, in this example it is the Log file. Cut that "TestDB_log.LDF" file and paste it on "D:\MSSQL\Data"
 
Step3: Update the system reference
Once you move the file physically , you need to update the system reference using the ALTER DATABASE .. MODIFY FILE Command as shown below:
 
ALTER DATABASE TestDB
MODIFY FILE (
   NAME ='TestDB_log'
   ,FILENAME = 'D:\MSSQL\Data\TestDB.LDF'
)

Step5 : Make database ONLINE
Last step is to make database online as shown below:

ALTER DATABASE TestDB SET ONLINE
GO

We are done. Now check existing files location using query mentioned above.