Publish SSIS Packages as OData Feed Sources

An IT Administrator, by using the Power BI for Office 365 Admin Center, can expose data from on-premises data sources as OData feeds to users. The 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.

Data Streaming Destination is a new 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.

Publish SSIS Packages - Conceptual Diagram

As the Admin Center allows you to publish views in a SQL Server database, you can create a view in a SQL Server database that consists of the query as described above, create a data source to point to the SQL Server database, and then select the view to be exposed as an OData feed. A data steward can consume the feed from SSIS package by using the Power Query Add-in for Excel.

After you deploy an SSIS package to the SSIS Catalog, you can run the SSIS Data Feed Publishing Wizard, which creates a linked server using the OLE DB Provider for SSIS (SSISOLEDB) and then create 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.

At runtime, the view sends the 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 to the query.

This walkthrough has the following steps with detailed instructions on registering an SSIS package as a data source with the Admin Center.

Prerequisites

You must have the following software installed on your computer to perform this walkthrough.

  1. SQL Server 2012 RTM or later with SQL Server Integration Services.
  2. SQL Server Data Tools for Visual Studio 2010 or SQL Server Data Tools – Business Intelligence for Visual Studio 2012.
  3. SSIS Data Feed Publishing Components. Download and run SSISDataFeedPublishing-<CPU Architecture>.msi file from the Download Center. Confirm 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.

Step 1: Build and Deploy SSIS Project to the SSIS Catalog

In this step, you will create an SSIS package that extracts data from an SSIS supported data source (we use SQL Server database here as an example) and outputs the data using a Data Streaming Destination component, and build and deploy the SSIS project to the SSIS catalog.

  1. Launch SQL Server Data Tools. On the Start menu, point to All Programs, point to Microsoft SQL Server, and click SQL Server Data Tools.
  2. Create a new Integration Services project.
    1. Click File on the menu bar, point to New, and click Project.
    2. Expand Business Intelligence and click Integration Services in the tree view.
    3. Select Integration Services Project if it is not already selected.
    4. Specify SSISPackagePublishing for the project name.
    5. Specify a location for the project.
    6. Click OK to close the New Project dialog box.
  3. Drag the Data Flow component from SSIS Toolbox to the design surface of the Control Flow tab.
  4. Double-click Data Flow component in the Control Flow to open Data Flow Designer.
  5. Drag a source component from the toolbox to the Data Flow Designer and configure it to extract data from a data source.
    1. For the purpose of the walkthrough, create a test database: TestDB with a table: Employee. Create the table with three columns, ID, FirstName and LastName.
    2. Set ID as a primary key.
    3. Insert two records with the following data:: (1, John, Doe), (2, Jane, Doe).
    4. Drag the OLE DB Source component from the SSIS Toolbox on to the Data Flow Designer.
    5. Configure the component to extract data from the Employee table in the TestDB database. Select (local).TestDB for OLE DB connection manager, Table or view for Data access mode, and [dbo].[Employee] for Name of the table or the view.

SSIS - OLE DB Connection Manager Settings

  1. Now, drag the Data Streaming Destination from the toolbox to the data flow. You should find this component in the Common section of the toolbox.
  2. Connect the OLE DB Source component in the data flow to the Data Streaming Destination component.
  3. Build and deploy the SSIS project to SSIS Catalog.
    1. Click Project on the menu bar and click Deploy.
    2. Follow the instructions on the wizard to deploy the project to the SSIS Catalog in the local database server. The following example uses Power BI as the folder name and SSISPackagePublishing as the project name in the SSIS catalog.

Step 2: Use the SSIS Data Feed Publishing Wizard to Publish SSIS Package as a SQL View

In this step, you will use the SQL Server Integration Services (SSIS) Data Feed Publishing Wizard to publish the SSIS package as a view in a SQL Server database. The output data of the package can be consumed by querying this view.

The SSIS Data Feed Publishing 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.

At runtime, the view sends the 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 to the query.

  1. Launch SSIS Data Feed Publishing 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.
  2. Click Next on the Welcome page.

SSIS Package Publishing Wizard - Introduction Page

  1. On the Package Settings page, perform the following tasks:
  1. Type the name of the SQL Server instance that contains the SSIS catalog or click Browse to select the server.

SSIS Package Publish Wizard - Package Settings

  1. Click Browse next to Path field, browse the SSIS catalog, select the SSIS package you want to publish (for example: SSISDB->SSISPackagePublishing->Package.dtsx), and click OK.

SSIS Package Publishing Wizard - Browse for Package

  1. Using the Package Parameters, Project Parameters, and Connection Managers tabs at the bottom of the page, enter values for any package parameters, project parameters, or connection manager settings for the package. You can also indicate an environment reference to be used for the package execution and bind project/package parameters to environment variables.

We recommend that you bind sensitive parameters to environment variables. This is to ensure that the value of a sensitive parameter is not stored in the plain text format in the SQL view created by the wizard.

  1. Click Next to switch the Publish Settings page.
  1. On the Publish Settings page, perform the following tasks:
  1. Select the database for the view to be created.

SSIS Package Publish Wizard - Publish Settings

  1. Type a name for the view. You can also select an existing view from the drop-down list.
  2. In the Settings list, specify a name of the linked server to be associated with the view. If linked server does not already exist, the wizard will create the linked server before creating the view. You can also set values for User32BitRuntime and Timeout values here.
  3. Click the Advanced button. You should see the Advanced Settings dialog box.
  4. On the Advanced Settings dialog box, do the following:
  1. Specify the database schema in which you want the view to be created (Schema field).
  2. Specify whether data should be encrypted before sending it over the network (Encrypt field). See Using Encryption Without Validation topic for more details about this setting and the TrustServerCertificate setting.
  3. Specify whether a self-signed server certificate can be used when the encryption setting is enabled (TrustServerCertificate field).
  4. Click OK to close the Advanced Settings dialog box.
  1. Click Next to switch to the Validation page.
  1. On the Validation page, review the results from the validating the values for all the settings. In the following example, you see a warning on the existence of linked server because the linked server does not exist on the selected SQL Server instance. If you see Error for Result, hover the mouse over Error and you will see the details about the error. For example, if you had not enabled the Allow inprocess option for the SSISOLEDB provider, you will get an error on the Configuration of Linked Server action.

SSIS Package Publish Wizard - Validation Page

  1. To save this report as an XML file, click Save Report.
  2. Click Next on the Validation page to switch to the Summary page.
  3. Review your selection in the Summary page and click Publish to start the publishing process, which will create the linked server if it does not exist already on the server and then create the view using the linked server.

SSIS Package Publish Wizard - Summary Page

The output data of the package can now be queried by executing the following SQL statement against the TestDB database: SELECT * FROM [SSISPackageView].

  1. To save this report as an XML file, click Save Report.
  2. Review the results from the publishing process and click Finish to close the wizard.

 Note    Following data types are not supported in this release: text, ntext, image, nvarchar(max), varchar(max), and varbinary(max).

Step 3: Create a Data Source using the Power BI Admin Center

Follow the steps in the Creating Data Sources section in the Admin Center User Guide to:

  1. Create a data source and enable OData feed in the Eldorado Admin Center. This data source should point to the database that contains the view you have published in the previous step. See Create a Data Source and Enable OData Feed in the Power BI Admin Center for detailed steps. After you enable the OData Feed for the data source, select the view to be included in the OData data feed.

 Important    Windows authentication is required to execute SSIS package. Therefore, you must select Windows Authentication when creating the data source. SQL Authentication is not supported.

  1. Configure Data Management Gateway using the Data Management Gateway Configuration Manager to allow access to OData feeds using HTTP/HTTPS protocol. See Data Management Gateway Configuration Manager section of Install, Configure, and Register Data Management Gateway for details.
  2. Access the OData data feed from Power Query Add-in for Excel. See Access OData Feeds from Power Query Add-in for Excel for details.

Step 4: Verify the SSIS Package Execution

In this step, you will verify that the SSIS package was executed.

  1. In SQL Server Management Studio, expand Integration Services Catalogs, expand SSISDB, expand folder in which your SSIS project exists, expand Projects, expand your project node, and expand Packages.
  2. Right-click on the SSIS package, and click point to Reports, point to Standard Reports, and click All Executions.
  3. You should see the SSIS package execution in the report.

 Note    On a Windows Vista Service Pack 2 computer, you may see two SSIS package executions in the report, a successful one and a failed one. Ignore the failed one as it is caused by a known issue in this release.

Appendix

Create a Linked Server using the OLE DB Provider for SSIS

Create a linked server using the OLE DB Provider for SSIS (SSISOLEDB) by running the following query in SQL Server Management Studio.

USE [master]
GO

EXEC sp_addlinkedserver
@server = N'SSISFeedServer',
@srvproduct = N'Microsoft',
@provider = N'SSISOLEDB',
@datasrc = N'.'
GO

Create a View using Linked Server and SSIS Catalog Information

In this step, you will create a SQL view that runs a query on the linked server you created in the previous section. The query will include folder name, project name, and package name in the SSIS Catalog.

At runtime, when the view is executed, the linked server query that is defined in the view starts the SSIS package specified in the query and receives the package output as a tabular result set.

  1. Before creating the view, type and run the following query in the new query window. OPENQUERY is a rowset function supported by SQL Server. It executes the specified pass-through query on the specified linked server using the OLE DB Provider associated with the linked server. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. See OPENQUERY documentation on MSDN Library for more information. See Appendix for information specific to using OPENQUERY with OLE DB Provider for SSIS.

SELECT * FROM OPENQUERY(SSISFeedServer,N'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')
GO

 Note    Update folder name, project name, and package name if needed. If the OPENQUERY function fails, in the SQL Server Management Studio, expand Server Objects, expand Linked Servers, expand Providers, and double click SSISOLEDB provider, and ensure that the Allow inprocess option is enabled.

The automatically added _ID column serves as the EntityKey for the output data from the data flow when the data is consumed as OData feed by other applications. In the current context, the data is consumed as an OData feed by the Data Management Gateway.

If you want to specify a custom name for the EntityKey column name, you can double-click on the Data Streaming Destination component in SQL Server Data Tools, and edit the value of the IDColumnName property under the Custom Properties tab. The default value of the column name is _ID.

SSIS Package - IDColumnName Property

  1. Create a view in the database (TestDB for the purpose of this walkthrough) by running the following query.

USE [TestDB]
GO

CREATE VIEW SSISPackageView AS
SELECT * FROM OPENQUERY(SSISFeedServer, 'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')
GO

  1. Test the view by running the following query.

SELECT * FROM SSISPackageView

OPENQUERY Function

The syntax for OPENQUERY function is:

SELECT * FROM OPENQUERY(<LinkedServer Name>, N’Folder=<Folder Name from SSIS Catalog>; Project=<SSIS Project Name>; Package=<SSIS Package Name>; Use32BitRuntime=[True | False];Parameters=”<parameter_name_1>=<value1>; parameter_name_2=<value2>”;Timeout=<Number of Seconds>;’)

Folder, Project, and Package parameters are mandatory. Use32BitRuntime, Timeout and Parameters are optional.

The value of Use32BitRuntime can be 0, 1, true, or false. It indicates whether the package should run with 32-bit runtime (1 or true) when the platform of SQL Server is 64-bit.

Timeout indicates the number of seconds that the OLE DB provider for SSIS can wait before new data arrives from the SSIS package. By default, the timeout is 60 seconds. You can specify an integer value for the timeout between 20 and 32000.

Parameters contain the value of both package parameters and project parameters. The rules for parameters are same as parameters in DTExec.

The following list specifies the special characters allowed in the query clause:

  • Single Quote (‘) – This is supported by the standard OPENQUERY. If you want to use the single quote in the query clause, use two single quotes (‘’).
  • Double-Quote (“) – The parameters part of the query is enclosed in double-quotes. If a parameter value itself contains a double-quote, use the escape character. For example: \”.
  • Left and right square brackets ([ and ]) – These characters are used to indicate leading/rear spaces. For example, “[ some spaces ]” represents the string “ some spaces ” with two leading spaces and one rear space. If these characters themselves are used in the query clause, they must be escaped. For example: \[ and \].
  • Forward Slash (\) – Every \ used in the query clause must use escape character. For example, \\ is evaluated as \ in the query clause.

Forward Slash (\) – Every \ used in the query clause must use escape character. For example, \\ is evaluated as \ in the query clause.

See Also

 
 
Applies to:
Power BI for Office 365