Comments (4)

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.