October 2, 2015 at 3:15 pm
Hello all,
I've seen this problem posted before, but not quite the same way I'm seeing it.
I have a package that basically copies data from one Excel file to another Excel file. It starts with an Excel Source, goes thru a Split to get rid of some empty records, and then a data conversion, and then to the Excel Destination. This package has been running for about a year with no issues. About 2 weeks ago, the destination file was turning up empty (other than the column headers which come from the template).
The fun part is, if I run the package locally, everything works normally. If I run the package on the server thru SSMS (in Integration Services), everything works normally (runs under my account). If I run the package using a SQL Agent Job (running under a service account), then I get nothing. No errors, no data in the output file.
At a loss for ideas, I started tinkering with the amount of data in the file just to see if I could get it to work right (although this isn't much data to start with). I ran a test with just a couple of hundred records and it worked normally. Then I ran some tests starting with the full data set, deleting data out of the source file 1000 records at a time, until I could get the package to work. Starting with over 13,000 records, when I got down to 8,000 records, the package would copy the data normally when running under the SQL Agent Job. Not making sense to me yet.
I went back and added extensive logging to the package so I could see what it is doing when running under the SQL Server Agent. Even when I get no data in my output file, the logging says the Excel Destination wrote the number of records that were in the source file (over 13,000). Yet, the destination file is empty.
The only thing I know that has changed recently is, some patches were applied to the server. Otherwise, nobody is admitting to changing anything.
Like I said, this is not a new package...it worked fine until recently.
So in summary...
1) Runs locally on my machine (under my account)
2) Runs on the server when ran directly in Integration Services (under my account)
3) Does not run on the server using the SQL Agent Job (under service account) unless I reduce the number of records in the source file
Anyone have any clues on what might be going on here or how to further trouble-shoot this?
Thanks!
October 6, 2015 at 6:14 am
hi
just check while running with the SQL agent the ID which you use has all permission to do
October 6, 2015 at 6:16 am
if u get same problem call the sp from the sql agent inside the Sp u call the pkg , i think that will work
October 6, 2015 at 6:29 am
Thanks for the input!
Service account used by SQL Server Agent has the same permissions that my personal account has, as far as I can tell.
October 6, 2015 at 6:40 am
maynardsammons (10/6/2015)
Thanks for the input!Service account used by SQL Server Agent has the same permissions that my personal account has, as far as I can tell.
is the destination a network share?
typically the core issue is permissions:
if you are writing to a network share, a mapped drive, or anything under a specific user account's folder structure(IE \\USERS\LOWELL\DESKTOP),
the standard accounts don't have access(they never login to the domain, so no access to network resources. they don't get mapped drives as a result, and individual folders are usually exclusive to users and admins.
you might need to either create a credential + proxy, to use specific accoutn permissions, orcreate a domain account,and have the services run under that acocunt.
Lowell
October 6, 2015 at 7:09 am
The destination is a folder on the database server. That is an interesting point though, let me check that something didn't happen to prevent the service account from writing to that folder.
Thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply