SSIS pkg successful when run manually but won't run from sql server agent

Posted on 2013-12-17
Medium Priority
Last Modified: 2016-02-10
Hi.  I created an SSIS package to load data from csv files into a sql server 2008 table.  I created the SSIS package directly on the server using Visual Studio 2008.  The SSIS package and the load folder all reside on the same server on the local drives (no mapped drives).  

When I execute the package directly by double-clicking on it and pushing the Execute button, it works perfectly.   Several weeks ago I created a SQL Agent job to execute this job every morning at 7am and it was working fine for at least one week.  Then, it stopped loading the data but with no error and saying the load was successful.   It is very strange.

I have several other similar SSIS programs running from the same folder on the same server with no problem (these have been running well for several years).  Just this program seems to have stop loading data but with no errors.  I've pasted below what I see in the job history but it didn't do anything and it seems as though the step is being ignored/bypassed even though it used to work.  I can't think of anything I changed.  I had moved on to another project but found out about the problem when users running reports told me data was missing.

I have read many posts about permissions and tried changing the owner to "sa" but that also didn't work. I am still getting no errors but data isn't loading.

I do have a local copy of SQL Server Mgmt Studio on my computer which I didn't have when I created the load programs from years ago that work fine but I did not create the SSIS program locally on my computer.  I did log onto the server like I did years ago and create the SSIS programs that way.  I probably did initially create the SQL Agent job locally so thinking that might be the issue, I created a new SQL Agent job directly on the server but that didn't help either.

Clearly I am missing something.   Not sure what else to try.  Oh yeah I also rebooted the server, just in case.


Date		12/17/2013 2:57:01 PM
Log		Job History (Run_Quality_RGA_Data_Load_112613)

Step ID		1
Job Name		Run_Quality_RGA_Data_Load_112613
Step Name		Run SSIS Pkg to load RGA data from CSV files
Duration		00:00:01
Sql Severity		0
Sql Message ID		0
Operator Emailed		
Operator Net sent		
Operator Paged		
Retries Attempted		0

Executed as user: UXX\SYSTEM. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  2:57:02 PM  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  2:57:02 PM  Finished: 2:57:02 PM  Elapsed:  0.281 seconds.  The package executed successfully.  The step succeeded.

Open in new window

Question by:alexisbr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
LVL 17

Expert Comment

ID: 39725781
There are many known oddities with running the packages through SQL Server Agent, but all of these would come with specific errors.  Are you not receiving any errors from the Agent or SSIS?

The elapsed time and the DTSER_SUCCESS message in your suggest that your package did something.  Unfortunately, I can't go much further without more information on your pkg definition.  Something else that would be helpful is the informational outputs from each of the components within the pkg.  Can you define a log on the package within SSIS (SQL server information services), and tell SQL to return the informational output for each step?  Then we can allow the agent to call the package, and translate what is written to the log.  That will give us a good picture of what's going on.

Author Comment

ID: 39727302
Thanks for your help.  I executed the pkg again directly from its folder and this time I noticed an error that I am not too sure about.  I could have sworn the last time I ran it that I saw no errors but I could be wrong.

The error is relating to the type on the connection mgr.  It's strange because even though there's an error, the pkg is still running fine and the data is being loaded perfectly.  But maybe SQL Agent can't handle the error and is ignoring it.  

I've attached 3 screenshots.  One is the error I see when I run manually.  The other 2 show some info on the pkg when it's in edit mode.

error on execution of pkgpkg infoconnection mgr info
LVL 17

Expert Comment

ID: 39727441
Your posts are very helpful, but they do suggest that something has been changed since before, when you say the package was running fine.  If the package connections are not successful, the Agent is not going to ignore these problems.  If your Agent or SSIS hasn't reported this in the logging, it may be that your logging is not defined well.

Regardless, we need to forget about the Agent job for now.  Let's focus on the package by itself.  Go into BIDS, right click your package and run it.  If it fails with the same error, we will recreate your connection manager, and go from there.

It sounds like a lot, but it really isn't.  Many times the fastest fix is to simply recreate the SSIS package components, and start anew.  

I am offline a lot today.  I will try to get back soon, to check status.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

ID: 39727949
You have a good point.  I looked at the package only and stopped thinking about the agent for now.  I realized I must have done something to corrupt the pkg so I had a backup from when I completed creating the package.  I restored the backup and now I am not getting any errors when executing the package itself by double-clicking on it.  The package runs fine that way and does what it's supposed to do.  

I created a new sql agent job but it still didn't load.  No errors.  "Success" like before but no data loaded.

You have given me a few ideas that I am currently working on.  Will give you updates later.

Thanks again.
LVL 17

Accepted Solution

dbaSQL earned 1400 total points
ID: 39728033
Very good progress.  I am mobile right now, but very quickly, this is what I would do, were I in your shoes.

1. In BIDS, save package to your SQL Server AND to file.
When you save the pkg to both file and server you should do so under a SQL account with appropriate privileges on your server/database.  I build all of my servers with a 'SSISuser' SQL account.  It has higher level privileges within all of my targeted user databases -- db_datareader, db_datawriter, ddl_admin.  This is the SQL login that you will use when you're saving to the file system and to the server.
2. Delete/Rename the existing Agent Jobs
3. Create your new Agent job, presumably your first step is to fire the package.  There are several ways to do this, but you can follow this example:

NOTE:  I always use configuration within m SSIS pkgs.  ALWAYS.  Within which, I specify the server/database connection string and the login/password.  There are a million other flags, but these are the ones I use, so that I don't have to add user logins and passwords in to SQL Server Agent jobs.  I strongly recommend you consider doing the same with your packages.  This is a good writeup to review:

I apologize.  I am still mobile and I have to move again.  I won't be back until much later tonight.  But I will be back.

Author Comment

ID: 39729223
Thanks so much.  I am still playing with this a bit but I am not sure what you mean by #1, "Save pkg to SQL Server and to file".  I couldn't find where to do that.  Currently the file is saved  as a dtsx file on the server.

I have super user privileges on the server and also access to be sa if I need to.  Another point to consider is that I wrote several other SSIS (dtsx) packages a few years ago that are still running on this server and they run fine a few times a day.  And they exist in the same folder as this new package and I was the one who created them.  In fact, the package I am currently working on is a copy of one of those previous packages.  And, again, this new package worked fine a few weeks ago.  I am trying to remember what I might have done or what could have changed.


Author Comment

ID: 39729937
I figured out how to save the pkg to SQL Server and created a new SQL Agent job following the instructions on the link (which is exactly how I have been doing them) and called the pkg saved in sql server.  Again, same problem.  Executing the package is the only step I am putting in the job for now.  I still get a "success" message but nothing has happened.

LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 600 total points
ID: 40010046
In that case make sure the SQL Agent user has permissions to read/write to that file.

Give proper permission to that file or

Execute SQL Job using proxy.


Author Comment

ID: 40021153
Thanks for the suggestion.  I have to try this out and will get back to you.


Author Comment

ID: 40061980
I want to thank everyone who helped me with this issue.  We are having permissions issue on the server and that has to be the problem.  I am going to try everything suggested here to resolve the problem.  Thanks again.


Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question