Data Streaming Destination
Data Streaming Destination is a new Microsoft SQL Server 2012 Integration Services (SSIS) destination component that allows the OLE DB Provider for SSIS to consume output of an SSIS package as a tabular result set. You can create a linked server that uses the OLE DB Provider for SSIS and then run a SQL query on the linked server to display data returned by the SSIS package.
For example, the following query returns output from the Package.dtsx package in the SSISPackagePublishing project in the Power BI folder of the SSIS Catalog. This query uses the linked server named [Default Linked Server for Integration Services] that in turn uses the new OLE DB Provider for SSIS. The query includes folder name, project name, and package name in the SSIS catalog. The OLE DB Provider for SSIS executes the package you specified in the query and returns the tabular result set.
SELECT * FROM OPENQUERY([Default Linked Server for Integration Services], N'Folder=Power BI;Project=SSISPackagePublishing;Package=Package.dtsx')
Microsoft SQL Server 2012 Integration Services (SSIS) Data Feed Publishing Components
SSIS Data Feed Publishing Components package includes the following components: OLE DB Provider for SSIS, Data Streaming Destination, and SSIS Package Publish Wizard. The wizard lets you publish an SSIS package as a SQL view in a SQL Server 2012 database instance. The wizard helps you with creating a linked server that uses the OLE DB Provider for SSIS and a SQL view that comprises a query on the linked server. You just run the view to query results from the SSIS package as a tabular data set.
To install SSIS Data Feed Publishing Components, download and run SSISDataFeedPublishing-<CPU Architecture>.msi file from Microsoft Download Center. Confirm that the SSISOLEDB provider is installed by using SQL Server Management Studio: Expand Server Objects, Linked Servers, Providers, and confirm that you see the SSISOLEDB provider. Double-click SSISOLEDB, enable Allow Inprocess if it is not enabled, and click OK.
Publish an SSIS package as a SQL view
The following procedure has steps to publish an SSIS package as a SQL view.
Create an SSIS package with a Data Streaming Destination component and deploy the package to the SSIS Catalog.
Run the SSIS Package Publish Wizard by running ISDataFeedPublishingWizard.exe from C:\Program Files\Microsoft SQL Server\110\DTS\Binn or by clicking Microsoft SQL Server 2012\Integration Services\Data Feed Publishing Wizard under Start\All Programs.
The wizard creates a linked server using the OLE DB Provider for SSIS (SSISOLEDB) and then creates a SQL view that consists of a query on the linked server. This query includes folder name, project name, and package name in the SSIS catalog.
Execute the SQL view in SQL Server Management Studio and review the results from the SSIS package. The view sends a query to the OLE DB Provider for SSIS via the linked server you created. The OLE DB Provider for SSIS executes the package you specified in the query and returns the tabular result set.
Important
For detailed steps, see Walkthrough: Publish an SSIS Package as a SQL View.
Expose output data from an SSIS package as an OData feed by using the Power BI Admin Center
By using the Power BI Admin Center, IT Administrators can expose data from on-premises data sources as OData feeds to users. The Power BI Admin Center, by default, allows you to register only SQL Server data sources. However, you can register SSIS packages as data sources with the portal by using new SSIS components: Data Streaming Destination and Microsoft OLE DB Provider for SQL Server Integration Services (SSISOLEDB) and expose the result data from SSIS package as an OData feed to the user.
As the Admin Center allows you to publish views in a SQL Server database, you can use the SSIS Package Publish Wizard to publish an SSIS package as a SQL view and then select the view to be included in the OData feed in the Power BI Admin Center. A data steward can consume the feed from SSIS package by using the Power Query Add-in for Excel.
For detailed walkthrough, see Publish SSIS Packages as OData Feed Sources.
In This Section
See Also
Concepts
Walkthrough: Publish an SSIS Package as a SQL View