0

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:

enter image description here

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)

  • Why must the user run from the Server rather than the much better practice of running from his local machine? Also, have you verified that the SSIS installation on your local machine and on the server are identical? – RLF Apr 10 '15 at 15:13
  • I'm not sure being able to directly access a production server from your local machine is best practice and regardless it is not allowed by the security policy. I'll look further into version numbers. – Fishcake Apr 13 '15 at 8:11
  • The server I'm deploying to directly from my machine is a replica of the production server which is why in this instance I can deploy directly to it. – Fishcake Apr 13 '15 at 8:13
0

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.

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.