Issue starting SSIS package with parameters

Question

I've read a lot of topic related to invoking ssis package with dtexec or sp_start_job, but can't launch my specific package.

Package: mail task with 1 parameter in mail body, SMTP connection with windows authentication. 1 Parameter. Launch from Designer - OK.

Deploying to server, creating a job with a step of recent package, using proxy with my login. Launch job using gui(in which i can change the parameter) - OK.

using dtexec form cmd: dtexec /is %package.dtsx /X86 /ser %servername /par "$Project::ProjectParameter(Int32)";

Launch - fail Launch history shows error in mail task: client was not authenticated.

Question: how to run correctly using dtexec or how to use sp_start_job with parameter(without creating a param_table for job)

It sounds like a permissions issue (like the account that dtexec is running under doesn't pass the Windows Authentication that is configured for your SMTP connection). Are you running dtexec from your local machine? If you hardcode the mail body and run dtexec (without passing the paramter) do you get the same error? If so, that would rule out that the parameter is causing the issue. — gannaway
@gannaway Running from local. Same with and with no params. I think there is only one issue that i need to fix: proxy to launch package. Because job with this package(only difference, that it has proxy credential) runs ok. — bklnf

sam sam · Accepted Answer · 2013-12-27T17:23:04

Never tried to pass parameters to SP_START_JOB, best practice would be to create a config file where you can add the variable and value and then attach it to the job and then run the below statement

exec msdb.dbo.sp_start_job 'JOB NAME'

or if you still want comand line stament try this, go to the step inside the JOB and then "Set Values" tab add the variable and its values and go to "command line" tab and it will show you the script which u can use to run in command line

Issue starting SSIS package with parameters

1 Answers