SSIS Data Streaming Destination to PowerBI

Utilizing the SSIS Data Streaming Component to process Tabular Data for PowerBI

Prerequisites:

• WideWorldImporters Data warehouse Copy

• SQL Server 2016

• PowerBI Desktop

• Visual Studio 2015

Data Streaming Destination Description: “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.”

Data Feed Publishing Description: “The 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.”

In this tutorial, we will:

• Build an SSIS Package with the Data Streaming Destination Component

• Deploy the SSIS Package to the SSIS Catalog

• Publish the SSIS Package as a SQL View using the SSIS Package Publish Wizard

• Use the Published View as a Source in PowerBI

• Query the Deployed Package Directly

1.      Build an SSIS Package with the Data Streaming Destination Component

1.      For the Data Flow Task:

a.       Create an OLE DB Source that points to our WideWorldImporters Datawarehouse:

Use the following SQL in the SQL Command for Data access mode:

   

 

 

1.     Create a Data Streaming Destination Task

SELECTDimension.Customer.Customer,

Dimension.Customer.Category,

[Buying Group],[Postal Code],

COUNT(f_o.[Stock Item Key]) AS TotalItemsPurchased,

SUM([Recommended Retail Price]) AS TotalCostPerItem

 FROM fact.[Order] f_o

INNER JOIN Dimension.City ON City.[City Key] = f_o.[City Key]

INNER JOIN Dimension.Customer ON Customer.[Customer Key] = f_o.[Customer Key]

INNER JOIN Dimension.[Stock Item] ON [Stock Item].[Stock Item Key] = f_o.[Stock Item Key]

GROUP BY Customer ,

 Category ,

 [Buying Group] ,

 [Postal Code]

ORDER BY Dimension.Customer.Customer

2.     Create a Data Streaming Destination Task

 

blog03.jpg

3.     Deploy the SSIS Package to an SSIS Catalog

·         Deploying to the PowerBI_Deployments Folder where our SSIS Package will be housed.

4.  Publish the SSIS Package as a SQL View using the SSIS Package Publish Wizard

From the Windows\Programs menu open:

Microsoft SQL Server 2016 \ SQL Server 2016 Data Feed Publishing Wizard

5.       Package Settings: Specify the Deployed SSIS Package with the SSIS Datastream Transformation

a.       Parameters:

    i.      You can define any Package, Project and Connection Parameters here.

*Recommended to bind sensitive parameter to environment variables to ensure the value of the sensitive parameter is not store din plain text format in the SQL view that will be create by the view.

6.       Publish Settings:

a.       View: You can define your View name here.

b.       LinkedServer: The linked server that the view uses to query the SSIS Package.

c.        Use32BitRunTime: Indicates whether the packages uses 32 bit or 64 bit runtime.

d.       Timeout: Specify timeout in seconds.

e.       ADVANCED:

i.      Schema: specifies the Schema to use on the destination server.

ii.      Encrypt: Specify if data should be encrypted before sending it over the network.

iii.      TrustServerCertificate: When used with encrypt, enables encryption using a self-signed server certificate.

7.       Validation and Deployment

a.       Hit Next to Validate

i.      If Validation on Configuration of the linked Server fails:

1.       In SSMS Browse to Server Objects > Linked Servers > Providers > SSISOLEDB

a.       Right Click, hit properties,*** Enable Allow Inprocess***

 b.       When Validation is complete click Publish

 8.       Test/Query the SSIS View

a.       From the Database we specified in our data feed, right click the view and select top 1000 rows to verify our feed is working properly. 

b.

b.

c.

c.

9.     Use the Published View as a Source in PowerBI

 

a.       Click Get Data

 

b.       Choose SQL Server

10.       Below you can see the VIEW displayed under the Database in which we deployed to.

10.       Below you can see the VIEW displayed under the Database in which we deployed to.

11.       Click LOAD 12. I then went ahead and created some simple PowerBI Dashboards off the View source. Everytime this report is refreshed or executed that in turn executes the SSIS Package

11.       Click LOAD

12. I then went ahead and created some simple PowerBI Dashboards off the View source. Everytime this report is refreshed or executed that in turn executes the SSIS Package

13.

13.

 

14.       Verify the SSISDB Metadata to see that the SSIS Package executed

 

a.       Use the following SQL to query the SSISDB Catalog meta-data.

 

  USE SSISDB;

  SELECT    es.execution_id ,

            folder_name ,

            project_name ,

            package_name ,

            reference_id ,

            reference_type ,

            environment_folder_name ,

            environment_name ,

            es.start_time ,

            es.end_time ,

            es.execution_duration

  FROM      internal.executions

            INNER JOIN internal.executable_statistics es ON es.execution_id = executions.execution_id;

This query will display your SSIS Folder, Project, Package, Environment(if applicable), and some execution stats on that item.

We can see some previous executions when I was testing the initial build of the report.

  15.       Go back to the PowerBI report and hit Refresh     16.       Execute this SSISDB Query to see the newly created execution:

 

15.       Go back to the PowerBI report and hit Refresh

 

 

16.       Execute this SSISDB Query to see the newly created execution: