SSIS Package not retaining parameters in SQL Server agent job

Question

I have an SSIS package stored in the Integration Services catalogs on a SQL server instance.

This package is used as a step in a SQL Server agent job.

The package has 4 required parameters.

Using SSMS on my local machine if I edit the package I can add all of these parameters and the job runs fine.

However editing the step from SSMS on the server gives the following error:

You can enter this value but every time you return to edit the step the error re-occurs and the parameter values are blank.

Because the person deploying this package will have to use SSMS on the server I could really do with working out what is causing this.

Server SQL Server: SQL Server 2012 (11.0.3128) SSMS: 11.0.3128.0 OS: Windows Server 2012 (6.2.9200)

My PC SSMS: 11.0.3128.0 OS: Windows 8.1 (6.3.9600)

Answer 1

I've discovered what the issue was.

2 of the parameters were connection strings and if a connection string contained an Application Name then the value of the preceding parameter was not being stored.

This worked fine from my machine so I'm assuming a difference in the version of SSMS client is to blame.

For the time being I have simply removed the application name from the connection strings.

Answer 2

I have spent days trying to figure out why my SSIS package ran in visual studio but failed when deployed to SSIS server. I figured out that you cannot use the work 'Connection' as part of a parameter name in your package (my parameters are package level not project level and configured in the sql agent job). In my case I have my connection strings parameterized. It caused sql agent to parse the parameter and pass the connection string incorrectly. Go figure!