Share via


Executing the Custom Import Task

For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.

This topic discusses the final step of importing custom data into the Commerce Server Data Warehouse. For the previous step, see Staging the Data.

This step involves creating a custom ActiveX Data Objects (ADO) script to read data from the staged data file and insert it into the Data Warehouse schema structure. It relies on the OLE DB provider for Commerce Server to guarantee the correct mapping of data. This process is not optimized. We do not recommend that you use a script for large import tasks. It appears here for demonstration only.

Example

If you use the following ADO script, it requires that the parser function, GetNextValue, exists to extract the next value from the staged data file. The format of the staged file is assumed to fit the following pattern:

#users
user1title user1fname user1lname user1email user1city user1state user1ffc
user2title user2fname user2lname ...

The ADO script uses the OLE DB provider for Commerce Server. The provider creates the necessary key values for each entry in the database. It also correctly formats and partitions the data to the underlying data store. We strongly recommend that you use the OLE DB provider for Commerce Server instead of manipulating the data store directly.

'Create the ADO DB record object.
   Dim rec
   Set rec = CreateObject("ADODB.Record")

'Create the ADO DB Connection object.
   Dim cnnConnection
   Set cnnConnection = CreateObject("ADODB.Connection")

'Create the connection string.
'The "fastload=true:" option is required.
   Dim strConn
   strConn = "URL=mscop://InProcConnect/Server=DWServer:" & _
    "catalog=dwschema:database=CSharpSite_datawarehouse:" & _
   "Trusted_Connection=True:fastload=true:"

'Connect to the Data Warehouse.
   cnnConnection.Open strConn

'Retrieve the number of users to process.
   Dim nUsers
   nUsers = GetNextValue()

'Instantiate the commerce GUID generator.
   Dim oGenID
   Set TypeLib = CreateObject("Scriptlet.TypeLib")
   oGenId = TypeLib.Guid
   Set TypeLib = Nothing

'Iterate through the users.
   Dim i
   For i = 0 to nUsers

      'Create a new blank RegisteredUser row.
      rec.Open "instance/registereduser", cnnConnection, _
       adModeWrite, adCreateCollection
   
      'Create the GUID.
      rec("UserID") = oGenID.GenGUIDString()

      'Get user title.
      rec("title") = GetNextValue()
   
      'Get user first name.
      rec("firstname") = GetNextValue()

      'Get user last name.
      rec("lastname") = GetNextValue()

      'Repeat for email, city, and state.

      'Get fav_flavor_code.
      rec("fav_flavor_code") = GetNextValue()

      'Bookkeeping.
      rec("UserIDChangedBy") = "DW Custom Import"
      rec("DateCreated") = Cdate(Now())
      rec("DateLastChanged") = Cdate(Now())

      'Save the new row.
      rec("__Commit") = 1
      rec.Fields.Update

      'Done with current user.
      rec.Close
   Next

'Close the connection and release the objects.
   cnnConnection.Close
   Set oGenID = Nothing
   Set cnnConnection = Nothing

See Also

Other Resources

Importing Custom Data into the Data Warehouse

What is the OLE DB Provider for Commerce Server?