Auto-create sites from .WSP in SharePoint 2010

[Andrzejek]

I am pretty new (and even that is an over statement…) to the SharePoint 2010 which is installed on SQL Server

I have been tasked with finding the way to set new sites (sub-sites?) in SharePoint 2010 based on a template created by somebody else. I understand that this template is a .WSP file. And the requirement is that this should be done with no user input/actions (no clicks, selections, choices, etc). What I mean by that is – there will be a job running at night and if certain Projects (from Oracle data base) meet some criteria, new sites in SharePoint 2010 should be created for those Projects (auto-magically) using the .WSP file as a template.

I already have the ‘main’ SharePoint site, and the Projects ‘sub-sites’ should be under the ‘main’ site.(that’s the terminology I use, but if that’s wrong, please correct me)

To my disposal I have (and can write the night job in):
Stored Procedures in Oracle PL/SQL
Visual Basic 6.0 (classic)
(Visual Studio) VB.NET 2008 or 2010
(anything else I should be looking at?)

Any help of where to start will be greatly appreciated.


Have fun.

---- Andy

 

[ehaze]

Can the Oracle process drop a file onto a network share?

If this can be done, you provide the Oracle process with a UNC path to a SharePoint library. (In a SharePoint library, click on Actions, then Open with Windows Explorer. This will provide you with a UNC path.)

You could then setup a SharePoint workflow that creates the new site based off the file that Oracle dropped into your library.

1. oracle exports a file to sharepoint named Taco.
2. sharepoint workflow fires, looks at the file name, then creates a site named Taco, and uses your .WSP template.


You would need this:

http://ilovesharepoint.codeplex.com/

Advanced Workflow Actions for SharePoint Designer 2010`
Actions; Create Site Action

the ilovesharepoint feature will add some new Actions to your workflow options. One of those options allows you to use a .WSP site template.

http://www.sharepointed.com/

 

[Andrzejek]

Can the Oracle process drop a file onto a network share?
[blue]If Oracle cannot, I can make it happen by some other way. That's not a problem[/blue]

If this can be done, you provide the Oracle process with a UNC path to a SharePoint library. (In a SharePoint library, click on Actions, then Open with Windows Explorer. This will provide you with a UNC path.)
[blue]Actions - do you mean 'Site Actions' at the upper-left corner? I have several options there, but no 'Open with Windows Explorer'. Unless there is some other 'Actions' that I do not know about - I am pretty new to it... [/blue]

You could then setup a SharePoint workflow that creates the new site based off the file that Oracle dropped into your library.
[blue]And what kind of 'file' are we talking about? Some text file? Again, I am very new at it... [/blue]

But - thank you for the information.


Have fun.

---- Andy

 

[ehaze]

Actions - do you mean 'Site Actions' at the upper-left corner? I have several options there, but no 'Open with Windows Explorer'. Unless there is some other 'Actions' that I do not know about - I am pretty new to it...

Nope, you would need to be in a Library.
example:

http://yourURL/sites/forms/Shared Documents/Forms/AllItems.aspx

And what kind of 'file' are we talking about? Some text file? Again, I am very new at it...

Text file will work.

We are trying to get a file into sharepoint for two reasons.
1. the file being added to the library will fire the workflow.
2. the name of the file can be used as the new site name. if you don't care about the site name, you can create a name using the workflow.

-------
other idea would be to use Access to interact with SharePoint to trigger the workflow.

have Access linked to an Oracle database.
run the Access database at a given time (use windows task).
if Access finds a new value in Oracle, write a value to a SharePoint List (use a little VBA and quires).
this would trigger a workflow in SharePoint.

http://www.sharepointed.com/

 

[Andrzejek]

This is what we have for now and it seams to work OK.

VB.NET 2010 (may work OK in 2008):

Imports System
Imports System.Data.OracleClient
Imports System.Threading
Imports Microsoft.SharePoint.Client
Module Module1
    Sub Main()
        Try
            CreateSite()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Thread.Sleep(5000)
        End Try
    End Sub
    Sub CreateSite()
        Dim connectionString As String = "Data Source=ABCD;" _
            & "Persist Security Info=True;User ID=MyID;Password=MyPasswd"
        Dim commandString As String = "SELECT * " _
            & " FROM MyTable " _
            & " WHERE Something = 'XYZ'"
        Dim siteNameString As String = "[URL unfurl="true"]http://sharepoint/ABCD/XYZ/MySite/"[/URL]

        Dim conn As New OracleConnection(connectionString)
        Dim comm As New OracleCommand(commandString, conn)
        Dim siteName As String = siteNameString
        Dim clientContext As New ClientContext(siteName)
        [green]
        ' Load properties of the web site.[/green]
        Dim site As Web = clientContext.Web
        clientContext.Load(site)
        clientContext.ExecuteQuery()

        conn.Open()
        Dim reader As OracleDataReader = comm.ExecuteReader

        While reader.Read()[green]
            'Stuff to put as Description on the site[/green]
            Dim description As String = "PROJECT: " & CStr(reader.Item("FieldA")) & vbCrLf _
                & "WHEN: " & CStr(reader.Item("FieldB")) & vbCrLf _
                & "WHO: " & CStr(reader.Item("Fc")) & vbCrLf _
                & "CITY: " & CStr(reader.Item("FD")) & vbCrLf _
                & "WHERE: " & CStr(reader.Item("FL")) & vbCrLf _
                & "DATE: " & CStr(reader.Item("FK"))

            Dim newSite = site.Webs.Add(New WebCreationInformation() With {
                .Title = CStr(reader.Item("FieldX")),
                .Description = description,
                .Url = CStr(reader.Item("SomeField")),
                .UseSamePermissionsAsParentSite = True,
                .WebTemplate = "STS#2"
            })

            clientContext.Load(newSite, Function(website) website.ServerRelativeUrl, Function(website) website.Created)
            clientContext.ExecuteQuery()
        End While

        reader.Close()
        conn.Close()
    End Sub
End Module

I did not go with ehaze's suggestion because I (well, nobody around here where I work) do not know how to set up the work flow to accomplish that.

Have fun.

---- Andy