Post reply

DTS_pkg Fails when run from schedule

  • July 7, 2005 at 3:36 pm

    #67728

    I have a dts package that I build and execute without problem. When I schedule it to run at specified times it fails immediately. I have other dts pkgs that work fine. ??? The difference from the others is that I am using an odbc oracle connection...? to retreive data from an oracle source. First step of pkg is to truncate the sql staging table, next import the oracle data then finally run the sql procedure to process the data. It fails immediately...possibly after the truncate but before the import.

    Any suggestions, thanks in advance,

    B

     

  • July 7, 2005 at 3:42 pm

    #572107

    What is the error recorded for the job that executes the DTS package?

    K. Brian Kelley
    @kbriankelley

  • July 7, 2005 at 5:09 pm

    #572124

    The job failed.  The Job was invoked by User sa.  The last step to run was step 1 (dts_FWFMdynamic).

    Thanks,

    B

     

     

  • July 7, 2005 at 5:16 pm

    #572126

    If you check "Show step details" and then look at what's in step 1, what does it say?

    K. Brian Kelley
    @kbriankelley

  • July 7, 2005 at 8:03 pm

    #572143

    Thank you Brian, here is the Step 1 detai:

    Executed as user: DELL\twadmin. DTSRun:  Loading...   DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   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.  Process Exit Code 1.  The step failed.

    I can glean from this that the data pump driver is the cause...but what changes between the context of me executing the dts pkg from Enterprise Mgr ... and the scheduler...I have logged in as twadmin also and executed from EM without problem...

    Thanks again

    B

     

  • July 7, 2005 at 8:28 pm

    #572147

    Keep in mind that when you execute a DTS package through Enterprise Manager, two things are true:

    1) It runs under your user context

    2) It runs on your workstation

    When a job is scheduled, it's run as a SQL Server agent job. That potentially changes the usr context and where it runs. If you're running the job with an owner that's a member of the sysadmin fixed server role (you are, since the owner is sa), then the job runs under the context of the SQL Server Agent service account (which I assume is Dell\twadmin). Also, since it is a SQL Server Agent job, the DTS package will run on the server.

    You wouldn't happen to be talking to an Oracle server, would you? If so, is the Oracle client installed on the SQL Server?

    K. Brian Kelley
    @kbriankelley

  • July 7, 2005 at 10:50 pm

    #572161

    One little give away is "Error:  -2147467259 (80004005); " this is usually a generic 'Access Denied' type message which indicates a permission problem.

     

    --------------------
    Colt 45 - the original point and click interface

  • July 8, 2005 at 7:08 am

    #572299

    Thanks guys,

    Yes it is an oracle connection and I have installed the oracle client tools. Checked the sysadmin role and added DELL\twadmin. No change on the result however. I am grasping here...is it possible that the twadmin  (a functional id) requires permission on the oracle side.... even though the odbc connection specifies an account to use? Like I say....I'm fishin now....

     

    Thanks for any ideas at all,

    B

  • July 8, 2005 at 8:06 am

    #572356

    Connecting to Oracle is a nasty piece of work, at best. Can you gain access to the SQL Server? Log in on the server and troubleshoot the ODBC connection using the network tools in the Oracle client. I have had to do that. Even though the parameters look the same, for some reason it required dinking around with the ODBC properties and the .ora configuration file.

    Shalom!,

    Michael Lee

  • July 8, 2005 at 9:07 am

    #572409

    Test the connection in the DTS by clicking the test connection button in the proprties window.  If there is a configuration error it will show up here.  I would also compare your TSNNAMES file to the one on the server where you are storing the DTS.  Make sure the Oracle DB you are trying to access exists there. 

    David

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

  • July 11, 2005 at 11:40 am

    #573028

    I'm with Michael here. If you haven't already, test the connection to Oracle from the SQL Server. If you don't have anything else, a simple VBScript program that pulls back a recordset can do the job. The reason I say this was we had the same problem where I worked and it did end up being a configuration issue to Oracle, even though the ODBC connection looked correct. At least by doing this step you can narrow down whether or not it's a SQL Server issue.

    K. Brian Kelley
    @kbriankelley

  • April 24, 2008 at 12:21 pm

    #806807

    Mr B,

    Did you find the solution to this problem? I am having the same problem here but haven't got any solution, anywhere.

    Thanks,

    Sourabh

    Thanks,
    Sourabh

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply