Use of Data Source Views in SSIS

Less often I have seen Data source Views (DSV) getting used in an SSIS solution. The biggest trait of DSV is that, once its defined, it doesn't get refreshed by itself. In a way it is desireable, and in a way it is not. This is harmful in the way that, if the underlying source structure has changed, there would be no notification to the package of the same, until and unless you explicitly hit the refresh button. But once refreshed, it catches all the changes from the underlying data source and updates the view.

The main advantage of using a DSV in a SSIS solution would be centralizing the connection logic and the scope of database objects to be used. All the packages in the solution can use the same DSV and connect to objects limited to the scope of the DSV. In case of change in logic, it would get easily cascaded to the respective packages using this DSV. Also one DSV would always run under one connection object, which would mean that by enforcing the use of DSV, uncontrolled creation of private database connections would be implicitly controlled. Also at times, it help in continued package development even if Server is offline or disconnected.

And if one needs to connect to some database objects that one would not wish to include in the DSV, a connection can always be created using the Shared Data Source created for the DSV. This would be a direct connection to the database, without any kind of filtering of database objects that a DSV would have. All in all, DSV is a good practice to centralize and control connection usage in a SSIS Solution.