1) It seems you are trying to connect ORACLE server from DTS. Try to check below link for help :- http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/06/30/ora-12154-tns-could-not-resolve-the-connect-identifier-specified-error-while-creating-a-linked-server-to-oracle.aspx 2) The problem could be that the package is running in 64 mode and that connection has not been defined in the tnsnames ora file. A guess.... http://msdn.microsoft.com/en-us/library/ms141766.aspx 3) Also, As mention by Uri, Checl SQL Agent service account having permissions over DTS. Regards, Rohit Garg (My Blog) This posting is provided with no warranties and confers no rights. Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.

Oracle provider for Ole DB are 32 bit providers (Check this) So you need to ensure that you are setting the "Use 32 bit" on the sql server agent job. Check this for more info.http://btsbee.wordpress.com/

I am able run dts package manually but same pacage job failing after scheduling with following error. Executed as user: domin\Sa. ...Start: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error string: ORA-12154: TNS:could not resolve the connect identifier specified Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 12154 (2F7A) Error string: ORA-12154: TNS:could not resolve the connect identifier specified Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTSStep_DTSDataP... Process Exit Code 3. The step failed. could any one please let me know the solution.

Probably the account that SQL Agent runs under does not have appropriate permission is complete the DTS.....Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/ MS SQL optimization: MS SQL Development and Optimization MS SQL Blog: Large scale of database and data cleansing MS SQL Consultants: Improves MS SQL Database Performance