CODEWITHSUNDEEP
sqlssissql-server-2012agent
user3665980
user3665980

Reputation: 3

SQL 2012 - SSIS Package not populating Text file when scheduled

I'm working on SQL 2012 Enterprise and I have a set of SSIS package exports which push data out to text files on a shared network folder. The packages aren't complex and under most circumstances they work perfectly. The problem I'm facing is that they do not work when scheduled - despite reporting that they have succeded.

Let me explain the scenarios;
1) When run manually from within BIDS, they work correctly, txt files are created and populated with data.
2) When deployed to the SSISDB and run from the Agent job they also work as expected - files are created and populate with data.
3) When the Agent job is scheduled to run in the evening, the job runs and reports success. The files are created but the data is not populated.

I've checked the reports on the Integration Services Catalogs and compared the messages line by line from the OnInformation. Both runs reports that the Flat File Destination wrote xxxx rows.

The data is there, the Agent account has the correct access. I cannot fathom why the job works when started manually, but behaves differently when scheduled.

Has anyone seen anything similar? It feels like a very strange bug....

Kind Regards,

James

Upvotes: 0

Views: 1047

Answers (2)

Pepys
Pepys

Reputation: 991

I had the same problem with Excel files. It was permission rights.

What worked for me was adding the SERVICE account to the folder's security tab. Then the SQL Agent can access the files.

Folder security

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

Make sure that the account you have set up as the proxy for the SSIS task has read/write access to the file.

IMX, when you run an SQL Agent job manually, it appears to use the context of the user who initiates it in some way. I always assumed it was a side effect of impersonation. It's only when it actually runs with the schedule that everything uses the assigned security rights.

Additionally, I think when the user starts the job, the user is impersonating the proxy, but when the job is run via the schedule, the agent's account is impersonating the proxy. Make sure the service account has the right to impersonate the proxy. Take a look at sp_grant_login_to_proxy and sp_enum_login_for_proxy.

Here's a link that roughly goes through the process: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

I also recall this video being useful: http://msdn.microsoft.com/en-us/library/dd440761(v=SQL.100).aspx

Upvotes: 1

Related Questions