SharePoint List and SSIS SQL Server (Retrieving data from SahrePoint List)

Retrieving Data from SharePoint List using SQL Server SSIS

1. Create a Test Package in SSIS
2. Create a Data flow Task and rename it to FromSharePointList
3. Double click the task and open the Data Flow
4. Add SharePointListSource from Tool box
5. Right click on the item and click edit
6. Add the Site URL: http://Vijay// (Ex: This is the name of my URL http://Vijay/Lists/EmpContactAdvWorks/AllItems.aspx)
7. Add the Site List Name: Name of the List you want to copy to a Flat-File (Ex:This is the name of my list EmpContactAdvWorks)
8. Go to Column Mapping and <Refresh> and Click OK
9. Now add a Flat-File destination and double click to configure
10. Create a new Flat File destination and Map the fields
11. Run the Package
12. Screen Shot follows:

SharePoint List and SSIS SQL Server (Loading data into SahrePoint List)

Create a Test Package in SSIS
Create a Data flow Task and rename it to ToSharePointList

Using SQL Server SSIS for Loading data into SharePoint List

1. Double click the task and open the Data Flow
2. Add a Flat File Source and Configure (This files should have compatible fields as per your SharePoint List)
3. Add SharePointListDestination from Tool box
4. Right click on the item and click edit
5. Add the Site URL: http://Vijay// (Ex: This is the name of my URL http://Vijay/Lists/EmpContactAdvWorks/AllItems.aspx)
6. Add the Site List Name: Name of the List you want to copy to a Flat-File (Ex: This is the name of my list EmpContactAdvWorks)
7. Go to Column Mapping and <Refresh> and Click OK
8. Execute the package
9. Go to the SharePoint site and check the list for the data
10. Screen Shot follows:

SharePoint List and SSIS SQL Server

Requirement:

Loading and Extracting data from SharePoint List and SQL Server SSIS packages.

1. The first and the foremost thing required is to download the .msi from codeplex called SharePoint List Source and Destination Sample to add the components to the SSIS Tool box.
2. Click the CodePlex Link: http://sqlsrvintegrationsrv.codeplex.com/
3. Click Download
4. Once the download is completed:
5. Open BIDS
6. Go to the Data Flow of any package
7. Open Toolbox (If Toolbox is not open Go to VIEW at the top and from the drop down select Toolbox)
8. Right click on DATA FLOW SOURCES -> CHOOSE ITEMS -> Click on SSIS Data Flow Items -> Scroll down and Select  SHAREPOINT LIST SOURCE -> Click OK
9. Now you will be able to see the SHAREPOINT LIST SOURCE on DATA FLOW SOURCES
10. Perform steps 7-9 for DATA FLOW DESTINATION
11. Added Screen shots for Clarity

VisualStudio & SharePoint Deployment options

Visual Studio 2010 Default deployment option and Upgrade
Problem :
Visual Studio 2010 Deployment: Error occurred in deployment step 'Activate Features': A list, survey, discussion board, or document library with the specified title already exists in this Web site.  Please choose another title.
Reason:
In Visual Studio 2010 by default the deployment configuration comes with the following:

•Default

•No Activation

•Upgrade (option is not available)

Solution:
The following download will allow you to add the upgrade options in VS 2010
Download it from http://vs2010spupgrade.codeplex.com/.
Installation Instructions

1. Simply unzip.

2. Double-click on VSIX file to install.

SharePoint DashBoard Size settings

Problem: When a Dashboard is published to the SharePoint environment different users have a different size view of the Dashboard due to different types of display units and configuration.
Solution: The problem can be fixed by setting the Auto-Size feature on the Dashboard using PerformancePoint Dashboard Designer.
Steps:

1. Open Performance Dashboard designer
2. Select the Report from the Dash-Board
3. on the Edit tab, in the Layout group, click Edit Item. The Item Settings dialog box opens.
4. On the Size tab, in the Width section, select Auto-size width.
5. On the Size tab, in the Height section, select Auto-size height, and then click OK.

1. Repeat steps for each item that you want to include in the dashboard.
2. In the Workspace Browser, right-click the dashboard, and then click Save.

SharePoint 2010 Web Application, Site Collection & Content Database

Web Application, Site Collection and Content Database
A. If you are planning for multiple Site Collection per Web Application you can either go with:

a. Single content database for all site collections with in the Web Application or

b. You can add 1 content database per Site collection.

B. By default a single content database is used per Web Application and multiple Site collections share the same content database between site collections.

a. In the long run this may lead to size issues if there are multiple site collection with in the Web Application and the content of each Site collection increase.

C. Scenarios:

a. One Web Application can have multiple Site collections or Single Site collection.

b. Each Site Collection can have its own Content Database. 1 Site Collection = 1 Content Database.

c. Each content database can hold multiple site collection. 1 Content Database = 2/3/4 Site Collections.

D. Advantages of 1 Site Collection = 1 Content Database

a. Improves performance

b. Easy backup or recovery

c. Copying data to another farm. Example: Different Environments(Test->Prod)

How to add Multiple Content Databases per Web Application?
I. Example: In a farm the default Web Application at PORT:80 will have its default Content Database for all the site collections underneath.
a. Now you want to create a Site Collection but you want to have a new Content database for this Site Collection
b. When you create a Site collection SharePoint utilizes the default database.
c. So the first step is to set the Status of the default database to STOP/OFFLINE.

i. GO to Central Admin -> Click on Application Management -> Databases (Click on Manage Content Databases). Now you will be able to see the current Web Application in the right hand corner. At this point you can change the Web Application if you have multiple Web Applications in the farm by clicking the down arrow.

ii. If you are already on the right Web APP Check the status of the default database. Most probably the status will be STARTED.

iii. To set the database STATUS to offline double click on the Database Name.

iv. In the Database Section change the Database Status to OFFLINE and Click OK.

v. Now the Database Status will be set to STOPPED.

d. Create a new Database.

i. GO to Central Admin -> Click on Application Management -> Databases (Click on Manage Content Databases). Click on Add a content Database.

ii. In the Web Application section, check whether you are on the right Web Application. If not click the down arrow and change the Web Application.

iii. In the Database Name and Authentication. Change the Database name or leave the default name as it is.

iv. In the Search Server select the Server and Click OK at the bottom.

v. This will take you back to the Manage Content Databases and you will be able to see the created database status as STARTED and CURRENT NO. OF SITE COLLECTIONS as 0. Now you are ready to create a new Site Collection.

e. Create a Site Collection to use the new Content Database created.

i. GO to Central Admin -> Click on Application Management -> Site Collections (Click on Create Site collection).

ii. Once again check the Web Application (Change if you are not on the right one).

iii. Create a Title, Web Site Address, Template and Primary Site Administrator

iv. Click OK to create the new Site Collection.

v. Now go back Central Admin -> Click on Application Management -> Databases (Click on Manage Content Databases). You will notice number Site collections changed to 1.

vi. Now if you create another site collection the number will increase to 2 on the same content database. This means now you will have multiple site collection with in the same content database.

f. Now restart the stopped content database and you are done.