Argument "SMTP" for option "connection" is not valid. The command line parameters are invalid
I was recently working with a client on migrating from one hosting provider to another. This involved migrating the databases to the new hosting center, including all the SQL Agent Jobs.
A number of the SQL Agent Jobs execute SSIS packages. The job for one of the critical packages was particularly problematic. For the migration, I used the deployment utility created for and deployed on the old server. The data sources for the staging and production databases, and the location of the incoming XML file and associated XSD were all stored in a dtsconfig file. The only exception was the connection for the SMTP server. For some reason, this connection was hardcoded in the package, rather than set in the configuration file.
Looking at the Data sources in SQL Agent, I tried to override the connection string with the address of the new SMTP Server.
This is what the data sources tab originally looked like:
And this is what the original command line looked like:
I updated the data source for the SMTP connection string to use the correct IP Address for the new SMTP Server:
And this was the resulting update to the command line that was made by SQL Agent:
I saw the update to the command line, with the new values for the SMTP connection and felt confident that the update was successful. However, that was short lived. The first execution of the job failed with the following error:
That was certainly not what was expected, and a bit disappointing to say the least. The updated command line as generated was:
/FILE "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages\ProviderLoad\ProviderLoad.dtsx" /CONNECTION "SMTP Connection Manager";"\"SmtpServer=192.168.33.102;UseWindowsAuthentication=False;EnableSsl=False;\"" /CHECKPOINTING OFF /REPORTING E
I reset the connection a number of times each time generating the same error. What I noticed, was that the connection string was quoted. This was the escaped quotes \” prior to SmtpServer and after EnableSsl=False;. None of the connection strings, in the dtsconfig were quoted, so on a wing and a prayer I removed the escaped quotes from the command line. By going back to the command line tab and selecting Edit the command line manually and removing the escaped quotes:
And then manually running the job yielded
I’m glad that I was able to get the package working. However, I was a bit surprised that the update generated by SQL Agent for the change to the connection string produced an invalid argument. The package has since been updated and the connection string for the SMTP Server is now set in the dtsconfig.
Thank you for the article. This solved my problem.
I am having the same issue and in the Command line is have the same escaped quotes. However when I edit, it does not save. I have scripted the job, made shanges then recreated the job with the script… with no luck.
How were you able to update the Command Line?
Which version of Sql are you using?
Answered my own question –
update msdb.dbo.sysjobsteps….
Hi Michael,
I’m glad that I was able to be somewhat of assistance. I was using SQL Server 2008R2, when I encountered the problem.
I did notice that SQL Agent sometimes acted a bit weird when I did the update. If I changed the values and ran the job the SMTP connection string was set correctly. But in some cases, if I opened the job step again the settings would revert back to the escaped values.
I hadn’t thought about updating msdb.dbo.sysjobsteps directly. Thanks for letting me know that’s how you got around the problem.
Jay