The big import

Well in the last post I boasted that looping through the time items was going to be easy, well it threw up a couple of interesting issues....

First off, I couldn't find an easy way to get to my Time folder by name, so in the end I copped out and just found it's EntryID and got the folder directly. But the confusing one was providing the URL to my existing script. I thought I would be able to get the URL using objMessage.Fields(&H6707001E).Value and pass that to my script. Unfortunately when retrieving this value it's already URL Encoded and unfortunately when you use ExOLEDB to find the record it doesn't want it URL Encoding. Rather than trying to decode it I decided to retrieve the EntryID (as I already had the message) and pass that to my script. Then I altered the original script to check for EntryID being passed as an argument, if it was it could skip the process to get it.

The following script is what I came up with to loop through the Time items and call the script with the relevant arguments:


providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders"

'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"

'Get the message based on the message entry ID.
Set objPubStore = objSession.InfoStores("Public Folders")

Set objRootFolder = objPubStore.RootFolder

Set objFolder = objSession.GetFolder("EF000000198262C0AA6611CD9BC800AA002FC45A020000000100000000039672")

Set WshShell = CreateObject("WScript.Shell")
For Each objMessage In objFolder.Messages
WScript.Echo "URL:" & providerURL & objMessage.Fields(&H6707001E).Value & ",EntryID:" & objMessage.Fields(&H0FFF0102).Value
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""save"" " & chr(34) & providerURL & objMessage.Fields(&H6707001E).Value & chr(34) & " " & chr(34) & objMessage.Fields(&H0FFF0102).Value & chr(34),7,True)
Next
Set WshShell = nothing

Set objFolder = Nothing

Set objRootFolder = Nothing

Set objPubStore = Nothing

objSession.Logoff
Set objSession = Nothing


Since my last post I've also noticed a couple of problems in the main script which I've since altered. Mainly to do with the SQL DataType conversion function. I've included a check for INTs so that it can put in NULL if a value isn't provided and also to check NTEXTs and TEXTs for apostrophes. This function now looks like:


Function ConvertSQLDataType(DateType,Value)
'Function to convert VB Script values to SQL values
Select Case DateType
Case "int"
If (IsNull(Value) Or Value="" Or IsEmpty(Value)) Then
ConvertSQLDataType="NULL"
Else
ConvertSQLDataType=Value
End If
Case "datetime"
ConvertSQLDataType=FormatDateTime(Value,1) & " " & FormatDateTime(Value,3)
Case "varchar", "nvarchar", "text", "ntext"
ConvertSQLDataType=Replace(Value,"'","''")
Case Else
ConvertSQLDataType=Value
End Select
End Function


The only other thing I added was some basic error checking. Unfortunately it has to be basic as VBScript doesn't support any clever form of error checking, so I've just had to guess where my script might go wrong and dump any potential errors to a text file.

So as I write this post my script is quite happily chundering throught the 87,000 or so items dumping them to the SQL DB.

The next installment should see me replicate this whole process for the Request folder.