CODEWITHSUNDEEP
sql-servercommand-linessissql-agent-job
e4rthdog
e4rthdog

Reputation: 5233

SSIS scheduled job does not retain command line changes

I changed all my PackageProtection settings in my SSIS packages to EncryptWithPassword.

This means that i have to change my command line parameters on the SQL agent job that runs them, and put the password with the /DECRYPT switch.

I just did that. Created a job like:

/****** Object:  Step [Load Staging]    Script Date: 14/10/2014 13:02:20 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Load Staging', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'SSIS', 
        @command=N'/DTS "\"\File System\INT02-LoadStaging\"" /SERVER appsrv02 /CHECKPOINTING OFF /REPORTING E /DECRYPT xxxxxxx', 
        @database_name=N'master', 
        @flags=8
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

When i run the script the job is created , but when i go to see the command line it still is in the default setting.

I run the job and i get an error for my data sources password.

I have read that it is a bug in older SQL versions where you cannot alter the command line through SSMS, and that is the reason that i created through script.

Is there anything can do or i will have to enable package configuration files for all my packages?

Upvotes: 0

Views: 1133

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

Editing SSIS job details through Management Studio is unfortunately not reliable -- I've personally had trouble with round-tripping complicated command lines that contain escaped strings (/CONNECTION "\"name with spaces\"";) where the command line is simply mangled when you open it, and this mangled version is what gets saved back if you click "OK".

That particular problem appears to have been fixed in SSMS 2014, but it's still true that the editor will parse the command line and rebuild it according to what it thinks is correct, silently discarding options it doesn't like. You can work around this by only scripting job modifications directly and not using the editor, or (I suppose) sink effort into how it thinks you should be doing things instead and stick to that. I personally prefer the former, but I should add that I have no experience using encryption, so for all I know using /DECRYPT might be a bad idea -- the general point stands, though.

Upvotes: 1

Related Questions