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.If you scheduled the package to run you should see it Enterprise Manager under the server in Management/SQL Server Agent/Jobs.
There you will see last run status among other things.

Of course the SQL Server Agent needs to be running.|||Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
Thanks|||Originally posted by exdter
Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
Thanks

Do a refresh on the jobs. If it isn't there, then that is your problem. After you right-click the DTS package and do schedule, you should see the job in the "Jobs" panel.|||I don't think you would get an error that it is not running

In Enterprise Manage you see if it is running by the small green "play" icon on it. You can start it from there with right clicking on it.
Or you can start it with Service Manager.

It would probably be a good thing to set it to always start automatically.|||The jobs were there, all with error messages. The Server Agent is running as well.|||Then you know the schedule works at least :-)

Can you run the task manually, and does it work without errors then?

Originally posted by exdter
The jobs were there, all with error messages. The Server Agent is running as well.|||Originally posted by exdter
The jobs were there, all with error messages. The Server Agent is running as well.

OK, that's a different error than what you were seeing before (no jobs).

Try double-clicking the job, click the "steps" tab, select the only step, click edit, cut and paste the command to a command prompt and see what you get.

That will probably fail and give you a more detailed error. Otherwise, you probably have a permissions issue.|||Manually the job works. Thanks for your help.|||When I put the line in a command prompt, everything worked. The table was created in Oracle.|||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).|||I created the job logged in as administrator. I deleted and recreated the job more than once, and it still doesn't run on a schedule. Thanks for your 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).|||I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.|||Originally posted by exdter
I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.

This is an almost sure sign of a permissions issue. Did you look at the Windows Event Log? The error message for scheduled jobs should be in there.|||I couldn't find anything in there about it.|||Just to make sure: Have you installed (Oracle) SQL*Net on the SQL Server? SQL*Net is similar to Client Connectivity in SQL Server. It installs all the underlying DLL's, executables, and such that any application (DTS included) would need to connect to an Oracle Database.

If so, can you do a TNSPing to the SID that you are trying to reach? DTS is horribly client bound, so running the package manually would mean that you are running the package from your lap/desk-top.|||I didn't have the SQL* Net installed. Thanks.