Any suggestions? Export (0) Print
Expand All

Data Streaming Destination

 

Applies To: SQL Server 2016 Preview

The Data Streaming Destination is a SQL Server Integration Services (SSIS) destination component that lets the OLE DB Provider for SSIS 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.

In the following example 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 runs 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')

The SSIS Data Feed Publishing Components include 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 database instance. The wizard helps you with creating a linked server that uses the OLE DB Provider for SSIS and a SQL view that represents a query on the linked server. You run the view to query results from the SSIS package as a tabular data set.

To confirm that the SSISOLEDB provider is installed, in 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.

The following procedure describes the steps to publish an SSIS package as a SQL view.

  1. Create an SSIS package with a Data Streaming Destination component and deploy the package to the SSIS Catalog.

  2. Run the SSIS Package Publish Wizard by running ISDataFeedPublishingWizard.exe from C:\Program Files\Microsoft SQL Server\150\DTS\Binn or by running the Data Feed Publishing Wizard from the Start menu.

    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.

  3. 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.

System_CAPS_importantImportant

For detailed steps, see Walkthrough: Publish an SSIS Package as a SQL View.

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 the Data Streaming Destination and the 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.

The Admin Center lets you publish views in a SQL Server database. As a result, you can use the SSIS Package Publish Wizard to publish an SSIS package as a SQL view. Then you can select the view to be included in the OData feed in the Power BI Admin Center. A data steward can consume the feed from the SSIS package by using the Power Query Add-in for Excel.

For a detailed walkthrough, see Publish SSIS Packages as OData Feed Sources.

Show:
© 2016 Microsoft