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.
No comments:
Post a Comment