Using Data Transformation Services, part 2: Using the DTS Designer Objects - Executing the package and verifying the transfer
(Page 6 of 6 )
As mentioned in Part 1, there are several ways to execute this package. Click Package in the DTS Designer and, from the drop down, click on Execute. This initiates the execution of the defined package in the DTS editor. In this case, the package MSA will be executed and the window processing the steps (tasks) shows up as follows, with another window showing up either success or failure. The first task has processed some 164 rows and the second step has processed some 17 rows of data.
Locate the SQLAccessNew.mdb file and open it. The New Table table should be present. When the table is opened the imported files from the SQL Server may be seen as follows:
Similarly, locate the MS Excel file, MSA.xls, and open it to reveal the contents shown in the next picture. You may also verify that dts created the table name you gave and populated the filtered data that you chose.
It is possible to log errors to a file as well as monitor completion status in the event log by appropriately configuring the properties window of the package as shown.
Here is an example of what is logged into the event viewer after execution of a package.
The execution of the following DTS Package succeeded:
Package Name: MSA
Package Description: (null)
Package ID: {9D446B7C-0E22-4B93-8CA8-18B6B7FB2305}
Package Version: {836733BE-482F-4517-9EED-A34FF8979427}
Package Execution Lineage: {4D72E66A-B655-4C2F-A947-F332C1E0324D}
Executed On: XPHTEK
Executed By: computer user
Execution Started: 3/27/2005 7:28:04 PM
Execution Completed: 3/27/2005 7:28:12 PM
Total Execution Time: 7.25 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 3/27/2005 7:28:05 PM
Step Execution Completed: 3/27/2005 7:28:08 PM
Total Step Execution Time: 2.969 seconds
Progress count in Step: 164
Step 'DTSStep_DTSDataPumpTask_2' succeeded
Step Execution Started: 3/27/2005 7:28:08 PM
Step Execution Completed: 3/27/2005 7:28:10 PM
Total Step Execution Time: 2.75 seconds
Progress count in Step: 34
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Summary The DTS designer is mostly wizard driven and easy to work with. The query builder, although it looks similar to the Query Analyzer, is not the same. Several items in the Data Transformation Services designer have not been discussed. These will be considered in a future tutorial.
No errors were encountered in this step-by-step procedure, but this is not always the case. The errors can be written to a log file. On the completion of the execution of the package, a completion status report can be written to the Event Viewer. It should be noted that a second time execution of the package appends the results and care should be taken to consider this possibility. Text annotation should help in documenting the package details.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 27 
