Results 1 to 9 of 9

Thread: DTS Package

  1. #1
    Join Date
    Oct 2004
    Posts
    6

    Question 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....

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Did you install oracle client and create dsn on sql server?

  3. #3
    Join Date
    Oct 2004
    Posts
    6
    I have installed oracle client in the machine running SQLServer, and created a System DSN from the ODBC Datasources.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    What's sql service account? Does the account have proper permission? Which id do you use in query analyzer?

  5. #5
    Join Date
    Oct 2004
    Posts
    6
    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....

  6. #6
    Join Date
    Oct 2004
    Posts
    6
    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...

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    Try connect to sql in query analyzer with windows authentication then run your sp to see if it works.

  8. #8
    Join Date
    Oct 2004
    Posts
    6
    i tried it earliear and same result.

  9. #9
    Join Date
    Oct 2004
    Posts
    6
    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....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •