INFO: CS2K: How to create a Profile Data Population Script (324506)

MORE INFORMATION

• Use the SQL Server Import and Export Data wizard.
  1. Click Start, point to Program Files, point to Microsoft SQL Server, and then click Import and Export Data.
  2. The introduction screen for the DTS Import/Export Wizard appears. Click Next.
  3. For Data Source, verify that Microsoft OLE DB provider for SQL Server is selected. Configure the source Server name and the Authentication information appropriately, and then select the correct source Database name (such as Retail_Commerce). Click Next.
  4. For Destination, verify that Microsoft OLE DB provider for SQL Server is selected. Configure the destination Server name and the Authentication information appropriately, and then select the correct destination Database name. (A database that has empty tables already exists; it is created when you unpack your Commerce Site from a PuP file.) Click Next.
  5. Leave the default option Copy table(s) and view(s) from the source database selected. Click Next.
  6. Click to select the check boxes for Addresses, UserObject, BlanketPOs, OrganizationObject, and any custom tables that were included in the Profile Definition Schema Script when you unpacked the Commerce Web site to the destination server. Click Next.
  7. Verify that Run immediately is selected, and then click Next.
  8. A summary of what is about to occur appears. Verify that the information is correct, and then click Finished.
  9. The Executing Package window appears, which shows the progress for each step in the DTS package. When the dialog box appears that says Successfully copied table(s) from Microsoft SQL Server to Microsoft SQL Server, the Profile Data Migration is completed. Click Done.
• Create a SQL script for PuP.exe to use that is based on C:\Program Files\Microsoft Commerce Server\PopulateProfileSQL.sql.
  The following is a sample script for the UserObject table that writes a SQL script that you can use with PuP.exe:
  1. Create a file named ExportDataToSQL.vbs (or any other name that has a VBS file name extension), and then paste the following sample code in this file.
     
     Note This script exports only the UserObject table. To export other tables, change the SELECT statement accordingly. When you have exported all the tables that you want by using this method, you can combine them in a single SQL script.

     Option Explicit
     Dim SqlUserName: SqlUserName = "sa"
     Dim SqlPassword: SqlPassword = "password"
     Dim SqlServer: SqlServer = "NetBIOS Computer name or IP"
     Dim SqlDatabase: SqlDatabase = "Retail_Commerce"
     
     '---- CursorTypeEnum Values ----
     Const adOpenForwardOnly = 0
     Const adOpenKeyset = 1
     Const adOpenDynamic = 2
     Const adOpenStatic = 3
     
     '---- LockTypeEnum Values ----
     Const adLockReadOnly = 1
     Const adLockPessimistic = 2
     Const adLockOptimistic = 3
     Const adLockBatchOptimistic = 4
     
     Dim fso, cn, rs
     Set fso = CreateObject("Scripting.FileSystemObject")
     Set cn = CreateObject("ADODB.Connection")
     Set rs = CreateObject("ADODB.Recordset")
     
     'This example SQL statement specifies each field.
     'Dim Sql: Sql = "SELECT g_user_id, g_org_id, u_user_title, i_user_type, u_first_name, u_last_name, u_email_address, " & _
     '               "u_tel_number, u_tel_extension, u_fax_number, u_fax_extension, u_logon_name, u_user_security_password, " & _
     '               "g_user_id_changed_by, i_account_status, u_user_catalog_set, d_date_registered, u_campaign_history, " & _
     '               "i_partner_desk_role, d_date_last_changed, d_date_created, u_Pref1, u_Pref2, u_Pref3, u_Pref4, u_Pref5 " & _
     '               "FROM UserObject"
     
     'These sample SQL statements SELECT everything in the table.
     'Dim Sql: Sql = "SELECT * FROM Addresses"
     'Dim Sql: Sql = "SELECT * FROM Organizations"
     'Dim Sql: Sql = "SELECT * FROM UserObject"
     Dim Sql: Sql = "SELECT * FROM UserObject"
     
     On Error Resume Next
     cn.Open "Provider=SQLOLEDB.1;User ID=" & SqlUserName & ";Password=" & SqlPassword & ";Persist Security Info=True;Initial Catalog=" & SqlDatabase & ";Data Source=" & SqlServer
     If Err.number <> 0 Then
     	MsgBox "Failed to open Database"
     	WScript.Quit
     End If
     
     rs.Open Sql, cn, adOpenForwardOnly, adLockReadOnly
     If Err.number <> 0 Then
     	MsgBox "Failed to open Recordset"
     	WScript.Quit
     End If
     
     Dim Flds, Fld
     Set Flds = rs.Fields
     Dim SqlInsert: SqlInsert = "  INSERT INTO [UserObject] ("
     For Each fld In Flds
     	SqlInsert = SqlInsert & "[" & fld.Name & "],"
     Next
     SqlInsert = Left(SqlInsert, Len(SqlInsert) - 1) & ")"
     If Err.number <> 0 Then
     	MsgBox "Failed to read Field Names"
     	WScript.Quit
     End If
     
     
     Dim SqlValues
     If Not rs.EOF Then
     	Dim SqlScript, Field
     	Set SqlScript = fso.CreateTextFile("CustomPopulateProfileSQL.sql")
     	If Err.number <> 0 Then
     		MsgBox "Failed to write output file"
     		WScript.Quit
     	End If
     
     	Do While Not rs.EOF
     		SqlScript.WriteLine SqlInsert
     		SqlValues = "  VALUES ("
     		For Each Field in rs.Fields
     			If IsNull(Field.Value) Then
     				SqlValues = SqlValues & "NULL, "
     			Else
     				Select Case Field.Type
     					Case 130, 202, 203
     						SqlValues = SqlValues & "N'" & Field.Value & "', "
     					Case 129, 135, 200, 201
     						SqlValues = SqlValues & "'" & Field.Value & "', "
     					Case Else
     						SqlValues = SqlValues & Field.Value & ", "					
     				End Select
     			End If
     		Next
     		SqlValues = Left(SqlValues, Len(SqlValues) - 2) & ")"
     		SqlScript.WriteLine SqlValues
     		SqlScript.WriteLine "GO" & vbCrLf
     		rs.MoveNext
     	Loop
     	SqlScript.Close
     	Set SqlScript = Nothing
     Else
     	MsgBox "There is no data in the database and/or table you specified. Please check connection string to ensure it is correct or check the database to ensure there is data."
     	WScript.Quit
     End If
     
     rs.Close
     cn.Close
     Set rs = Nothing
     Set cn = Nothing
     Set fso = Nothing
     
     MsgBox "Script Generation Complete!!"
     						

  2. Change the values in the script for SqlUserName, SqlPassword, SqlServer, and SqlDatabase, and then save the file.
  3. When you run this script, you create a file named CustomPopulateProfileSQL.sql. Point to this file when you package the Commerce Web site.
     
     Note Most administrators do not include the Profile Schema Script when they package a site. It is a good idea to also select this file (ProfileSQL.sql, if the site is based on the Retail site) when you pack your site.
• Use the SQL Server command line utility BCP to export and import profile data.
  For more information about BCP, see the Books Online topic "bcp Utility."
  1. Run the following command line utility from the source computer running SQL Server:

     bcp "Retail_commerce.dbo.UserObject" out "userObject.txt" -c -q -U"sa" -P"thePassword"

  2. Bring the newly created UserObject.txt file to the destination computer running SQL Server, and then run the following command:

     bcp "Retail_commerce.dbo.UserObject" in "userObject.txt" -c -q -U"sa" -P"thePassword"