DTS package import issues

[aolb]

I have 2 dts packages, one dts package imports data from a sql server db and the other imports data from an ORACLE db.
Both dts packages can be executed from sql enterprise manager.
The problem is that when I schedule the packages with a job, the SQL Server import dts package works fine but the ORACLE dts package just hangs.
When I take out the ORACLE connection in that dts package the job executes.
So why is the ORACLE connection causing the job to hang but not a problem for the dts package to be executed through the sql enterprise manager?

Also when I execute the ORACLE dts import package from a command line it executes.
Makes no difference if I generate the command line or write it myself.
I Reun the job as SA

Any ideas?

 

[MissTipps]

Hi

When you run the DTS package, are you actually executing it on the server or are you running it on your local machine. If so, try checking out the Oracle ODBC/OLEDB driver versions between your local machine and the server. It could be that there's a problem with the version on the server because obviously when you schedule the package, it executes on the server itself.

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[aolb]

A good idea but we built an ran the dts package on the server.

It looks to me like a security problem. I log onto the server with domain security. The job has SQL Server uses the sa logon, with administrator rights. The connection to ORACLE as an ORACLE account with read only rights.

The dts package can be executed through enterprise manager and from the command line but can't be run from a job.

When I remove the ORACLE connection from the dts package I can execute the job.

I have the same dts package running on another server with that server I am using a machine logon.

Local policy prevents me from using a machine logon in this instant.

Any thoughts appriciated

 

[fuzzyocelot]

Hi aolb,

We have a scheduled job that imports data from an Oracle db on a daily basis, and it works for us. I don't know how you have your job set up but here's how we did it. The step that runs the import package from the Oracle db is set up as follows:

Type: Operating System Command (CmdExec)
Command: DTSRun ...

The command is a DTSRun command string that's been encrypted (generated by the DTSRun utility).

Hope that helps!
Rebecca

 

[aolb]

We have sorted it out now. We had to create a windows account with the correct permissions and then go into the SQL Server Agent Properties, select Jobs System and set up a proxy account using the window account.

Don't understand why it did previously work with a SQL Server import and not an ORACLE import!!


Thanks for your surgestions

 

[unclerico]

aolb, what account is the SQL Server Agent running under? If you execute the package interactively either from the command line or from EM, it will execute using your credentials and I'm sure that you can execute SELECT commands from the Oracle DB. When you schedule it to run, it will run under the context of the SQL Server Agent account and if that account does not have the neccesary permissions it will bomb, hence why you used a proxy account, I'm sure it has the correct permissions.

 

[aolb]

I have set up a windows account with Administrator privileges as my SQL Server Agent proxy account. When I schedule the job it uses that account and executes the dts package as expected.

Thanks