SSIS can sometime throw error “Execution was canceled by user” when there is a issue with the DTS package task.
Recently I was working with the legacy Execute DTS 2000 Package Task in SSIS and found that In the SSIS Control flow when you use an Execute DTS 2000 Package Task to run a DTS 2000 package, if the DTS package fails, the resulting error in SSIS can be quite vague...
My SSIS Package that used to work previously suddenly stopped working and threw errors:
Error: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
I enabled SSIS Package log and below is the log entry:
OnError,MyComputer,MyDomain\MyUserName,Package,{CA338B74-F8DE-4166-AF7A-4DD344F47C3D},{B0666597-2757-4A4E-8F63-820BD6607ED4},2/24/2010 3:23:49 PM,2/24/2010 3:23:49 PM,0,0x,System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
OnWarning, MyComputer,MyDomain\MyUserName,Package,{CA338B74-F8DE-4166-AF7A-4DD344F47C3D},{B0666597-2757-4A4E-8F63-820BD6607ED4},2/24/2010 3:23:49 PM,2/24/2010 3:23:49 PM,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
There is a Knowledge Base article https://support.microsoft.com/kb/904796 which speaks of the similar error when:
- You run or schedule a SQL Server 2005 Integration Services (SSIS) package that uses the Execute DTS 2000 Package task to run a SQL Server 2000 Meta Data Services package.
- You run or schedule an SSIS package that uses the Execute DTS 2000 Package task to run a SQL Server 2000 DTS package that uses the Execute Package task to run a SQL Server 2000 Meta Data Services package.
However, it didn’t make much sense in my scenario as the package I was working on was not running Sql Server 2000 Meta Data Services package.
So I decided to enable logging on the DTS 2000 package level which helped me to dig deeper and get the actual underlying error message. The DTS Package log looked like the following:
Package Steps execution information:
Step 'DTSStep_DTSExecuteSQLTask_1' failed
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID: 0
Step Execution Started: 2/24/2010 3:38:08 PM
Step Execution Completed: 2/24/2010 3:38:25 PM
Total Step Execution Time: 16.131 seconds
Progress count in Step: 0
This error message pointed me to the right direction as there was indeed a connectivity issue to the data sources I was using in the DTS 2000 Package. Once I addressed the connection issues, my package started working like a charm. So, in a nutshell if you are not hitting the scenario as highlighted in the KB above, you should always enable logging in the DTS 2000 package level when you run into issues running the Execute DTS 2000 package task in SSIS. Below are the steps to enable logging in DTS 2000 package:
1. Open the DTS 2000 package:
A) Either use Enterprise Manager and Right Click on Data Transformation ServicesàLocal PackagesàDesign Package.
B) OR within BIDS (You need to have DTS 2000 design components installed from here) Double Click
the Task, and in the Advanced Editor, click "Edit Package..."
2. Click the Background of the design surface in the DTS 2000 package
3. Go to Package Menu > Properties
4. Add the logging:
A. Add an Error handling file with the Browse button.
B. If you want check the box "Log package execution to SQL Server" which will log the output msdb.
Save the DTS 2000 package design and re-run the SSIS Package. After the failure, go to the location who provided to generate the DTS Logs and you should be able to figure out the actual issue in the DTS Package which causes the SSIS Package to fail in turn.
Author : Debarchan(MSFT), SQL Developer Engineer , Microsoft
Reviewed by : Naresh(MSFT) , SQL Developer Technical Lead , Microsoft