Executing the Custom Import Task
This topic discusses the final step in the process of importing custom data into the Commerce Server 2000 Data Warehouse. For the previous step, see Staging the Data.
This step involves creating a custom 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 to ensure the correct mapping of data. This process is not optimized. The use of a script is not recommended for very large import tasks; it appears here for demonstration purposes only.
The use of the following ADO script requires the existence of a parser function, GetNextValue, 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 properly formats and partitions the data to the underlying data store. Use of the provider is highly recommended 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=Retail_dw:user=admin:" & _
"password=*****: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 oGenID = CreateObject("Commerce.GenID")
'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