December 8, 2015 at 1:07 pm
OK - I have a pretty straight forward SSIS package. Our environment is SQL 2014 Standard SP1 on both servers.
The package executes a stored procedure on our PROD database server and puts the result set into a table. Then the data flow task merely copies the data from that table to a table on our web database server.
Here is the rub...
The package is invoked by an Agent job and runs hourly. Job completes, no errors. The package log file shows everything working correctly. But there is NO data in the web database table.
If I execute the package manually in Visual Studio using debug mode - everything works.
I re-imported the package to MSDB to make sure nothing was corrupt there and the correct version was loaded.
Job runs - no data....
I have never seen this behavior before and am a bit lost...any help would be appreciated!
And if necessary I can upload the package if that will help...
Thanks in advance.
December 8, 2015 at 1:16 pm
Are you sure the SSIS package is inserting the data in the correct server?
If the package log indicates it's inserting rows and the destination server doesn't show any activity, then it's not pointing to the correct server.
December 8, 2015 at 1:22 pm
I have triple checked the OLDDB connections. And like I said - if I run it step by step in Visual Studio it works absolutely fine. And after I ran it manually, I saved it and re-imported it into MSDB and double checked the Agent job to make sure it was pointing to the correct package.
It's flipping bizarre...
December 8, 2015 at 1:27 pm
Is this an old package that used to work or a new one that has not worked yet?
When a package works correctly when run manually, but doesn't work correctly when run run as a SQL Agent job, the first thing you should look at is the rights for the account used by SQL Server Agent.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 8, 2015 at 1:31 pm
OK guys - I am officially stupid today.
Someone had gone into the Agent job and manually changed the data sources in the job step that executes the package and had it pointed to a test server.
When I find the culprit they are going to die...slowly and painfully 🙂
Thanks for the responses...thought I was losing my mind for a minute.
December 8, 2015 at 9:14 pm
It happens.
Normally in such cases, I find my best old mate SQL Profiler very useful. Completely decoupled from the application layers and capable of tracking things happening in SQL server
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply