Executing the Custom Import Task
This topic discusses the final step of importing custom data into the 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 2009 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 2009. 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 2009 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