.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 in agent job (but actually succeeds)

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

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.

View Complete Post

More Related Resource Links

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 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?



SQL Agent Failing when trying to run my SSIS Package

My SQL Agent was working fine.  Now when I try to run it, it failes when trying to run my SSIS package.  I looked in the logs, and job history, no luck in finding any critical errors. My SSIS package runs fine through VS 2005 in debug mode. Here are the only errors: 01/04/2006 09:53:48,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect01/04/2006 09:53:48,,Warning,[260] Unable to start mail session (reason: No mail profile defined)01/04/2006 09:53:48,,Information,[129] SQLSERVERAGENT starting under Windows NT service control01/04/2006 09:53:48,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent01/04/2006 09:53:30,,Information,[432] There are 11 subsystems in the subsystems cache01/04/2006 09:53:29,,Information,[339] Local computer is BG-SQL2005 running Windows NT 5.2 (3790) Service Pack 101/04/2006 09:53:29,,Information,[310] 1 processor(s) and 512 MB RAM detected01/04/2006 09:53:29,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is01/04/2006 09:53:29,,Information,[102] SQL Server ODBC driver version 9.00.139901/04/2006 09:53:29,,Information,[101] SQL Server BG-SQL2005 version 9.00.1399 (0 connection limit)01/04/2006 09:53:29,,Information,[100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 1860

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


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


SSIS Package / SQL Agent Job never ends on SQL 2008 R2



I have run into a strange problem and hoping someone may have a suggestion.

We have a set of SSIS packages that are developed in BIDS 2005. The packages are all executed by SQL Agent Jobs on a scheduled basis. These packages have been running on numerous SQL 2005 instances for the last year or so with no issues being experienced.

We recently has our first customer start running SQL 2008 R2 and we have internally upgraded to SQL 2008 R2 in our development environment. We are not converting the packages to SQL 2008 as we still need to support customers who are running SQL 2005. The problem is that in both our dev environment and at this particular customer, we have seem a random issue where the SQL Agent job never seems to end. Most of our jobs normally execute in less than 2 minutes, but occasionally (one or twice a week) a random job will just not end until we manually cancel it. We don't know if it is the SSIS package that is not ending or the SQL job.

We never experienced this on SQL 2005.

Anyone have any ideas what may be causing this? Any suggestions for troubleshooting?

Any help will be much appreciated


Craig Bryden - Please mark correct answers

SSIS package on standalone server invoked by SQL Server Agent raises DTS_E_PRODUCTLEVELTOLOW


I have developed an SSIS 2005 package but the client was reluctant to install SSIS on their SQL Server and instead installed it (SSIS) on an IIS server which doesn't have SQL Server on it all. Microsoft documentation surprisingly indicated this would work. http://technet.microsoft.com/en-gb/library/cc966389.aspx states "SSIS packages can also be run on systems where SQL Server is not installed. However, the .NET Framework and the SSIS runtime must be installed in order to execute the packages on systems that do not have SQL Server 2005 installed."

The package can be run from within BIDS and by invoking from the SSIS server fine and runs to completion. It fails when invoked by the SQL Server Agent on the actual SQL Server (i.e. NOT the SSIS server) (which IS Enterprise version) and raises a DTS_E_PRODUCTLEVELTOLOW error. This is the third "step" out of three and is a fairly basic data flow task where the results of a SQL Server proceedure are output to a text file.

I have logging on which doesn't tell me anything more than I already knew
OnError,<ServerName>,<SQLServerAgentUser>,<PackageName>,{C88F42DE-E3A8-40CB-A140-F10E270C5F1F},{1D2B421E-E067-4458-A908-23C7F081EF28},9/30/2010 4:52:21 PM,9/30/2010 4:52:21 PM,-1073450754,0x,

SQL Agent - Running SSIS Package - Windows Server 2008/Windows 7 - Messenger Services


We have tried to run a SQL job for SSIS Package. But it failed during the execution with the error "[364] The Messenger service has not been started - NetSend notifications will not be sent". But in the job, no notifications have been configured and in windows server 2008/Windows-7 there is no messenger service at all.

When we search for the the messenger service in windows 7/windows server 2008, we found that service is not in both the OS and it was replaced by msg.exe. 

Any help or workaround would be appreciated.

Thanks in advance.

PS: The normal SQL jobs other than SSIS are working fine without any error.

Vijay Pandurangan

Measure groups not being processed when SSIS package run by agent job - why?



We have an SSIS package that builds a large analysis services cube by doing a "full process" of the cube.  The SSIS package normally is executed by a scheduled agent job.  This arangement has been in production for a year and had worked well until recently.  We use SQL Server 2008 and Windows Server 2008.

Recently, the SSIS package has been having problems when run by the agent job.  The agent job executes the SSIS package and the agent job reports that the job ended successfully.  However, the cube has not been usable because the measure group partitions are indicated as "unprocessed".  We also have determined that the pre-existing fact files in the ".prt" folder are being deleted by the SSIS package but are not being recreated by the package as they should.

This doesn't happen if we execute the SSIS package directly.  If we open the Integration Services server and run the cube-builder package, everything performs as it always has and the cube is fully processed.  When the SSIS package is run directly, it executes significantly longer than if run through the agent job, and we are convinced this is because the measure groups are being created.  We have repeated our testing enough to know that the measure groups always are proc

Error Running SSIS Package as SQL Agent Job loading a file from network drive, works fine in bids an

Having problems reading an excel file from a networked drive while running a package as a SQL Agent job step as a type "SQL Server Integration Services Package". I have agent running as an AD account, and I can use the execute package utility logged in as this account and it works. When I log into the server as this account, I can see the network drive, read/write/create to folders on the drive but when I try to run the agent job it cant find the file. I tried using the unc path in the config file rather than a mapped drive, still no luck. If I use a local drive (c) in the config file, the job works fine so it has to do with the network drive. Running windows server 2003 standard R2, SQL Server 2005 standard sp3. Any ideas out there?

SSIS Fails intermittently when scheduled as a SQL Agent Job.


I have several SSIS jobs that I have created and they operate just fine when running manually or even when the job is scheduled.  Lately, I have been seeing some intermittent failures in these jobs and am having a difficult time in understanding how to resolve this issue.    All Jobs runs consistently overnight with no issues and start to fail intermittently as the load on the server increases.

Attached is the output from a successful job and a failed job.

Any Suggestions?

--Successful Run

Date                      4/8/2011 9:45:01 AM

Log                         Job History (WFM_TCSDATA_EXP_LAS_15)


Step ID                 1

Server                   DNC-UCCECUIS04

Job Name                      &nb

Issue in SQL server agent job running SSIS package containing a batch script.


I have a package that I have created that runs locally and as package on the server that has a batch script with in it and it run correctly when manually run. But when I create a sql server agent job it runs the job but doesn't execute the package but returns with a successful run. I have setup a proxy account trying to run it with this. I have given the permission to the batch script to the file that the SQL service account. I have tried everything I can think of even setting the package creator to the same name as the service account in the package, and even doing a dtexec command from the SQL job. If I run the dtexec from a cmd prompt the package runs. Need help please thanks.



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

Error while executing a package through SQL Server Agent

Hi All,   I have a ssis package. It has 3 tasks, first task updates a record in Oracle database to set an Indicator to “Y”, second task process SSAS Cube and the third again updates the same record and sets the indicator to “N”. This package has 2 data sources, one is Oracle and the other is Analysis Services. I gave the credentials for the Oracle and use NT Authority for Analysis services.   When I am executing from BIDS package is executing successfully. But when I am calling the job to execute this package its throwing me the error. Below is the error.     Message Executed as user: User\Username. ...ion 9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:57:46 AM  Error: 2010-08-27 10:57:46.79     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2010-08-27 10:57:47.16     Code: 0xC0202009     Source: Ord_test Connection manager &q

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
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