Unanswered: Data Transformation Services Revisited
I am trying to export a table from SQL Server to Oracle8i home edition. I want to schedule the export package to run at a certain time. The package gets created, but when the time is reached for the package to execute, it doesn't execute. There are no errors at all. It just doesn't run. The package is there because I see it in the Data Transformation Services->Local Packages list. Any ideas? Thanks.
Originally posted by exdter
When I put the line in a command prompt, everything worked. The table was created in Oracle.
If the job reliably fails when automated and reliably works when executed manually, it is almost definitely a permissions issue. Right-click the job, do "Start Job", wait a few minutes, do a refresh, and check if the job does reliably fail when scheduled.
Check who owns that job. Try changing that to sa or local Admin. You also may want to try deleting and recreating the job (I've seen that help).
The scheduled job will run under the credentials the SQL Server Agent has if using Windows integrated security.
For the connection with Oracle I guess you set that in the Oracle connection in the DTS package. (I don't know much about Oracle security).