From SQL server version 2012 onwards there is an in-built mechanism to publish SSIS package result set into SQL view
1. Requires SSIS package to be designed with Data Streaming Destination.
This destination component needs to be separately downloaded in 2012 and 2014. From 2016, it is part of standard toolbox item.
2. Running Data Feed Publish Wizard
i) Creates a linked server – SSISOLEDB.
ii) Creates a SQL view in the specified database.
Below are the steps to publish an SSIS Package result set as a SQL View
1 In Linked server property, do the below setting:
2 For example, below SSIS package is created that pulls data from AdventureWorks2012 database publish as view in AdventureWorksODS database
3 Publish the package to Integration services catalog
4 Run the SQL Server Integration services Data Feed Publishing wizard.
5 Perform SELECT on the view to invoke the SSIS package
6 Verify the package execution by looking into Integration services catalog reports
This feature can be used for publishing a result set consisting of data extracted from various heterogeneous data sources. This feature is preferred in cases where dynamic execution of SSIS package is needed.
SELECT’ing the View created by the Publishing Wizard dynamically invokes the SSIS package and streams the results.
This functionality is not suited for frequent or concurrent access but only for On-demand execution.
See Also :