Two jobs call the exact same SSIS package. One consistently fails and the other consistently succeeds, although in rare cases the one that succeeds has failed and the one that fails has succeeded. The step was copied from one job to the other by scripting and just altering the @job_name in the call to sp_add_jobstep. Nothing was done to the SSIS pkg. The pkg ProtectionLevel property is set to EncryptSensitiveWithUserKey. If I change it to DontSaveSensitive and add a config file containing the credentials, the pkg succeeds when called from the job step where it used to fail. (see MSKB http://support.microsoft.com/kb/918760).
So we have this workaround, but why would the exact same call to SSIS succeed in one job and fail in the other? Ultimately this pkg is failing on Unable to connect to FTP server using "FTP Connection Manager". Both jobs are owned by sa and the SSIS steps in each job are running under the SQL Agent Service account. When scripted the job steps are identical. The difference between the jobs themselves is the scheduling and the order in which this particular step runs. Other SSIS steps occur in both jobs, and the commonality is that the various other SSIS steps are also hitting the same ftp server. I wonder if there's something in the threading or the duplication of the hits to the ftp server that is whacking the authentication?
Has anybody else seen something like this?