Work offline with SSIS package

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:

Work offline option in SSIS menu

Work offline option in SSIS menu

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.

Work offline option connection level

Work offline option connection level

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.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.

Leave a comment

Your email address will not be published. Required fields are marked *