SQL Server Magazine

Businesses increasingly need to pull data from the Web into internal databases and spreadsheets. This common task has spawned a proliferation of custom applications that all do the same thing. The applications have code for downloading files from the Web, logging successes and failures, running the application on a predetermined schedule, and notifying the administrator of failures and completions. In devising methods of handling common tasks, you need to find a method that provides quick turnaround; provides a consistent way to easily manage, monitor, and modify these applications; and uses as many of the OS's services as possible.

For example, let's consider the business problem of bringing data from the Web into internal databases. This task seems to be a good fit for SQL Server 7.0's Data Transformation Services (DTS). A good solution for downloading flat files and Microsoft Access or other data would be to use DTS for data pickup and massage and to use the SQL Server 7.0 Job Scheduler for scheduling, notifying, and logging. DTS is a welcome addition to SQL Server's arsenal of tools. But in its current form, DTS doesn't interact well with the Web because it can't access data by means of a URL and it doesn't support Web site logon authentication. Someone will likely soon produce an OLE DB driver for Web data, and one for XML won't be far behind. In the meantime, however, DTS does let you run VBScript, and you can instantiate COM-compliant objects from VBScript. You could use Visual Basic (VB) to create a simple, generic object for downloading files from the Web. When the object is complete, you'll have a framework for processing data from the Web.

In this article, I demonstrate how to use VB to make a simple ActiveX DLL with the sole function of downloading files from the Web. (If you don't like to write code, you'll like this project. It has only a couple of dozen lines of code, and after you've written and compiled it, you shouldn't have to look at it again.) After the DLL is complete, you can concentrate on writing a DTS package to process the data. Finally, you use Job Scheduler to schedule DTS package execution, set notification options, and set logging options. This design will let you reuse the same code and application services for downloading files, event logging, notifying, and scheduling. And because you set up the scheduling and failure notification with Job Scheduler, you can easily roll the application over to the support team.

Creating the ActiveX DLL
If you don't have VB 6.0, you can use regsvr32 to register the DLL by opening the Run dialog box and typing

regsvr32 "C:/WebDataFetcher/ WebDataFetcher.dll"

to replace the first occurrence of WebDataFetcher with your file path to the WebDataFetcher.dll file. Now you can start creating the project. First, open VB. If you don't see the New Project dialog box, Select File, New Project from the File menu list. Select the ActiveX DLL icon from the New Project dialog box, as Screen 1 shows. Next, open the Project Properties dialog box by selecting the Project1 Properties menu item in the Project menu list. Then, change the project's name and description in the Project Properties dialog box, which Screen 2 shows, to WebFileFetcher. Open the class Properties dialog box by pressing Ctrl+R. Select Class1 from the list, and press F4. Then, change the name of the class to CWebDataFetcher, as Screen 3 shows. Finally, press Ctrl+T to open the Components dialog box, and select the Microsoft Internet Transfer Control 6.0 check box, as Screen 4 shows. You can use several other controls, but this one is simple to use and supports FTP, HTTP, and HTTP over Secure Sockets Layer (HTTPS).

To add a form to the project, you click the Add Form menu item in the Project menu list. Press F4 to open the form's property list, then change the form's name property to frmGetFile. You're now ready to add the Internet Transfer control, which is a VB control that implements FTP and HTTP, to the frmGetFile form. To add the control, click the toolbox menu item in the View menu list, then double-click the Internet Transfer control. Finally, add the code in Listing 1 to the CWebDataFetcher class. You can open the CWebDataFetcher code window by pressing Ctrl+R and double- clicking the CWebDataFetcher item.

Here's a short explanation of the code you add to the CWebDataFetcher class. By calling the GetFile function, you pass in a URL (for example, http://www.anywhere.com/test.mdb), filename and location (C:\test.mdb), username, and password. Pass in empty strings for the username and password parameters if you don't need them. (You'll need these parameters, for example, to restrict users' access to secure Web sites.) The code in Listing 1 loads the frmGetFile form. The line of code

frm.GetFile.Inet1.URL = aURL

sets the Inet1 control's URL property to the aURL variable, which the code passed into the function. The aURL variable will contain the address and name of the file that you want to download from the Web. The code passes the filename and address to the Inet1 control.

The next line of code

If aUsername <> "" Then

is a conditional statement that checks to see whether the aUsername variable isn't an empty string. If the aUsername variable isn't an empty string, it will set the Inet1 control's Username and Password properties to values contained in the aUsername and aPassword variables, respectively. The line of code

b() = frmGetFile.Inet1.OpenURL(, icByteArray)

   Prev. page   [1] 2     next page