SQL Server Performance Forum – Threads Archive
DTS package Error from Scheduled Job
Hi, I have created a DTS package to create a table, get the source data from Oracle and transform it into the created table. This works fine when I execute the package from "Local Packages". However, when I try to schedule the job through "SQL Server Agent -> Jobs", I get and error: "DTSRun: Loading… DTSRun: Executing… DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnError: DTSStep_DTSDataPumpTask_4, Error = -2147467259 (80004005) Error string: Specified driver could not be loaded due to system error 126 (Oracle in Client90). Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 160 (A0) Error string: Specified driver could not be loaded due to system error 126 (Oracle in Client90). Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: . The step failed." Any hints/tips greatly appreciated. Ian1.
Are the Oracle client tools (SQLNET) installed on the sql server? 2.
Are you using a DSN? If so verify that the sql agent service account has rights to read the DSN registry keys. A link I found:
http://www.experts-exchange.com/Databases/Oracle/Q_11393978.html
Which version of Oracle tools used?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi Argyle, The Oracle client tools are installed. Yes, I am using a DSN. How do I check that SQL Agent Service account has rights to read DSN registry keys? Thanks for the help, Ian
Using REGEDT32 tools check the permissions for the account used for SQLAgent.
Refer to this KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;229929 about registry keys and keywords for DSN etc. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi Satya, Thanks for your tips. But being new to this, where in the registry do I find the permission settings for the account used by SQLAgent? At present SQLAgent is started using the LocalSystem account as is SQLServer, how do I check if this LocalSystem account has permissions to the DSN. Thanks again, Ian
IF its used with Local system account then no permission set to access registry, use LOCAL account which is part of administrator group on the server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>