Using Data Transformation Services, part 3: Using the DTS Designer Objects (continued) - Verify the results of transformation
(Page 8 of 9 )
Case 1:
Destin.txt and Destin2.txt both exist and are empty. If the package is executed now, the result is as shown. There is an error in the Data Transformation task.
One can look at the error message in the Event Viewer:
Event Type: Information
Event Source: DataTransformationServices
Event Category: None
Event ID: 80
Date: 05/03/2005
Time: 4:03:38 PM
User: N/A
Computer: XPHTEK
Description:
The execution of the following DTS Package succeeded:
Package Name: FileCreated
Package Description: DTSScript
Package ID: {61DC5E41-EB68-4A80-A84C-DDC92C8C8B7A}
Package Version: {0FE41506-7B8D-4038-A39C-6B9FBA2E21A7}
Package Execution Lineage: {E57FBB0A-56C8-40E4-90FC-123D101BDD5B}
Executed On: XPHTEK
Executed By: computer user
Execution Started: 05/03/2005 4:03:36 PM
Execution Completed: 05/03/2005 4:03:38 PM
Total Execution Time: 2.594 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 05/03/2005 4:03:36 PM
Step Execution Completed: 05/03/2005 4:03:36 PM
Total Step Execution Time: 0.11 seconds
Progress count in Step: 9
Step 'DTSStep_DTSActiveScriptTask_1' succeeded
Step Execution Started: 05/03/2005 4:03:36 PM
Step Execution Completed: 05/03/2005 4:03:38 PM
Total Step Execution Time: 2.563 seconds
Progress count in Step: 0
Step 'DTSStep_DTSDataPumpTask_2' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error creating datafile mapping: The volume for a file has been externally altered so that the opened file is no longer valid.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
Step Execution Started: 05/03/2005 4:03:36 PM
Step Execution Completed: 05/03/2005 4:03:36 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0
For more information, see the Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Case 2:
Destin.txt does not exist; Destin2.txt exists, but is empty. If the package were to be executed now, you would get the following result. Since the FirstCopy does not exist to start with, the transformation fails.
However, after execution Destin.txt is created as a copy of TextDts.txt.
The execution of the following DTS Package succeeded:
Package Name: FileCreated
Package Description: DTSScript
Package ID: {61DC5E41-EB68-4A80-A84C-DDC92C8C8B7A}
Package Version: {0FE41506-7B8D-4038-A39C-6B9FBA2E21A7}
Package Execution Lineage: {F5B1F4A7-162E-43B1-933A-29950ACF3953}
Executed On: XPHTEK
Executed By: computer user
Execution Started: 05/03/2005 4:08:58 PM
Execution Completed: 05/03/2005 4:09:01 PM
Total Execution Time: 3.36 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 05/03/2005 4:08:58 PM
Step Execution Completed: 05/03/2005 4:08:58 PM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 9
Step 'DTSStep_DTSActiveScriptTask_1' succeeded
Step Execution Started: 05/03/2005 4:08:58 PM
Step Execution Completed: 05/03/2005 4:09:01 PM
Total Step Execution Time: 3.344 seconds
Progress count in Step: 0
Step 'DTSStep_DTSDataPumpTask_2' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: The system cannot find the file specified.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
Step Execution Started: 05/03/2005 4:08:58 PM
Step Execution Completed: 05/03/2005 4:08:58 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0
For more information, see the Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
In both cases, Destin.txt is created but Destin2.txt remains empty.
Next: Add a workflow item to rectify the package >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
