SSIS package fails giving "System.Runtime.InteropServices.COMException"

• u12umesh

  Old Hand

  Points: 328

  More actions

  April 2, 2008 at 8:59 am

  #120905

  Hi,

  I have one SSIS package which is written in Visual studio business intelligence tool. For that SSIS packages i have scheduled a job from SQL server management studio 2005. I mean i have scheduled a job in SQL server agent.

  But it is failing giving some com.interop exception. Not sure what type of error is this?

  It give following type error:

  Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-03-27 23:00:00.81 Code: 0x00000000 Source: Execute DTS 2000 Package Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 11:00:00 PM Elapsed: 0.579 seconds. The package execution failed. The step failed.

  I get the same error when i try to execute the package from Visual studio Business Intelligence tool.

  Can you please help me out as to what is this "System.Runtime.InteropServices.COMException" exception occuring when scheduling or executing the job.

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  April 2, 2008 at 9:15 am

  #797324

  This would typically be caused by using a connection manager for managed code with an OLEDB component (or the other way around).

  It is hard to do in most cases because the IDE prevents it, but if you used any script components, they are likely the cause. Make sure you use .Net data sources in script components - you cannot use native OLEDB connections because they are com components.

• u12umesh

  Old Hand

  Points: 328

  More actions

  April 3, 2008 at 7:33 am

  #797802

  I am not using any native OLEDB connection or provider.

  This is problem is with only one SSIS packages which is executing DTS 2000 pacakge.

  There are other SSIS packages which are also using DTS 2000 packages and they are executing successfully.

  So why it is giving error for only this SSIS package. And that too a Com type of exception.

  NOt sure what to do as this is a production server issue.

  Please help

  Thanks,

• mcgillm

  SSC Journeyman

  Points: 87

  More actions

  September 9, 2008 at 2:56 pm

  #869316

  I am getting this same error on a very simple SSIS package. If you run the package manually, it works fine, but as soon as you schedule it as a job and run it, I get the following:

  Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

  09/09/2008 16:40:00,Employee Import,Error,0, . The last step to run was step 1 (Import Employees).,00:00:02,0,0,,,,0

  09/09/2008 16:40:00,Employee Import,Error,1, tempEmployees Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:40:00 PM Finished: 4:40:02 PM Elapsed: 1.344 seconds. The package execution failed. The step failed.,00:00:02,0,0,,,,0

  Any help would be greatly appreciated.

• matthew.pullen

  SSChasing Mays

  Points: 644

  More actions

  November 11, 2008 at 4:46 am

  #896858

  Are you by any chance using local/localhost as your server

  I had a similar problem on a development server while migrating a SQL 2000 database to SQL 2005.

  The original DTS was developed on a SQL 2000 machine and used a SQL login for a connection.

  The server was changed to localhost with Windows Auth but failed with your error message.

  It was changed again to local and it worked.

  In short for SQL 2000 dts use local

  for SQL 2005 use localhost

• Henrik Schütze

  Ten Centuries

  Points: 1004

  More actions

  January 8, 2014 at 2:45 pm

  #1678602

  matthew.pullen

  You are me Hero !!! 2hours of searching and now it works !!! Thanx!!!

  Switch vom "(local)" to the real Servername in DTS solve the problem