SSIS Data Streaming Destination to Power BI

Utilizing the SSIS Data Streaming Component to process Tabular Data for Power BI

Prerequisites:

  • WideWorldImporters Data warehouse Copy
  • SQL Server 2016
  • Power BI 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 Power BI
  • Query the Deployed Package Directly
  1. Build an SSIS Package with the Data Streaming Destination Component
    1. For the Data Flow Task:
      1. 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
      SELECT  
             Dimension.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. Deploy the SSIS Package to an SSIS Catalog · Deploying to the PowerBI_Deployments Folder where our SSIS Package will be housed.
    3. 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
    4. 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.
    5. Publish Settings:
      1. View: You can define your View name here.
      2. LinkedServer: The linked server that the view uses to query the SSIS Package.
      3. Use32BitRunTime: Indicates whether the packages uses 32 bit or 64 bit runtime.
      4. Timeout: Specify timeout in seconds.
        1. ADVANCED:
        2. Schema: specifies the Schema to use on the destination server.
        3. Encrypt: Specify if data should be encrypted before sending it over the network.
        4. TrustServerCertificate: When used with encrypt, enables encryption using a self-signed server certificate.

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

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

    8. Use the Published View as a Source in Power BI

      a. Click Get Data

      b. Choose SQL Server

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

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

    1. 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.
    Go back to the PowerBI report and hit Refresh

    Execute this SSISDB Query to see the newly created execution: