SolvedPrivate

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

Posted on 2013-12-17
10
66 Views
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.

Thanks,
Alexis

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

Step ID		1
Server		UUXX\UUXX
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

Message
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

0
Comment
Question by:alexisbr
  • 6
  • 3
10 Comments
 
LVL 17

Expert Comment

by:dbaSQL
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.
0
 

Author Comment

by:alexisbr
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.

Thanks,
Alexis
error on execution of pkgpkg infoconnection mgr info
0
 
LVL 17

Expert Comment

by:dbaSQL
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.
0
 

Author Comment

by:alexisbr
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.
Alexis
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 350 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:
http://blog.sqlauthority.com/2011/05/23/sql-server-running-ssis-package-in-scheduled-job/


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:
 http://www.mssqltips.com/sqlservertip/1405/sql-server-integration-services-ssis-package-configuration/

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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:alexisbr
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.

Regards,
Alexis
0
 

Author Comment

by:alexisbr
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.

Thanks,
Alexis
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 150 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.

http://www.experts-exchange.com/Database/MS-SQL-Server/A_8452-Execute-SQL-Job-with-Proxy.html
0
 

Author Comment

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

Regards,
Alexis
0
 

Author Comment

by:alexisbr
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.

Alexis
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now