June 25, 2015 at 8:32 am
Hello,
I have a job scheduled that imports a table from a Oracle database. The job runs at 3am and reports success. But for some reason when i query the table to see how many records there are, I see the same row count as the day before (it should increase everyday- student enrollment). When i execute the package manually, the table updates fine.
SQL Server 2014/Win7 x64.
Thoughts?
June 25, 2015 at 8:50 am
xddevv (6/25/2015)
Hello,I have a job scheduled that imports a table from a Oracle database. The job runs at 3am and reports success. But for some reason when i query the table to see how many records there are, I see the same row count as the day before (it should increase everyday- student enrollment). When i execute the package manually, the table updates fine.
SQL Server 2014/Win7 x64.
Thoughts?
When you said "execute the package" you got me wondering about what was where? Is this an SSIS package or an Oracle package?
Assuming the job is a SQL Server job (as opposed to an Oracle job) that reports success, you have to see if there are any new rows to transfer from Oracle to SQL Server. If there are and they're not getting pulled in, is there security in play on the Oracle side? I'm asking because if you run the update as yourself and it works, but it runs as the SQL Agent user and doesn't work, does the SQL Agent user have the necessary permissions in Oracle to pull the rows you intend to pull?
June 25, 2015 at 8:58 am
Ed Wagner (6/25/2015)
xddevv (6/25/2015)
Hello,I have a job scheduled that imports a table from a Oracle database. The job runs at 3am and reports success. But for some reason when i query the table to see how many records there are, I see the same row count as the day before (it should increase everyday- student enrollment). When i execute the package manually, the table updates fine.
SQL Server 2014/Win7 x64.
Thoughts?
When you said "execute the package" you got me wondering about what was where? Is this an SSIS package or an Oracle package?
Assuming the job is a SQL Server job (as opposed to an Oracle job) that reports success, you have to see if there are any new rows to transfer from Oracle to SQL Server. If there are and they're not getting pulled in, is there security in play on the Oracle side? I'm asking because if you run the update as yourself and it works, but it runs as the SQL Agent user and doesn't work, does the SQL Agent user have the necessary permissions in Oracle to pull the rows you intend to pull?
hi, sorry i should have been more clear about that. I execute the SSIS package, not an Oracle package. As for the job, yes it is a SQL Server Job. For example, this morning I checked the Oracle database and there were 13580 rows. In the SQL Server, there were only 13403..which is the same from the yesterday. As for permissions, Im thinking the Agent should report failed if that was the case?
June 25, 2015 at 9:01 am
No problem. Since I don't know SSIS beyond how to spell it (and sometimes I get that wrong :-P) I'm going to let someone else help out here.
June 25, 2015 at 9:02 am
Job results (success/failure) can be misleading. You have to focus on what the job does to understand the success/failure result.
The issue is that the job is reporting on the success or failure of running the command.
Let's say you have a job that runs this code:
SELECT GETDATE()
If the command runs and returns GETDATE() value, it will report as successful. If it can't get the value for some reason, it will report failure.
Now, let's say you have a job that calls a stored procedure:
EXEC mystoredproc
If the stored procedure exists and is executed, the job will report success. But the stored procedure itself might not work correctly. The job can't report on that because it doesn't know the stored procedure didn't run correctly. All the job knows is that it was supposed to execute the stored procedure and it did that.
That is the same thing with SSIS packages....the job reported that it successfully started the package...it has no clue as to whether the package ran correctly.
-SQLBill
June 25, 2015 at 9:12 am
Although if the package fails, the job step will fail. However, the package won't necessarily fail just because one or more components in it do. That will depend on how the package is written.
John
June 25, 2015 at 11:03 am
I would lean heavily towards a security issue. When you manually kick off a job it runs under the context of whatever you are logged in as. When it executes on a schedule it runs under the agent accounts credentials.
As stated previously, the job reporting success does not necessarily mean the work was done; only that the job run from start to finish without an error.
Where is the package stored? is it in the catalog or the filesystem?
Ensure the agent account has permissions to execute the package wherever it is stored.
How is the Oracle connection established?
Is it being done under the context of the agent account? or a stored connection?
Check permissions there as well.
If it's possible a very quick way to check this out is to log in with the agent service account and execute the SSIS package manually(NOT the agent job, the package itself) Any errors in the execution should be easy to track down.
June 25, 2015 at 11:05 am
I would lean heavily towards a security issue. When you manually kick off a job it runs under the context of whatever you are logged in as. When it executes on a schedule it runs under the agent accounts credentials.
As stated previously, the job reporting success does not necessarily mean the work was done; only that the job run from start to finish without an error.
Where is the package stored? is it in the catalog or the filesystem?
Ensure the agent account has permissions to execute the package wherever it is stored.
How is the Oracle connection established?
Is it being done under the context of the agent account? or a stored connection?
Check permissions there as well.
If it's possible a very quick way to check this out is to log in with the agent service account and execute the SSIS package manually(NOT the agent job, the package itself) Any errors in the execution should be easy to track down.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply