June 28, 2005 at 3:55 am
I have scheduled a DTS package to run as a job. It extracts data from an Oracle database and writes to a SQL Server 2000 table. This has been running for months and nothing has changed. The package structure is:
Execute SQL Task to truncate the destination table
Transform data task from MS OLEDB provider for Oracle to SQL Server. Straight column copy - no scripts.
After the transform I have an OnSuccess Execute SQL Task and and OnFailure Execute SQL Task to log what happened.
Last night the process failed. The SQL Server Agent job history reads "Copy Data from Results to [Reporting].[dbo].[BANK_DETAILS] Step: 3000 Rows have been transformed or c... Process Exit Code 1. The step failed." There should be around 50,000 records in this transfer. However, the OnSuccess task in the package was executed, making it look as if all was well. I've looked in the SQL Server log in EM and there's nothing of note.
Can anyone give me some tips for finding out what happened? I ran the package interactively this morning and everything ran perfectly.
Thanks
Scott
--
Scott
June 28, 2005 at 5:15 am
Look to see if any jobs were running around the same time last night that could have affected the running of the package. Eg database set to read only..
June 28, 2005 at 10:23 am
No, nothing like that. I checked to see if backup schedules had been altered but nothing I can think of has changed. Obviously something has changed, otherwise the process would have run properly.
--
Scott
June 28, 2005 at 10:36 am
In that case you may be looking at insufficient disk space either for the database or the log.
If the process ran when started manually you can rule out bad data (Unless any data changed since you re-ran the package) and probably permissions as the failed package executed some records.
Barring that, it could have been something as simple as a bad write to the disk. Have you checked the sqls erver logs for that time?
June 29, 2005 at 2:07 am
I don't think it's disk space - the data and log partitions have over 20 and 40 Gb free respectively. The database is just over 1 Gb and is in simple recovery mode. I've looked at the log in Enterprise Manager and there's nothing in there apart from backup details. Happened again last night with exactly the same symptoms.
--
Scott
June 29, 2005 at 6:06 am
It's beginning to look like a permission problem to me.
When it runs interactively it runs under whatever nt account you are loggied in as.
When it runs scheduled, it runs under whatever account is used in the sql server agent.
I think permissions have changed, most likely on the oracle server. If you can logon as the sql server agent account and do a simple select and insert interactively on the server, you should get more of a clue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply