CODEWITHSUNDEEP
sql-servervisual-studiossisbusiness-intelligence
Chris Adragna
Chris Adragna

Reputation: 632

How do I deploy a change to connection manager properties in a previously deployed SSIS Package?

I had a business logic change that increased the width of a column being output to a flat file.

In the SSIS project, I made the necessary changes, most significantly to the Flat File Connection Manager/Advanced: Output Column Width (increased from 10 to 14).

I deployed the package from Visual Studio to the Integration Services Catalog. To do this I right-clicked the package in the Solution Explorer and selected Deploy. For what it's worth, I also did a new build of the project.

In Visual Studio:

The file is output as expected. There are no errors and no warnings.

In Deployment:

The package throws an error, due to truncation -- like the connection manager was not updated.

I did not know that a change to the connection manager properties would not be part of the deployment. I have tested this new knowledge of mine by making another change to the connection. For the test: I changed the Text Qualifier property from to double quote. Then I deployed the package again, and in the Integration Services Catalog I clicked Configure Package/Connection Managers, and just as I suspected, the Text Qualifier property remained equal to in the deployed package.

How do I make a change to the connection manager and get it deployed?

Upvotes: 3

Views: 7916

Answers (1)

Wolfgang Kais
Wolfgang Kais

Reputation: 4090

There are two possible scopes of connection managers in SSIS: They can be configured at the package level or at the project level. If you defined the connection manager in question at the project level (as an indicator, you will see "(Project)" behind the connection manager name in the connection manager pane of your design window), it won't get updated in the catalog when just deploying a single package. You will have to deploy the whole project to get the connection manager updated on the server. If you do not want this, you can still change the connection manager scope via right-click to make it a package-level connection manager. Then, you can deploy that single package. But of course, this will not affect other deployed packages that were also using that connection manager, they will still use the "old" version. But I think that you didn't use that connection manager in other packages, because otherwise you wouldn't have thought to deploy the updated connection manager while deploying that special package. In future projects, define the connection managers from the start at the approprite level.

Upvotes: 5

Related Questions