DTS Package

Hello everyone!
I am having problems executing a DTS package from a StoredProcedure. This DTS Package consists of two steps. The first one delete a table in SqlServer to prepare for 2nd step. The 2nd step reads data from an Oracle server and insert data into the previous table. When I run this package from within the Data Transaformation Services section it works. when i try to run it from SQLAnalyzer using a stored procedure I get the following error.

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL


I am running this from the box where this is going to run. ALso, I have installed all the Oracle client software and ODBC driver in this box. I have created DSN connections using the Oracle driver and tested it, and it connects.
any suggestiion on how to solve the issue happening here will be appreciated it. thanks in advance....

Did you install oracle client and create dsn on sql server?

I have installed oracle client in the machine running SQLServer, and created a System DSN from the ODBC Datasources.

What's sql service account? Does the account have proper permission? Which id do you use in query analyzer?

when I execute it from SQLServer Analyzer I use the sa account. When I execute it from inside the DataTransformation services my "myNTaccount" account is the creator however I logged in to Enterprise manager with a different "NTsql-account", so I would say I execute the package with the "NTsql-account" from the EM. I am not sure if I am clear here....

Adding to this topic... also, I am able to run the package from the command prompt using this sintax, in the server

dtsrun /S server /E /N mypackage


I am not sure why here is able to run it...

Try connect to sql in query analyzer with windows authentication then run your sp to see if it works.

i tried it earliear and same result.

hello! It started to work now I tried this , that I actually got from another forum

1)Opened up Windows Explorer
2)Navigated to Oracle\ora92
3)Right clicked and selected properties
4)Clicked on Securities tab
5)Clicked on Authenticated Users
6)Unchecked Read and Execute
7)Rechecked Read and Execute
8)Clicked ok to close window
9)Restarted web server (entire box)
10)No longer get the error message

thanks everyone....