SSIS jobs fails but Dtexec works

Question

Post reply

SSIS jobs fails but Dtexec works

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

What package protection level do you use?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

ss-457805 SSCertifiable Points: 5872 More actions · Answer 2

Have you tried it by changing the security level of the Package to:

Protectionlevel = Don'tSaveSensitive

blog: http://sarveshsingh.com Twitter: @sarveshsing

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 3

The consultant used 'EncryptSensitiveWithPassword'.

The things I do not understand is that it was working fine for months and then just stopped. I can only think that he must have changed something and 'forgot' to tell me.

How can I get the job to run as an Integrated Services package and add the password?

Madame Artois

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

S Hodkinson (12/14/2009)
The consultant used 'EncryptSensitiveWithPassword'.
The things I do not understand is that it was working fine for months and then just stopped. I can only think that he must have changed something and 'forgot' to tell me.
How can I get the job to run as an Integrated Services package and add the password?

You have to include the following parameter to your command line:

/DE <password>

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 5

I have double checked that the decrypt and password are there.

I have enabled the jobs for the early hours of the morning. I will report back in the morning.

Thanks for your help

Madame Artois

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 6

This gets weirdier and weirdier. The first and second job succeeded but the last one failed. I checked the command line and it did have the encryption password.

Anyway I accepted all three again and will wait again for the morning. I have sequenced the 'package' jobs with the final one as the 'utility' job. The users do have data when they arrive in the morning so I can battle on sorting this out.

Just had a thought, though. I have sysadmin rights on the server but I am not a domain admin; I have the client SQL Server on my desktop. I can log on to the server using a domain admin login and use SQL Server directly. It is when I log directly onto the server (using the domain account)that things appear to OK. Does Integration Services really need a domain admin account to work efficiently? Does the user creating the job have an effect?

Madame Artois

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

S Hodkinson (12/15/2009)
This gets weirdier and weirdier. The first and second job succeeded but the last one failed. I checked the command line and it did have the encryption password.
Anyway I accepted all three again and will wait again for the morning. I have sequenced the 'package' jobs with the final one as the 'utility' job. The users do have data when they arrive in the morning so I can battle on sorting this out.
Just had a thought, though. I have sysadmin rights on the server but I am not a domain admin; I have the client SQL Server on my desktop. I can log on to the server using a domain admin login and use SQL Server directly. It is when I log directly onto the server (using the domain account)that things appear to OK. Does Integration Services really need a domain admin account to work efficiently? Does the user creating the job have an effect?

Indeed if you use EncryptSensitiveWithUserKey package protection level, the account creating the package is very important. Make sure all your packages use EncryptAllSensitiveWithPassword package protection level and the password is the same.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 8

I think I am beginning to get somewhere now as I suspect that I know what happened.

I will report back in the morning

Madame Artois

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 9

I am pleased to report that all packages ran successfully this morning. I think there was a contradiction in privileges between the user who created the packages and the user who was trying to run the jobs. When I altered the user who was running the jobs, to one with greater privileges, the jobs run successfully.

Phew security, what a nightmare. Thanks for all your help.:-)

Madame Artois

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 10

Good to hear that, thanks for updating here.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty