DTS Scheduled job always fails

Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.

Originally posted by multiplex77
Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions

Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.

Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.

Thanks for posting the follow-up with the solution.

dts package to check dynamic file name

Hi all,

I have created a DTS package in SQL Server that uses file system obj to list the .csv files in a folder then runs and imports it into database. The problem that I run into is - the names of the .csv files will be dynamic based on the date and at times the hour.

The question is how to check ( I believe using Active X) for a new file each day and even each hour?

A sample of what my file names are:

CVG_IDO_1_20070703.csv

CVG_IDO_2_20070703.csv

CVG_IDO_3_20070703.csv

CVG_IDO_1_20070704.csv

CVG_IDO_2_20070704.csv

CVG_IDO_3_20070704.csv

Thank you!

A bit hard to say...what constitutes a new file? Are you trying to process all files in the directory? Are those all considered "new files"?

The following article on sqldts.com may help you out, depending on what your needs are with this:

Looping, Importing, and Archiving - http://www.sqldts.com/246.aspx

-Sue

|||

i believe this is time triggered. the last part of the filename is obvoiusly the date

prior to that is a number followed by an underscore.

if this number designate the latest file then you can easily script it.

well that is if your only going to pull out the latest file.

DTS package Scheduled Job does not run correctly

Hi
I have a DTS package that is running a VB script that reads all the
files from a folder one by one and processes them. After a file is
processed - file is read and the content entered in a table in one of
the databases - the file is moved to a different location.
The DTS package runs wonderfully if I run it manually (right click
Execute Package).
I scheduled this DTS package to run every hour (right click Schedule
Package and a job was created).
The corresponding job finishes successfull, my files are even moved to
a different location but no data is entered in my table.
The SQL Agent runs under an account that is system admin for our
network.
The job is owned by the same account.
Please help.
Thanks,
CarlyAre you using workflow, in your DTS package. If you are trying to execute any
step after inserting data into table try to do it 'OnSuccess' workflow and
see if it gets executed successfully.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Carly" wrote:
> Hi
> I have a DTS package that is running a VB script that reads all the
> files from a folder one by one and processes them. After a file is
> processed - file is read and the content entered in a table in one of
> the databases - the file is moved to a different location.
> The DTS package runs wonderfully if I run it manually (right click
> Execute Package).
> I scheduled this DTS package to run every hour (right click Schedule
> Package and a job was created).
> The corresponding job finishes successfull, my files are even moved to
> a different location but no data is entered in my table.
> The SQL Agent runs under an account that is system admin for our
> network.
> The job is owned by the same account.
> Please help.
> Thanks,
> Carly
>

dts package active x script

I want to rename my file in the active x script in my DTS package and archive it in a archive folder. My code is archiving the file in the archive, but I need to rename the file too so that it has filename_today's date. I wrote the code like this

set fso = CREATEOBJECT("Scripting.FileSystemObject")

fso.MoveFile DTSGlobalVariables("FileFullName").Value ,DTSGlobalVariables("ArchiveLocation").Value

before this step I want to write

fso.renamefile(DTSGlobalVariables("FileFullName").Value DTSGlobalVariables("FileFullName").Value & now)

and after this step I want to do

fso.MoveFile DTSGlobalVariables("FileFullName").Value ,DTSGlobalVariables("ArchiveLocation").Value

so in the archive folder I can have the date of the file that has been archived.

when i trying to do this

fso.renamefile(DTSGlobalVariables("FileFullName").Value DTSGlobalVariables("FileFullName").Value & now)

I am getting an error
Cannot use parantheses while calling a sub.

Please let me know how can I do this

Off the top of my head, the first thing I would try would be this:
fso.renamefile DTSGlobalVariables("FileFullName").Value DTSGlobalVariables("FileFullName").Value & now
|||I tried this, but now I am getting the error
Object doesn't support this property or method: 'fso.RenameFile'
Please let me know how can I fix this
Any help will be greatly appreciated|||

I figured it out.
Thanks