Scheduling DTS package as a job

[Danster]

Gday all,

Much has already been asked and explained about why a DTS package can run successfully when manually executed, but fails when scheduled as a job. The bottom line always seems to be a permissions issue, whether it be the context of the SQL Agent service logon account, the user who created the package, the server and/or woekstation the package was created on, the list goes on.

I have been trying to run a DTS package as a job now for about two days, reading just about every article I can find and trying every combination of SQL Agent logon account, creating the package under diferent login IDs (admin, user, the service logon account, et al), changing ownership of the job, making just about every account a SQL administrator, and ensuring every i is dotted and t crossed.

I still can't bloody well get my job to run!!! Can someone puhleez just gimme a failsafe method to schedule a DTS job! Preferably via the SQL Server Agent. At the moment I'm reduced to running a BAT file with a DTSRUN command & I have that in Windows Scheduled Tasks. The problem with that is a DOS window pops up when it runs which disturbs the user and sometimes it just doesn't run at all. I've never been a great fan of scheduled tasks.

My environment is WinXP Pro. I have MSDE loaded (usually, I've actually installed a full SQL Server to see if it makes a difference) and I've created a local user account which is a member of local administrators which the SQLAgent services runs under. Ideally I want the agent service to log on using Local System becuase that tends to be the typical implementation of MSDE.

The package is doing the following: dropping tables, recreating them and then importing data to them.

This is the error I always get:
[MyAttache].[dbo].[CUSTOMER_MASTER] Step DTSRun OnStart: Create Table [MyAttache].[dbo].[INVOICE_DETAIL] Step DTSRun OnStart: Create Table [MyAttache].[dbo].[INVOICE_HEADER] Step DTSRun OnFinish: Create Table [MyAttache].[dbo].[PRODUCT_MASTER] Step DTSRun OnFinish: Crea... Process Exit Code 7. The step failed.

cheers
Danster

 

[JayKusch]

Can you enable the error logging to a file and post all of its contents. The job shows the creation of the tables but then tails off.

Thanks

J. Kusch

 

[Danster]

I've scoured BOL and looked at the options for the job, and Agent, but can't get error logging. How do I enable it?

Danster

 

[MissTipps]

Hi

You need to enable logging at the DTS package level.

Open your DTS package and select Properties from the File menu.

Select the Logging tab enable check the 'Log package execution to SQL Server' option.

Having done that, the next time the package is run you can subsequently right click the package and select 'Package Logs'. This will give you a step by step log of the package execution.

Cheers