SSIS DTS Package Security Woes and Fixes

In a previous blog I talked about how to get security settings to persist across servers in DTS packages made using Business Intelligence Studio for SSIS.

Well it seems sometimes no matter how many rebuilds and hard coding of connection strings into the package you do, things just don't go as planned. The dreaded "AcquireConnection" message comes up and your package fails to execute.

There is hope though. From a maitenance perspective this may in fact be a better way of doing things assuming access to SSIS is limited to the correct server team guys and gals.

In the "Run Package" dialog box you can click on the "Connection Managers" tab. This lists the various connections in your package. You can override the settings for these connections here.



In a future post I will let you know how this integrates with the SQL Server Agent jobs.