calls the OpenURL method of the Inet1 control, requesting it to return the file at the requested address as the ByteArray variable type and put it in the b() ByteArray variable. The line of code
Open aFile For Binary Access Write As #1
creates the file specified in the aFile variable. The code
Put #1, , b()
then writes the b() ByteArray variable, which contains the Web file, to the file opened in the previous line of code. The code
Close #1
closes the open file. The code then unloads the form. (The code also contains some simple error handling for completeness.) The project to download and process Web data is now complete. Compile the project by clicking the compile WebDataFetcher.dll menu item in the File menu. For information about debugging the project, see the sidebar "How to Debug Visual Basic COM DLLs Used in DTS," page 55.
Creating the DTS Package
Now, let's create a simple DTS Package that downloads a text file from the local Web server. This text file contains a couple of new stores for the Pubs database. The package will insert these new stores into the Stores table in the Pubs database. First, open SQL Server Enterprise Manager and connect to your local database. Right-click the Data Transformation Services folder, and select the New Package menu item. On the Task menu, drag an ActiveX Script Task into the package. Name the ActiveX Script Task Download Webdata.txt, then put the code from Listing 2 in the Script window, as Screen 5, page 53, shows. This code creates an instance of the CWebDataFetcher class. The code then calls the GetFile function, passing in the location of the file on the Web and the place where you want to save it locally.
Copy the Webdata.txt file—which you can download from the SQL Server Magazine Web site at http://www.sqlmag .com, Article ID 8413—to the root directory. (The Microsoft Internet Information Server—IIS—installation process sets this directory's location. The default is C:\InetPub.) Save the DTS package, then run it. If an error occurs when the task runs, follow the instructions in the sidebar "How to Debug Visual Basic COM DLLs Used in DTS." The Webdata.txt file is now in the root directory of your drive, and you'll use the file to continue building the package.
Now you need to open the DTS Designer and add a text file source to the package. The text file source will serve as the connection that DTS will use to access the Webdata.txt file. To open the Connection Properties dialog box, drag a new text file data icon from the toolbox into the DTS package. Name the connection Webdata .txt, and set the filename to C:\WebData .txt, as Screen 6 shows. Click OK, and the Text File Properties wizard will open. Select the Delimited option, and set the Text qualifier to none, as Screen 7 shows. Click Next. In the Specify Column Delimiter dialog box, select the Comma option. The two rows of data in the WebData.txt file will appear in the Preview window, as Screen 8 shows. Click Finish, then click OK.
Drag a Microsoft OLE DB Provider for SQL Server data icon onto the package. This icon is the server icon in the Data toolbox at the side of the package. Name the connection pubs, set the Server text box to (local), and select the pubs database, as Screen 9 shows. Click OK. To create a transformation between the WebData.txt connection and the pubs connection, hold down the Ctrl key and click the WebData.txt connection, then click the pubs connection. Right-click to open the menu list, then click the Transform On menu item in the WorkFlow menu list. Then, open the transformation's Properties dialog box and give it a description, such as Move the store records from the text file, as Screen 10 shows. Select the Destination tab, and select the [pubs].[dbo].[stores] table in the Table name drop-down list, which Screen 11 shows. Select the Transformations tab, then click OK. Create an On Success workflow between the Download WebData.txt ActiveX script and the WebData.txt source control, as Screen 12 shows. To do this, hold down the Ctrl key and click Download WebData.txt ActiveX script, then click the WebData.txt source control. Right-click to open the menu list, then click the On Success menu item in the WorkFlow menu list.
You can download the completed package from the SQL Server Magazine Web site. If you want to run the package again, delete the newly added rows from the Stores table by using the following SQL statement:
delete from stores where stor_id in ( 1, 2 )
Save the package to the (local) server.
This project gives you a way to download files from the Web within DTS. You can reuse the ActiveX Script Task—Download Webdata.txt—in any DTS project to download and process Web data.
How the SQL Server Agent Fits In
Downloading and processing Web data is a task you'll probably need to perform periodically, so you might want to set up failure or completion notification and logging. The Schedule Package wizard in the SQL Server Agent is perfect for this task. To open the wizard, right-click on a DTS package in Enterprise Manager. The Edit Recurring Job Schedule dialog box, which Screen 13 shows, lets you set various scheduling options, such as frequency and duration. After you've completed the steps in the wizard, you'll have a package that the SQL Server Agent runs on a schedule. You can access the logging and failure notification options through a Job's properties on the Notifications tab, as Screen 14 shows. Set the output file that you want to send the notification to on the Advanced tab of the step's Properties dialog box. After you set the notification options, you have a robust data processing system in place.
The biggest advantage of this solution is that it lets designers apply the business rules to the data. You don't have to write code for scheduling, logging, notifying of failure or completion, or file downloading. This solution also lets you process all Web data in a consistent manner, which makes the data easier for the support team to manage.
End of Article
Prev. page
1
[2]
next page -->
