4

I was not able to find a resource discussing this from the SQL Agent and SSIS Catalog.

I'm getting behavior that was unexpected when running a package from SQL Agent. I believe that the issue is that I specified a different InitialCatalog property in the sql agent configuration than I used in my connection string environment variable.

Agent Connection Properties

Here you can see my connection string is different.

enter image description here

I was expecting that the ConnectionString would override the InitialCatalog, but it does not seem to be the case.

Which one really overrides the other? Can that behavior be altered? It does not appear that I can omit an InitialCatalog value, but is it actually possible?

Stephen Lloyd
  • 733
  • 8
  • 21
  • I was running into this the other day and it seems contrary to my 2005/2008 experience. The full connection string appears to be overridden by the individual components. I didn't prove it out beyond a shadow of a doubt but it's on my eventual TODO (which is why this is a comment and not an answer) – billinkc Mar 18 '15 at 15:24
  • @billinkc I'm crushed you didn't have the answer, but thanks for taking a look. Well, in my experience with this, I can confirm that these Property values outside the connection string override their "counterparts" in the connection string. My new understanding is that the ConnectionString property is simply used to fill in non-standard (or otherwise unspecified) connection properties at runtime. – Stephen Lloyd Mar 19 '15 at 20:19

1 Answers1

1

If you modify a value in the properties windows for a connection manager, it will update the connection string immediately.

The SQL Server Agent has no connection strings (it runs different types of jobs/steps). In this case, it calls DTSExec, which in turn executes the SSIS package.

If you want to override the a property for a connection manager dynamically, then you may want to look at these other questions:

SSIS how to set connection string dynamically from a config file

Quickly changing SSIS-packages data source parameters for easy migration

Community
  • 1
  • 1
Ricardo C
  • 2,205
  • 20
  • 24