If you are wanting to populate a Sharepoint list from an SQL database or populate a SQL database from a Sharepoint list you can do this failry easily using SSIS & the SharePoint List Source and Destination Adapter project on Codeplex.

Introduction
This sample illustrates the creation of a a source and destination adapter in C#, using the public APIs for Microsoft SQL Server Integration Services. The component connects to SharePoint through a proxy dll, which wraps the complexities of working with the SharePoint Lists Webservice.

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. The batchsize lets you specify a solid size that works for your list.

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
Included Bonus – SharePoint Utility

To simplify the adapter code and keep the SharePoint routines generic, a proxy VB.Net component is included which access the SharePoint Webservice API. It includes a very flexible api for getting SharePoint List information, which is used by the Components above. In addition, it can publish and remove files from a document library.

Component Features
Unique features that may inspire you to create great custom SSIS components:
• Extensive Validation: Validation for this component actively goes against the SharePoint Site to verify the properties are valid.
• Values from Expressions: This component supports external variables, which can be expressions, and can be attached to the source component to customize the query. Similar to the CommandText for the other Sql Components
• Linq: The Component has been written using Linq with .net 3.5 and shows how elements such as the metadata and columns can be combined to create a readable usage in a Linq format.
• Custom properties: The component keeps its configuration in custom properties on itself, inputs, and input columns.
• SharePointUtility Component: Simplifies access to the SharePoint Lists / Views WS using Linq friendly objects

Sharepoint List Source & Destination project on Codeplex

In Sharepoint 2010 when you are returning rows from an external datasource using External Content Types & External Lists the defaul throttling is set to 2000 rows.

If your datasource is trying to return more than this you will get an error on you external list saying this webpart cannot be displayed etc.

If you have logging turned on for Business Connectivity Services in Central Admin -> Monitoring -> Diagnostic Logging you will see errors similar to this in your Sharepoint Logs:

Error while executing web part: Microsoft.BusinessData.Runtime.ExceededLimitException: Database Connector has throttled the response. The response from database contains more than ’2000′ rows. The maximum number of rows that can be read through Database Connector is ’2000′.

You can change these throttle limits using the following powershell script:

$bdcProxy = Get-SPServiceApplicationProxy | where {$_.GetType().FullName -eq (‘Microsoft.SharePoint.BusinessData.SharedService.’ + ‘BdcServiceApplicationProxy’)}

$dbRule = Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $bdcProxy

Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Maximum 5000 -Default 5000

If you have your Sharepoint farm hosted in a different domain to your users you will notice that the people picker only looks at its local domain, ie does not list any users from the Internal / User domain. Luckily you can get round this fairly easily:

Run this command on all Sharepoint servers in your farm:

stsadm -o setapppassword -password <AnyPasswordYouWant>

Then run this commmand for each web app:

stsadm.exe -o setproperty -url <YourWebApp> -pn "peoplepicker-searchadforests" -pv "domain:<YourDomain>,<Domain Account>,<password>"

You should now be good to go & pull people & resolve names from both domains.

For example you are running Sharepoint 2010 in the a 1 way trust environment & you want to add a new managed account to your Sharepoint farm from your internal domain. This should be fairly simple by going Central Admin –> Security –> Configure Managed Account –> Register Managed Account

User name: domain\accountname

Password: *********

However no matter what I tried doing this in cental admin always returned the error:

An error occurred while getting information about the user at server : Access is denied

To get round this you need to add the account using powershell.

- Log onto you server using an account from your internal domain (you need to do this so it can retrieve the info it needs from this domain)

- In powershell

$cred = Get-Credential

Now a login prompt will pop up, enter the managed account details here you are wishing to add domain\username, enter password & click OK

- Now enter

New-SPManagedAccount –Credential $cred

Your managed account will now be added, you can check this in central admin or powershell using

Get-SPManagedAccount

When deleting & recreating Sharepoint service applications you sometimes get old application pools showing in your drop downs. To delete these orphaned application pools:

Remove-SPServiceApplicationPool -Identity "Orphaned SharePoint Service Application Pool Name"

To list you application pools:

Get-SPServiceApplicationPool | select Id, Name

When trying to delete a service application in Sharepoint 2010 from the central admin, sometimes you will find it just stays on the ‘processing’ window for hours (if you wait that long).

If it does you can try & delete the service application using Powershell:

Remove-SPServiceApplication

If that command doesnt work you can force the deletion by using STSADM:

STSADM -o deleteconfigurationobject -id {ID}

The above STSADM command is not documented by Microsoft (or doesnt seem to be)

When applying updates to your Sharepoint 2010 farm you may get the following error in the Sharepoint Configuration Wizard:

An exception of type System.ArgumentException was thrown. Additional exception information: Keyword not supported ‘failoverpartner’.

This means youve got mirroring enabled on your Sharepoint databases and Sharepoint CU updates dont install on mirrored databases.

To find out what sharepoint databases are mirrored/failover you can use the following powershell command:

get-spdatabase | select name, failoverserver

You can turn off the failover for you content databases in central admin aswell as your service application databases. However all other databases need to be turned on & off using powershell.

To Turn Off Mirroring /Failover on your database:

$database = Get-SPDatabase | where { $_.Name -eq "ASharePointDB" }
$database.AddFailoverServiceInstance("")
$database.Update();

To Turn On Mirroring /Failover on your database:

$database = Get-SPDatabase | where { $_.Name -eq "ASharePointDB" }
$database.AddFailoverServiceInstance("FailoverSQLServerName\OptionalInstanceIfUsed")
$database.Update();

Had a strange problem when creating a new Secure Store Service Application, the wizard would complete but when you click ‘manage’ you would get an error saying –

‘Cannot complete this action as the Secure Store Shared Service is not responding. Please contact your administrator’

After looking at the event logs it was failing with an error:

Cannot open database “Secure_Store_Service_DB_edb5686a-ff41-4457-903a-4ff09xxxxxx” requested by the login. The login failed. Login failed for user ‘XXXXXXX\FarmAcct’.

After lots of head scratching I notice that the db the Secure Store Service App wizard was creating in Central Admin was a different database to the one giving the connection error in my event viewer.

I tried to create a new service app & renamed the DB to the name that it was trying to connect to but I would then get an error message saying this database already existed, even though it didnt exist in SQL Management Studio.

It was trying to connect to an orphaned secure store service DB which must have been created at an earlier time, which I verified by looking here:

http://CentralAdminURL/_admin/DatabaseStatus.aspx

To cut a long story short you can use this powershell script to remove any orphaned Sharepoint 2010 Service Application databases

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq
'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null) {
 Write-Host "Loading SharePoint Powershell Snapin"
 Add-PSSnapin "Microsoft.SharePoint.Powershell"
}
Get-SPDatabase | Where{$_.Exists -eq $false} | ForEach {$_.Delete()}

I found the script at http://www.sharepointusecases.com/index.php/2010/12/how-to-remove-renamed-sharepoint-2010-service-app-databases/

More From pstilgoe

• How to delete the Shared Service Provider in MOSS 2007
• Sharepoint 2010: How to hide the quick launch menu
• Sharepoint 2010 CQWP: Make the CQWP portable by using tokens to point to the current site instead of static URL

Ask pstilgoe To Recommend Your Posts

Document Sets in Sharepoint 2010 – Limitations

- Document Sets are only available in SharePoint 2010 Server and are not part of the SharePoint Foundation 2010. This also means that the programing object model (SPFolder) is part of the SharePoint 2010 Server API and not available in the Foundation OM.

- You cannot create a Folder within a Document Set. Beware that the UI will not prevent you from doing this, but folders are not supported and likely will not work as you think they should.

- You cannot create metadata navigation in a Document Set.

- Since Document Set is being treated as a Folder, which is treated as such when you deal with operations like Record Management and File Upload. It’s a little bit inconsistent from the document-centric point of view.

- You cannot send a Document Set larger than 50MB using the Send To command.

- In general, beware when you create and use very large Document Sets (thousands of items), as the limitation and performance factor of viewing and displaying the list will be a factor.

- Download the entire document set. This is a very useful feature that did not make it into this release of SharePoint 2010. If you select the entire document set, you will notice that the “Download a Copy” button is grayed out. Fortunately, there are instructions available to add a custom button in the ribbon to add this feature.

Source: Bamboo Solutions

When using Sharepoint Designer 2010 to create custom aspx list forms I was getting the error:

‘sharepoint designer could not save the list changes to the server’

I had a look on the net but couldnt really find anything relating to do with this.

I seem to have solved it by going to my list and removing duplicate column names that werent being used ie.

Consultant

Consultant #Used in CT ‘Consultant’#

I was using a custom content type so I deleted all the columns that weren’t ‘Used by’ my custom content type. Once I had done this I was them able to create custom forms as expected in SPD 2010.

Hopefully this will help some people if theyre getting similar problems!