Sometimes it happens that we need to open an SSIS package which contains connections which are not accessible or not allowed to be accessed due to some security reasons. In this post “Work offline with SSIS package”, we are going to learn that how we can open, read, and modify the components of an SSIS package when its connection managers are not able to connect to the underlying data source. This feature can be highly useful when it comes that there is a developer who is not authorized to access the underlying data source being used in the package but he needs to read some column mappings information from the source and destinations being used in the package.
Whenever we open an SSIS package in Visual Studio designer, the designer tries to connect to the data sources being used by the package to verify the metadata. And if it fails to connect to the underlying data source for any connection for any reason, the metadata information for that source or destination object becomes inaccessible. In other words, we would not be able to access the column mappings of the source or destination object. When we click on the Columns tab of the source or destination object, it throws an error as “The AcquireConnection method call to the connection manager failed with error code”.
So, in order to work with an SSIS package which has this kind of limitations, we can use the “Work Offline” option available in SSIS menu in menu bar. Have a look at the below image:
In earlier versions of SSIS (till 2008), this option was only available at package level but since SSIS 2012 release, this option is also available at the individual connection level also.
The connection managers for which we set the “Work Offline” property to true do not try to validate its metadata. Also, when we close and reopen the package, this property gets automatically resets to work online.
Thanks for the reading. Please share your input in the comment section of this post.
very good