VSS & SSIS

[epkelly]

I saw my exact problem described in thread1555-1271319 but there were no replies yet the thread was closed. Anyone have a solution?

RECAP: We're saving our SSIS packages in Visual SourceSafe (VSS). When we develop them, things work fine, but then we have connection error issues when someone checks the package out to edit. The protection level on the SSIS is "Don't Save Sensitive".

When trying to debug the package, it says it can't connect to the server, but when we edit the connection and re-enter the password, the connection test goes fine.

 

[paulkeys]

Sounds like the issue is the package protection level. As you have it as don't save sensitive, none of the connection passwords are saved, hence you're having to re-enter them each time you want to run the package.

There are numerous schools of thought regarding best practice as far as protection levels go - but we've decided to go down the 'Encrypt All with Password' and set a generic password for all packages.

This removed the problem you're having, above, and also removed a couple of the issues around scheduling packages using SQL Server Agent

 

[epkelly]

We have re-entered the password and it still gives the error.

We have no problem running this job with SQL Server Agent.

 

[epkelly]

With a little (very little) assistance from Microsoft we have found the answer.

Each developer needs to re-create the config file then edit it to add in the Password. (Because we don't save sensitive BIDS/VS won't save the password.) This allows the 2nd developer to successfully test the package.

Steps:
1. New developer gets package from source control
2. In Package Configurations delete the existing configuration.
3. Create a new configuration, just like the one you deleted.
4. Save the package and the configuration.
5. Use notepad to add the password to the configuration.

Runs Great for the new developer.