Introduction

I recently completed an project that I thought might be interesting to the SQL Server Community. First a little background of what my company does, as it's the only way I could think of to explain why I had to do this particular project.

Problem-Knowledge Coupler Corporation (PKC) is a software development company whose core competency is medical guidance software called Problem-Knowledge Couplers® or simply Couplers. A Coupler elicits patient information and then presents corresponding clinical strategies. That is, a Coupler is an information tool that, in a structured manner for a particular health topic, "couples" (or links) patient "problems" with medical "knowledge." Now to the project, we have a couple hundred of these Couplers. Information about them (titles, descriptions, etc.) is kept in Outlook in a public folder, using custom forms. We wanted users on our web site to be able to search for couplers (each is related to a specific problem; headache, diabetes, etc) and have the web site present coupler titles and descriptions, so the appropriate coupler is chosen.

Our web site has sql server as its back-end. In order to present these titles and descriptions they were either going to have to be manually entered into sql server, or imported into sql server from outlook, where they are stored. Not wanting to have it be done manually (and therefore having to worry about the data being put into the sql server I'm responsible for), I began to investigate pulling the data directly into sql server from these outlook custom forms. I did quite a bit of research on the web on this, and found quite a bit of info on exporting from sql server into outlook, but not much on pulling from outlook into sql server, which led me to write this article.

I was motivated to have the solution live in SQL Server so I could take advantage of scheduling, tracking successful completion of job, etc. I did come across a driver that could be used to directly access exchange as a linked server (I hoped), but it only worked if sql server was on the same machine as exchange, which is not the case here. SQL Server comes with a product designed to transfer data to and from sql server, so Data Transformation Services (DTS) was where I concentrated my efforts.

This task was completed with DTS, mostly an Active-X task, using scripting. A significant amount of assistance was given on the particulars of using script in outlook by our resident outlook expert. This particular project is somewhat specific to this task, since it involved custom forms, but the process would be the same to get at just about any information in outlook; email addresses, emails, contacts, appointments, etc.

First a quick look at the database. The relevant table contains a primary id, a coupler number, title and description:

CREATE TABLE [tblList] (
[fldID] [int] IDENTITY (1, 1) NOT NULL ,
[fldNum] [int] NULL ,
[fldName] [varchar] (100) NOT NULL ,
[fldDescrip] [varchar] (4000) NULL ,
CONSTRAINT [PK_tblList] PRIMARY KEY  CLUSTERED 
(
[fldID]
)  ON [PRIMARY] 
)

I needed to update the name and description. The fields that had to be updated are fldName and fldDescrip. The field fldID is the primary key, and is the id used throughout the database for relationships, the coupler number (field name: fldNum) is the number used to match a coupler in the sql table to its corresponding entry in outlook.

For this process I created a working table, to hold the data from outlook before our coupler list is updated. This could be a temporary table, but periodically I like to look at what's being imported before updating, to make sure everything looks ok. That table is:

CREATE TABLE [tblCpls] (
[cplNum] [int] NULL ,
[cplName] [varchar] (4000) NULL ,
[cplDesc] [varchar] (1000) NULL,  
CONSTRAINT [PK_tblList] PRIMARY KEY  CLUSTERED 
(
[cplnum]
)  ON [PRIMARY] 
) 
) ON [PRIMARY]
GO

For this project, the DTS package only has 3 steps, the more cleansing, checking, or manipulation of data you do, the more steps you'd need. The data from outlook is dumped into this table, and then a stored procedure is executed that updates our production table; tblList. If necessary, any data cleansing or checking could then be done here before updating. The stored procedure updates the live table, and we are done. The DTS package looks like this:

DTS Package

First a sql connection is added that connects to our sql server. The first step cleans out the working table, which holds the data imported from outlook (tblCpls), so it starts with fresh data. It is a simple sql task that executes the following T-SQL:

Truncate table tblCpls

Next is the actual script that imports the data from outlook.

ActiveX Script

First declare the needed variables. After that the following lines set a few of these

variables:

Set RSLogin = CreateObject("ADODB.Recordset")
Set objOutlook = CreateObject("Outlook.application")
Set mNameSpace = objOutlook.GetNameSpace("MAPI")

The RSLogin creates a recordset object, so I can work with the data I'm getting. Next create an instance of the Outlook application, and then get a namespace object within our Outlook object, so I can now work within Outlook.

The next 3 lines of code are somewhat particular to our environment:

strOlStoreID = "18DE200A0C99D6DC800AA002FC45A06000000504B435654303100D"
strOlEntryID = "5B7D1118DE200A0C99D6DCCCBA91795B7D11145A06000000504B435654300000"
Set strCplFolder = mNameSpace.GetFolderFromID(strOlEntryID, strOlStoreID )

Outlook custom forms can be accessed via their entry and store ids. Outlook assigns a unique entry id to an item when it is created, and the store id represents the folder the item is stored in. As a caution, if you're going to use this method, outlook changes an item's entry id when the item is moved to a different store (i.e. from your inbox to an Exchange public folder). In this case the custom forms I'm getting to do not move, so this is the quickest method to get to them. It is also possible to navigate through the folder structure in Outlook as well, if you don't know these ID values. For more information on these ids and how to obtain them, see Outlook's help.

So after assigning the two variables strOlEntryID and strOlStoreID their proper ids, use the GetFolderFromID method to return the MAPIFolder object desired.

Next create and open a connection to SQL Server

Set Connection1 = CreateObject("ADODB.Connection") 
ConnectionString= "Provider=SQLOLEDB; "
ConnectionString= ConnectionString&"SERVER=SERVERNAME; "
ConnectionString= ConnectionString&"UID=USERID; "
ConnectionString= ConnectionString&"PWD=PASSWORD; " 
ConnectionString= ConnectionString&"database=DBname"
Connection1.Open ConnectionString

Now that we've gotten to the public folder needed, call the Items property, which returns a collection of items in the folder. Next grab the first item and then start moving through the collection.

Set oItems = strCplFolder.Items 
Set objItem = oItems.GetFirst

A simple Do loop is used to iterate through the Items collection, looking for the particular User Properties desired. I am looking for User Properties, because the particular data I was looking for is stored in custom forms, in fields set up expressly to store this data. If you were looping through the contacts folder, looking for the contact's full name, you could just call the field's name (just an example not part of this project/DTS package):

Set myContacts = myNamespace.GetDefaultFolder(olFolderContacts).Items
cntName = myContacts.FullName

To get the appropriate data, call the Find method on the UserProperties object of the item. Then return the Value property to get the data needed. With the description and title, the Replace function was used to get rid of single quotes, which caused the sql statement to crash. Then create the T-Sql insert statement and write the record into the working table in the sql database. Finally move to the next Item in the collection and loop through again, until the end of the collection is reached. This method is admittedly sql intensive launching repeated insert statements, a more efficient approach could surely be found. It isn't too long of a list (only a couple hundred), so I've left it as is for now.

Do While Not objItem Is Nothing
Set oProp =  objItem.UserProperties.Find("Description") 
Set oCplNum =  objItem.UserProperties.Find("Cpl Number") 
Set oCplTitle = objItem.UserProperties.Find("Title") 
CplNum = oCplNum.Value
CplDesc = Replace(oProp.Value , "'" , " '' "  )
CplTitle = Replace(oCplTitle.Value , "'" , " '' "  )
SQLLogin = "Insert  into tblcpls values  ( '"& CplNum &"' , ' " & CplDesc & " ' , '"& CplTitle &"')  "
RSLogin.open SQLLogin,Connection1, 3
Set objItem = oItems.GetNext
Loop

The last bit is just some cleanup and the end of the ActiveX script:

Set RSLogin = Nothing
Set objOutlook = Nothing
Set mNameSpace = Nothing
Set strCplFolder = Nothing

Connection1.Close
Main = DTSTaskExecResult_Success
End Function

Lastly, another sql task runs the stored procedure that updates our production table. The actual stored procedure code is:

CREATE proc sp_Upd_CplDesc
AS
update tblList
set fldDescrip = cplName,
    fldName = cplDesc
from tblList
join tblCpls on fldNum = cplNum

To run it simply:

exec sp_Upd_CplDesc

Conclusion

Now just run the DTS package, and the information is updated in SQL Server. The natural extension of this would be to update information in outlook from SQL Server, if you look around this site and others; there are a number of examples of how that would work.