This post will be divided into two parts. The first one is written by me and will describe the scenario, the problem to solve and how to prepare the setup in SharePoint. The other part will be written by Niklas Goude and describes the solution made with PowerShell from a more detailed and technical point of view. The full script is available for download from from mine or Niklas Download section.

Let’s start off with the scenario. You are about to move a large amount of customer data in to your MOSS environment. To structure it you have decided to create a site for each customer and store all relevant information about the customer in each site. The problem you face is that in SAP you currently having 700+ customers and if you should create all of them one by one, you will probably need to pass the task on to your grandchildren before it’s finished.

What you also want to do is to add all the new sites to your Site Directory and use your custom Site Template that you have created.

So how do we do this? The answer is spelled PowerShell! But before we hand over to Niklas and look at the code we will take a look at the prerequisites. 

Site Directory
First of all we need to make sure that we have a Site Directory. The Site directory is a separate site that ships with MOSS and helps you sort and categorize your sites within a Site Collection. If you have created your Site Collection based on one of the publishing site templates you will already have the Site Directory. If not, you can create the Site Directory from the Create Site and you find the Site Template under the Enterprise section on the create new site page. The sites created in the Site Directory is actually just a simple SharePoint List. So you can add your own columns with your own metadata values which makes it possible to sort the sites on your metadata. You can also, as in our case, add a Business Data Column pointing back to SAP so that you can present SAP information in a standardized way on each customer site.

Click to see a larger picture

Site Template
In our case we want to use our own Custom Site Template, this is of course not necessary from a technical point of view but when structuring this amount of data you need to put a lot of effort in making a good Site Template that includes everything you need. Make sure that the document library has the right content types, Versioning Settings etc. because that is not something you want to do afterwards.

 
Click to se a larger picture

List of sites
To be able to create the 700+ sites we of course need some kind of source data. In our case we were able to get this list from SAP. We got an Excel worksheet that we exported from Excel to a CSV file. What we also need to take care of is that not all of the customers have URL friendly names. This could cause problems in the batch script but instead of modifying the list we got, we decided to move this in to the script and let PowerShell take care of that.

So, let’s move over to www.powershell.nu, stage is yours Niklas.