Second Runner-Up
Kevin Terry,
DBA
Problem-Knowledge Coupler (PKC) Corporation
Teaching DTS a New Trick
"There's a certain satisfaction in trying something that you don't know will work," says Innovator Award runner-up Kevin Terry of his winning solution. Kevin's solution uses a common SQL Server tool—DTS—to solve an uncommon problem—moving data from Microsoft Outlook into SQL Server.
The problem arose when Kevin's company, PKC, needed to move data contained in custom Outlook forms to a Web site so that customers could use the data. PKC is a software development company whose main product is medical guidance software called Problem-Knowledge Couplers. Companies that subscribe to the software let their employees access it through the Web or an in-house installation. Employees use the software to search for information about medical problems they might be having. But the results they get aren't generic. Couplers use data that users provide to intelligently match the symptoms with personalized medical information. "The program asks you all sorts of questions about your symptoms," Kevin explains. "Then it cross-references those symptoms with current medical literature. It's designed to give you some idea of what's going on and help you search for treatment options."
Companies can subscribe to all Couplers that PKC offers or select certain Couplers that are of particular interest to their employees. For example, if a company is sponsoring a stop-smoking campaign, it can subscribe specifically to the Coupler containing smoking-cessation information. The SQL Server system that Kevin administers serves as a place to store a list of available Couplers, but the descriptions of the Couplers were stored in Outlook forms. Kevin needed to get the descriptions into SQL Server so that he could put the complete Couplers list—including descriptions—on PKC's Web site so that customers could see their options.
Kevin faced the prospect of manually entering all the descriptions into a SQL Server database. But manual entry opened the possibility of introducing errors into the data. In seeking alternatives to manual entry, Kevin found a driver that he could use to directly access Microsoft Exchange as a linked server, but it worked only when SQL Server was on the same machine as Exchange, which wasn't the case in his situation.
Kevin's search for a better transfer method took him back to SQL Server. He wanted to use SQL Server's own DTS tool if possible because "that's where the data was going to end up anyway." In addition, Kevin really wanted to take advantage of DTS's job-scheduling and tracking capabilities, which would give him more control over job execution and let him watch for problems during the transfer. He'd seen lots of information about using DTS to move data from SQL Server to Outlook, but not much information about moving data from Outlook to SQL Server. Still, he decided to give it a try.
Kevin says he spent a few days "flailing about trying to see if the solution was even possible." With advice from PKC's resident Outlook expert, Chris Weed, about how to use scripting with Outlook, Kevin created an ActiveX task that lets DTS move the descriptions of the Couplers from Outlook forms to a working table, and from there to SQL Server. (To see the scripts that Kevin used for this solution, you can read his article "Coupling Outlook to SQL Server" at http://www.sqlservercentral.com/columnists/kterry/couplingoutlooktosqlserver.asp.)
The completed DTS package requires only three steps, as Web Figure 1 shows. Kevin warns that the more cleansing and manipulation you have to do with your data, the more steps you'll need. However, he says he thinks other DBAs can certainly adapt his solution if they need to move such information as email addresses, email text, contacts, or appointments from Outlook to SQL Server.
Kevin learned from creating this solution not to underestimate SQL Server's capabilities."SQL Server comes with a lot of powerful stuff," he says. "Before you look outside it, see if what you need can be done with what comes in SQL Server."
See Associated Figure
Dawn Cyr (dcyr@sqlmag.com) is a senior editor for SQL Server Magazine.
Anne Grubb (agrubb@windowsitpro.com) is a senior editor for Windows IT Pro. She has more than 20 years of experience as a writer and editor of articles, books, and other materials in the computer, business, and legal fields.