I recently had a customer with the need to extract a .wsp (SharePoint Solution) File from the SharePoint Farm Solutions because the source file disappeared mysteriously…
As SharePoint loads the .wsp file into the config database (SharePoint_Config in most SharePoint environments – although it should be a name that you can recognize easily – you don’t name you files dok1, dok2, etc.) and stores it as binary content, it is not too hard to extract. When uploading the farm solution with Add-SPSolution or stsadm -o addsolution, the file is stored in the Binaries-Table of the SharePoint_Config Database.
As usual, be careful when querying the SharePoint Config/Content Database directly – this is an unsupported action.
What you get is the FileImage and a beautiful GUID representing the link to the object, which can be found in the Objects Table.
Now that you have your binary content and the original filename of your .wsp, there are a lot of scripts out there to extract the binary content. I modified one of the SQL Server Team Scripts (a quite sophisticated one that uses an ADODB.Stream) to meet my needs. Be sure to allow OLE Automation Procedures on your SQL Server, otherwise you will get lots of errors.
sp_configure‘show advanced options’, 1;
GO
RECONFIGURE;
EXEC sp_configure ‘Ole Automation Procedures’,1
reconfigure
GO
declare @ObjectToken INT
declare @TheSolution VARBINARY(MAX)
declare @Save2File VARCHAR(255)
declare @FileName varchar(255)
DECLARE TheCursor CURSOR FAST_FORWARD FOR
select fileimage, name from binaries, objects
where objects.id=binaries.objectid and name like ’%.wsp’ order by name
OPEN TheCursor
FETCH NEXT FROM TheCursor INTO @TheSolution, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Save2File = ‘c:\Temp' + @FileName
PRINT @Save2File
EXEC sp_OACreate ‘ADODB.Stream’, @ObjectToken OUTPUT – Creates an instance of an OLE object.
EXEC sp_OASetProperty @ObjectToken, ‘Type’, 1 – Sets a property of an OLE object to a new value.
EXEC sp_OAMethod @ObjectToken, ‘Open’ – Calls a method of an OLE object.
EXEC sp_OAMethod @ObjectToken, ‘Write’, NULL, @TheSolution
EXEC sp_OAMethod @ObjectToken, ‘SaveToFile’, NULL, @Save2File, 2
EXEC sp_OAMethod @ObjectToken, ‘Close’
EXEC sp_OADestroy @ObjectToken – Destroys a created OLE object.
FETCH NEXT FROM TheCursor INTO @TheSolution, @FileName
END
CLOSE TheCursor
DEALLOCATE TheCursor
Finally, your c:\temp directory contains all the .wsp files from your SharePoint Farm.
Markus
