How can I eliminate false “connection string format is not valid” messages from SSIS log?

Question

I have a project that uses the SSIS catalog with project-level connection managers. The connection managers' connection strings are overridden at the server using a project parameters file, to enable easily repointing the whole project to different data sources by setting different connection strings.

Here's the issue:

1. It works. No real connection problems or failures.

2. It bloats the log tables horribly in the catalog because upon each package execution I get:

   120 30 Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

For every connection manager, in every package. I think this has to be a false error that gets triggered when the connection string is overridden with the (correct) values in the param file.

Is there something wrong here? Some way to suppress those messages?

Answer 1

If you use the Excel connection manager for example, you should set the ExcelFilePath property as an Expression instead of the ConnectionString !

Answer 2

You need to use this format when you are configuring de params

See image bellow

Answer 3

If you are using XML file package configuration, then make sure you type the values proper within tags. There shouldn't be missing semicolon within the string of subtags. And,there should not be extra spaces. For e.g if you are using like below in the ConfiguredValue tag, see the semicolons between each properties.

Data Source=SERVERNAME;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

If you miss the semicolon like below :(between Data Source and Initial Catalog properties)

Data Source=SERVERNAME Initial Catalog=DatabaseName

Then it will show error.