Package That Runs From Visual Studio But Not From Agent Job

[Skittle]

I have a package on SQL Server 2005 that reads data from an AS400 and writes it to SQL Server.
It has worked fine for years. I have modified it to no longer use an old 'Execute DTS' command
and instead use SQL Server 2005 Data Reader, conversion and destination. This is a preparation step to move the package to SQL Server 2008. It works fine from Visual Studio.

I run it as an Agent job both as a scheduled event and as an interactive 'Start Job' and it fails with
'The package execution failed. The step failed.' in the history of the job.
I get no further clue what the problem is.

There is nothing on the event log of the server.
I have added a text log to the package and there is nothing written to it.
I have not spotted anything in the profiler for 'SQL Server Profiler' when the job runs.

I am at a loss as what to try next.

Can anybody advise?



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[Skittle]

Found the issues.

1) I didn't have logging turned on properly.
2) The password for the AS400 connection wasn't being saved because I didn't have ProtectionLevel set to EncryptAllWithPassword AND I seem to have to save the package with a different package password before reconfirming the connection and saving the package with a final package password different to the first package password.

It's as if the settings are only saved when I change the package password. Which is odd.





Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.