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 Permissions

We have a DTS package that was created and saved by
someone who is no longer with the company. How can we
convert this DTS package to a valid ID? ThanksHi Natalie
I am reviewing your post and our community members provide some good
suggestions to your problem. Have you try these methods? Do they resolve
problem? If you still have any questions about it, please feel free to post
message here and we are glad to help!
Looking forward to hearing from you. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sqlsql

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie