.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

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

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server

I am trying to execute the package in SQL Server 2008 and i am getting this error.


Executed as user: sql\SYSTEM. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    

Started:  11:07:09 AM  Error: 2010-09-20 11:07:10.95     Code: 0x00000000     Source: Transform Data Task  undefined      

Description: System.Runtime.InteropServices.COMException (0x80040428): Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.     at DTS.PackageClass.Execute()     at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()  End Error  DTExec: 

The package execution returned DTSER_FAILURE (1).  Started:  11:07:09 AM  Finished: 11:07:10 AM  Elapsed:  1.094 seconds.  The package execution failed.  The step failed.


Do any one have a clue


View Complete Post

More Related Resource Links

SSIS Package working on SQL Client Machine but when trying to run on SQL Server machine giving error

Hi We are trying to run SSIS package on sql server machine but sometimes it is giving error for data flow task containing Script Component. Error is - [SSIS.Pipeline] Error: component "SCR DimensionRelation" (1) failed the post-execute phase and returned error code 0x80004002. This error is not coming continuously. And also not for specific DFT. It comes for different DFTs as we rerun the package. Can anybody help me on this? We are using SQL server 2008. We are not getting any error on client machine :(:(

SSIS package is giving error when ran thru JOB...its running good when I execute in BI Studio or Exe

Friends I am getting weird issue...I built an SSIS package. In one variable I used express builder and built like this... RTRIM( (DT_STR, 2,1252)  (((DT_I4) ( (DT_WSTR, 3) (SUBSTRING( @[system::machineName] , 12, 16 ))) +1) /2)) when I run the package outside ie., with VStudio or execute utility it running absolutely with out any problem. But when I created a job just to execute that package in particular timings...the job is totally failing by throwing error....the error output is like the following: Started:  10:22:33 AM Error: 2010-02-27 10:23:06.23    Code: 0xC00470C2    Source: Rerun_MissingFiles    Description: Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_I4. Error: 2010-02-27 10:23:06.23    Code: 0xC00470C4    Source: Rerun_MissingFiles    Description: Casting expression "(SUBSTRING( @[system::machineName] , 12, 16 )))" from data type "DT_WSTR" to data type "DT_I4" failed with error code 0xC00470C2. End Error Can any one help me please? Thanks in advance.

SSIS package fails under SQL Server Agent job


Hi All

I have designed a SSIS package to encrypt a file using Cipher (EFS) it works fine from BIDS & Package Execution Utility.

But SQL Server Agent job is failing to do this. I am getting the error as

" In executing C:\Windows\System32\Cipher.exe /e /a C:\Test.txt at "",The Process Exit code was 1 while the expected was 0." 


Please help me.




SSIS package fails in agent job (but actually succeeds)


SQL Server 2008 SSIS 32-bit using Oracle 11g 32 bit client to connect to Oracle 10gR2

The job actually runs and populates the destination tables correctly, but for some reason the agent reports the job as failing.  there is only one step (the SSIS package itself).

The package execution returned DTSER_SUCCESS (0).  The return value was unknown.  The process exit code was -1073741819.  The step failed.

There are many other packages that are built more or less the same that do not throw an error but for some reason this one does.

I know this isnt a lot of information - mostly hoping for tips on what steps I can take to narrow in on the problem.

I've run the package directly from Integration Services on the server as well as from BIDS on my desktop and it succeeds.

SSIS Package fails with Execute Process Task when run from SQL Agent Job


I have an SSIS 2008 package running on Win 2008 64-bit server. The package has an Execute Process Task that runs a net use command using cmd.exe to make a connection.

This runs successfully when tested from the command prompt and runs successfully when the package is run in Visual Studio 2008. However, when the package is called from a SQLAgent job the package fails with the error (actual servernames and passwords removed):

Failing Task Name = EPT Connect mapped drive Error Code = -1073573551 Error Detail = In Executing "C:\WINNT\system32\cmd.exe" "/C net use
\\servername\foldername /USER:userid password" at "", The process exit code was "1" while the expected was "0".

I have determined that a successfull connection should provide a exit code of 0.

The job runs under a proxy account but as explained when the package has run from within VS 2008 it was also tested to run under the proxy account and is successful.

It is not a 64-bit issue as I have tested it by running the package using the 32-bit version of dtexec.exe and the same problem occurs.

Also I have found that by running a simple command process within the Execute Process Task such as dir *.* it is successful when run from the job indicating that it is in fact something related to the net use command itself when run from

SSIS package which reads excel files fails on SQL Server Agent



I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.

I tried all the possible fixes I found on the web but still can't get it to work.

Could you shed some light on it?



using SharePoint List for SSIS package

I need to use a sharepoint list as data source for SSIS package, it works fine until the sharepoint site implement Kerberos only (IIS 7) authentication, anyone knows how to get around this? thanks.

SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

Execute SSIS Package PART based on Some Condition

Hello, Is it possible to execute part of the Package based some condition? (Like If var1 = true, then execute this block). If Yes, what control flow I should use. Please advse. I will have a Package Variable and if that variable is True then some part should execute. REst all should execute every time the package is called. Like if I will have "2 Sequence Container" in my control flow, the first one should execute every time, but 2nd one only if the variable value = True. Thanks, Prabhat

SSIS package Scheduled Job Not Running

I'm not sure which way to research this issue.. I have an SSIS package that we can run just fine manually. But, when we put it into a scheduled job, it won't run. The package is on the same system as the SQL Server (2005), but it does call for updates from tables on another SQL Server (2000) on another domain (trusted). So, I'm thinking this might be a permissions / security issue, but not sure where to start... The package ProtectionLevel is "don't save sensitive", but that didn't help. Any help would be appreciated. Thanks. jill

How to make SSIS Package Fail when something goes wrong

Hello, Is there any way I can make the package (SSIS 2005) fail when something happen? Like: if some value become zero I will to make the package fail from with in the SSIS, so that the AgentJOB  that started this package will know? Thanks, Prabhat Nath

Can not get excel connection when calling an SSIS package from ASP

I have an SSIS package importing data from excel. I have coded it and deployed it on the sql 2008 server. I then execute it via a stored procedure with the following: select @cmd = 'C:\"Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /sq "HRIS"' select   @cmd = @cmd + ' /SET \Package.Variables[User::path].Properties[Value];"' + @path + '"' exec   @rtn = master..xp_cmdshell @cmd   This works fine when  i execute it on my local machine. But when I try to call this Stored proc from my asp app (classic asp, not .net) I get the error: Code: 0xC0202009 Source: HRIS Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error   Any help would be greatly apprecitated

Execute an SSIS package from ASP.net (C#) - my package keeps failing. Help! - Works on localhost but

I'm looking for any and all information on how to execute an SSIS package from my asp.net web site. I have written the call and when I run the application from (Localhost) it works, but when I run it from the server it does not.Here is my code to call and execute the package: public string ProcessFiles(int ClientID, string FileName) { //System.Security.Principal.WindowsImpersonationContext impersonationcontext; //impersonationcontext = ((System.Security.Principal.WindowsIdentity)ServiceContext.User.Identity).Impersonate(); string packagePath = "\\\\\\Customer File Import.dtsx"; Microsoft.SqlServer.Dts.Runtime.Application app; Microsoft.SqlServer.Dts.Runtime.Package pkg; Microsoft.SqlServer.Dts.Runtime.Variables vars; Application integrationServices = new Application(); DTSExecResult result; FileName = FileName.Substring(FileName.Length - 25 , 25); app = new Microsoft.SqlServer.Dts.Runtime.Application(); pkg = app.LoadPackage(packagePath, null); //pkg = integrationServices.LoadFromSqlServer("Customer File Import.dtsx", "", "healthtech\\brian.montfort", "Nightdragon8", null); vars = pkg.Variables; if (pkg.Variables.Contains("

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

help regarding SSIS package!

Hi Everyone,, I am pretty much new to SSIS. Any help regarding this project would be appreciate it. I need to transfer data from the files into a Database. I need to use FTP to copy those files locally on a machine and then combine/merge those files  and then read the data from those files and copy the values into the database. I have atleast 5-6 files which I need to combine or read them individually to form a single row in the database. (I am not sure if merging into single file or reading as each indivial file is faster!) Those files are in report formats and can be opened like notepad. There is Ton of data in each file and some filtering is required before I insert a single record into the DB. I just started learing the SSIS package, i am not really sure how I can do it.Any help or sample code or screen shots would be really appreciated!! Thanks Sunny.

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend