Unanswered: Schedul DTS as a Job problem (hackneyed but still interesting for you)
I know that this issue is hackneyed but I already tried almost everything.
The DTS were created in the EM (Enterprise Manager) of the server without passwords, it transform data from a fox pro .DBF file (the file exists in the server and it has an ODBC system DSN) and save it to a table in the SQLServer 7. thats all. As always, it runs fine form EM but when I try to schedul it as a job... I receive this error:
... DTSRun: Executing... DTSRun OnStart: Copy Data from detafact to [sicproduccion].[dbo].[tDetallesFacturaTIJ] Step DTSRun OnStart: Copy Data from facturas to [sicproduccion].[dbo].[tFacturasTIJ] Step DTSRun OnStart: Copy Data from pagofact to [sicproduccion].[dbo].[tPagosFacturaTIJ] Step DTSRun OnError: Copy Data from facturas to [sicproduccion].[dbo].[tFacturasTIJ] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Da... Process Exit Code 3. The step failed.
The server is a windows 2k domain controller and the windows user logon is a member of the administrator group.
Before posting this I tried:
1.-Reading the Microsoft article Q269074 http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074
2.-Assigning SQLAgentCmdExec account to Administrators(DOMAIN/Builtin), Users(DOMAIN/Builtin), Domain Admins(DOMAIN/Users) and Domain Users(DOMAIN/Users) groups.
The rights and permissions are assigned from Active Directory Users and Computers.
3.-The DTS were created by the same windows user logon
4.-In the properties window for the job in General tab, the owner is the windows user logon (DOMAIN\user) Also I tried 'sa'
5.-In the Steps tab of the same window I tried both the default encrypted syntax of EM and the following syntax:
DTSRUN /S[.] /Usa /P /N[DTSNAME],
DTSRUN /S[.] /Usa /P /N[GUID_DTS],
DTSRUN /S[.] /Usa /E /N[DTSNAME],
Also replacing [.] for [SERVERNAME] and
DTSRUN /S[SERVERNAME] /U[User] /P[Pwd] /N[DTSNAME]
Obtaining the same error
Have you gone over to the Server's console logged into the the Server and run the DTS package from the Server. Make sure to login as the account the package runs as. This way you will be running the package interactively but from the exact Windows environment that the package runs in batch mode.
1.-The package was created with using an NT authenticated login member of the Administrators Group and is the same login always for create and execute them also is the same used to turn on the SQL Agent... in conclusion, is the same user always
2.-I have it running using the TechScheduler executing a .bat file with the DTSRUN /F[dts_path] /N[dts_name] command line but, it launches a msdos window every 10 minutes (this is why I need to turn on the job scheduling) and it also demonstrates that something is wrong because it runs fine in EM and in msdos with a .bat file but fails scheduling it as a job.
Again, any suggestions gratefully received
The only thing I can suggest and this is what I''ve done in the past is setup the command in Scheduler to output to a log file and see the environment that the job is running in.
c:\temp\MyDTSRUN.bat > c:\temp\MyDTSRUN.log
Inside c:\temp\MyDTSRUN.bat add to the top
The SET command will print out your environment, make sure USERNAME is who you think it is. DIR to see the directory you are currently in and DIR dts_path to verify that the job can see the directory. DTSRUN with no argument, to verify DTSRUN is in your path.