.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 which reads excel files fails on SQL Server Agent

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


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?



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.




Loading Decimal Number From Excel into SQL Server 2005 using SSIS Package

If you have an excel spreadsheet that displays a number .02 (because it is formated) but internally it is stored as .2654329 and you want to load .2654329 how can you do that in the SSIS package?  When we load the spreadsheet it is loading .02.  Is there some kind of option in the SSIS package to tell the package to load the internal number?lcerni

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

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

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

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

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.



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

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

SSIS Deployment Reads local Environment Variable and not Server

Hi All, I have an SSIS package that uses an Environment variable to hold the connection string for the local SSISConfiguration table.  This works properly on my local machine.  However, when I import the package to Integration Services(through SSMS) it reads the environment variable from the local machine and not the server it is deployed to.  If I log on to the server remotely and do the same process it uses the the correct environment varible... (SQL Server 2005) Is there a way to deploy the package to the server and get it to use the correct Env variable without having to log-in to the server and import it?  Let me know if anyone is looking for additional information.   Thanks!

SQL Server 2008 Agent Fails to start in a Win 2008 Cluster

When I try to bring SQLAGENT Online, I get the following errors: EventID:53 [sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service.  CurrentState: 1 [sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435) [sqagtres] OnlineThread: Error 435 bringing resource online.   I just did a fresh SQL Cluster install as well.  When I installed the cluster, the service account was good to go.  I cant figure out what is going on.  SQL Server Engine starts fine.  Any ideas?

Execute SSIS PAckage on Remote Server

I've seen several posts pertaining to this topic, but none of them covers what I'm having an issue with. I'm trying to create a "master job server". Essentially, I want to control all maintenance jobs from one central location. I've created SSIS packages that handle full backups, transaction log backups, index rebuilding, etc. on all of my production boxes. I've stored those packages in SQL Server. What I'd like to do now is create a separate job for each of those tasks on one central SQL Server and run those packages from there. I've been successful in creating a test job. The only step in the job is an SSIS PAckage execution step. The step successfully found the package I want to execute on the target server. When the job ran, I got the follwoing error: The job failed.  Unable to determine if the owner (ADMINSYS\arobinsf) of job SFO2PQE03 - Database Backup (FULL) has server access (reason: Could not obtain information about Windows NT group/user 'ADMINSYS\arobinsf', error code 0x2751. [SQLSTATE 42000] (Error 15404)). I want to make sure this job is executing on the remote server and not trying to execute locally. Is there something I'm missing in the set up of this job? I should also mention that the SSIS packages were created in SQL2K5, but I'm executing them from SSMS for SQL2K8. Any help would be appreciated! Thanks! A. M. Robinson

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

Slow startup of SSIS package if started using a SQL Server job

Hello all,   We have noticed in our environment slowness when starting SSIS packages from SQL Server jobs. I did a quite detailed study on when the slowness actually occurs and what are the consequences. Here are the results.   The SSIS package execution is slow if all the following is true: The package is started from a job. If started directly as a SSIS package, the execution is fast. The job is running on a 64 bit Windows Server (SQL Server 2005 SP2). The SSIS package and the job are either on the same server or on different servers (the second server is SQL Server 2005 SP1). If the job is run on a 32-bit workstation (Windows XP SP2) the execution is fast (the SSIS package still being on the server). The package contains tasks. §         If there are no tasks, just an empty sequence container, the execution is fast. §         If a package that has no tasks has logging into the database configured, the execution is fast. §         Slowness has been verified with A) a package having a single Execute SQL statement and B) a package having a Send Mail task. It doesn't seem to matter which user account is used on when running the job.   The slowness happens in several locations, e.g. (there are also others, at least the following have been verified) There is exactly 30 seconds lag between starting the job (as seen from job history) and when PreValidate (as seen in the sy

if ssis package writes to excel file that might be open by a user

I have written a package that writes to an excel file to a network folder. Eventually this package will run as a job. What is the best way to handle the situation where someone opens the file or inadvertantly leaving it open while the package is running and and stop the package.? No one needs to modify or write anything to this file after it is finished
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