Commerce Server Profile Schema - Automating Import/Export with PowerShell Scripts

A common headache for any large team Commerce Server project is keeping each local developer’s workstation in synch with the schemas.  In the case of the Profile System, each developer must be mindful to export the profile schema using the Commerce Server Manager, check-in to source control, and then notify the team to import the new schema.  Each developer then uses the same MMC to manually import the schema, being sure to specify connection strings for their local profile databases. 

Wouldn’t it be nice to automate the export & import?!!  Here are a couple PowerShell scripts for exporting and importing the schema.  Note the export script uses BusinessDataAdmin2FreeThreaded.ExportCatalogs Method to store the credentials in the outputted XML.  This is not something available to the CS MMC.

To run these scripts, simply open your PowerShell command prompt and CD to the directory where you saved the scripts.  Of course, you’ll want to update the parameter values to reflect your CS Site Name.  Run .\ExportProfileSchema.ps1 and .\ImportProfileSchema.ps1

 

ExportProfileSchema.ps1

 # Commerce Server Site Name
 $siteName = "CSharpSite"
  
 # Export File Path 
 $filePath = "c:\ProfileSchema.xml"
 $user = ""
 $pwd = ""
  
 # If removeCredentials parameter is true, then the credentials (user name and password) will be removed from all connection strings. 
 # These credentials will have to be replaced # before the catalog can be imported. 
 # If the catalog is imported through Commerce Server Manager, the user must enter a user name and password for each data source partition in # the catalog. 
 # A value of true indicates remove the credentials. A value of false indicates do not remove the credentials. The default value is true. 
 # If the removeCredentials parameter is false, then the credentials will be exported in plain text with the rest of the catalog.
 # http://msdn.microsoft.com/en-us/library/microsoft.commerceserver.interop.profiles.businessdataadmin2freethreaded.exportcatalogs.aspx
 $removeCredentials = 0
  
 # Load Assemblies
 echo "Loading .NET Assemblies"
 [System.Reflection.Assembly]::Load("Microsoft.CommerceServer.Interop.Profiles.BizDataAdmin, Version=6.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35")
 [System.Reflection.Assembly]::Load("Microsoft.CommerceServer.Interop.Configuration.MSCSCfg, Version=6.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35")
 [System.Reflection.Assembly]::Load("ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
 [System.Reflection.Assembly]::Load("System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
  
 # Initialize SiteConfigReadOnlyFreeThreaded            
 echo "Initializing Commerce Server 2007 SiteConfigReadOnlyFreeThreaded Object"
 $siteConfig = new-object Microsoft.CommerceServer.Interop.Configuration.SiteConfigReadOnlyFreeThreaded
 $siteConfig.Initialize($siteName)
  
 # Get the Biz Data Store Connection String (OLEDB)
 $bdsConnect = $siteConfig.Fields.Item("Biz Data Service").Value.Fields.Item("s_BizDataStoreConnectionString").Value
 echo "CS Biz Data Store Connection String (OLEDB) $bdsConnect
 :"
  
 # Release underlying COM resources
 $siteConfig.Dispose()
  
 # Connect to Biz Data Store
 $bizDataAdmin = new-object Microsoft.CommerceServer.Interop.Profiles.BusinessDataAdmin2FreeThreaded
 echo "Connecting to Biz Data Store"
 $bizDataAdmin.Connect([ref] $bdsConnect, [ref] $user, [ref] $pwd)
  
 # Export to XML File
 echo "Exporting Profile Schema to " $filePath
 $bizDataAdmin.ExportCatalogs([ref] $filePath, [ref] $removeCredentials)
  
  
 # Release underlying COM resources
  
 $bizDataAdmin.Dispose()
  
 echo "Profile Schema Exported Successfully!"

 

ImportProfileSchema.ps1 

 # Commerce Server Site Name
 $siteName = "CSharpSite"
  
 # Import File Path 
 $filePath = "c:\ProfileSchema.xml"
 $user = ""
 $pwd = ""
  
 # Load Assemblies
 echo "Loading .NET Assemblies..."
 [System.Reflection.Assembly]::Load("Microsoft.CommerceServer.Interop.Profiles.BizDataAdmin, Version=6.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35")
 [System.Reflection.Assembly]::Load("Microsoft.CommerceServer.Interop.Configuration.MSCSCfg, Version=6.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35")
 [System.Reflection.Assembly]::Load("ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
 [System.Reflection.Assembly]::Load("System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
  
 # Initialize SiteConfigReadOnlyFreeThreaded            
 echo "Initializing Commerce Server 2007 SiteConfigReadOnlyFreeThreaded Object..."
 $siteConfig = new-object Microsoft.CommerceServer.Interop.Configuration.SiteConfigReadOnlyFreeThreaded
 $siteConfig.Initialize($siteName)
  
 # Get the Biz Data Store Connection String (OLEDB)
 $bdsConnect = $siteConfig.Fields.Item("Biz Data Service").Value.Fields.Item("s_BizDataStoreConnectionString").Value
 echo "CS Biz Data Store Connection String (OLEDB) $bdsConnect
 :"
  
 # Release underlying COM resources
 $siteConfig.Dispose()
  
 # Connect to Biz Data Store
 $bizDataAdmin = new-object Microsoft.CommerceServer.Interop.Profiles.BusinessDataAdmin2FreeThreaded
 echo "Connecting to Biz Data Store"
 $bizDataAdmin.Connect([ref] $bdsConnect, [ref] $user, [ref] $pwd)
  
 # Read XML into string
 $doc = new-object System.Xml.XmlDocument
 $doc.Load($filePath)
  
 # Import Profile Schema
 echo "Importing Profile Schema from " $filePath
 $bizDataAdmin.ImportCatalogs([ref] $doc.get_InnerXml())
 # Release underlying COM resources
 $bizDataAdmin.Dispose()
 echo "Profile Schema Imported Successfully!"

http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/CommunityServer-Components-PostAttachments/00-09-57-37-65/ExportImportSchema.jpg