Tag Archives

4 Articles

Publish SSIS Packages as OData Feed Sources with Power BI

With the upcoming advent of Power BI on-premises data sources can be published as OData feeds to users. By default, you can only register SQL Server data sources here. With a clever trick you can register SSIS packages as data sources here, using the new Data Streaming Destination and Microsoft OLE DB Provider for SQL Server Integration Services (SSISOLEDB).

How it works is that you use a special destination (Data Streaming Destination) in the SSIS package. Then, we you publish the package to the SSIS Catalog you can run the SSIS Data Feed Publishing Wizard which enables you to create a view. That view can then be published as OData feed as can any other view.

In this post we will walk through an example. You will need SQL 2012 and the SSIS Data Feed Publishing Components, available here: http://www.microsoft.com/en-us/download/confirmation.aspx?id=39931 (pre-release).

  1. Install the SSIS Data Feed Publishing Components. After you installed it make sure it is successfully installed and configured by using SQL Server Management Studio. Connect to your SQL Server, expand Server Objects, Linked Servers, Providers and makes sure you see the SSISOLEDB provider. Double click SSISOLEDB here and enable ‘Allow Inprocess’ if it is not enabled and click OK.



  2. Create a new Integration Services project (or of course you can edit an existing one), I’ll work with a new one just to keep it simple. I named my project SSISODataPublishing. Drop a Data Flow task onto the control flow and double-click it.
  3. In the data flow, define a source (I connected to an AdventureWorks database). Also specify the table you want to publish as OData feed (I used DimProduct).
  4. Now drag and drop the Data Streaming Destination to the data flow. Of course in real life the package would not be this simple: you would get the data from sources, transform it and then finally use the destination. For demonstration purposes I am keeping it simple here. Connect the OLE DB Source to the new Data Streaming Destination like so:

  5. What I noticed is that the Data Streaming Destination does not support all data types. For example, LargePhoto is a varbinary and is not supported. To fix this we need to unselect it from our OLE DB source. Do this by editing the source. On the Columns page unselect LargePhoto and click OK:
  6. Now there is nothing else to do but to deploy the package. Right-click the project in the Solution Explorer and choose Deploy. Click Next and use browse to enter the destination SQL server name to publish the project to the Integration Services catalog. Then click browse to enter the path. If you run into this information message you have not correctly set up the SSISDB catalog yet:

    To fix this go to SQL Server Management Studio, connect to your SQL Server, right-click Integration Services Catalog and choose ‘Create Catalog…’. In the next dialog enter a password for the encryption key protection and click OK.

    After this return to your Integration Services Deployment Wizard and choose browse to enter the path again. Now you can create a new folder in the catalog and select it. Click OK to enter the path:

    Click Next and choose Deploy to finish the deployment.


  7. When the deployment finishes, make sure all items have resulted in Passed and no errors or warning exist. Then click Close.
  8. Now that the deployment has been done, we need to publish the package as view in a SQL Server database. To do that we need to use a new wizard called the SSIS Data Feed Publishing Wizard, which can be started by running the ISDataFeedPublishingWizard.exe from C:\Program Files\Microsoft SQL Server\110\DTS\Binn or just search for Data Feed Publishing Wizard in Windows.
  9. In the wizard, click Next on the welcome page.
  10. On the package settings page enter the name of the server where you deployed the package and enter the package. If you have any parameters configured you can enter values here or bind them to environment variables:

    When done click ‘Next’.

  11. On the Publish Settings page select a database where you want the view for the SSIS package to be created and enter a name for the view. Make sure to choose a LinkedServer. If it does not exist already, the wizard will create it. Note you can also specify Use32BitRuntime and Timeout options here:
  12. Before clicking ‘Next’, choose ‘Advanced’ and specify the Schema you want to use, if you want to use encryption to encrypt data before sending it over the network and what certificate you will use for the encryption. I left everything at the default (schema dbo, no encryption). Click ‘Next’.
  13. On the validation page you will see a warning if no linked server exists yet. That is not a problem since the wizard will create the server automatically. Make sure that no other warnings exist or errors exist and click ‘Next’.
  14. Review the selections you made and click ‘Publish’ to actually create the view.
  15. Review the progress and make sure no warnings or errors exist. Note that the query you will need to use to get the output data of the package is displayed at the bottom of this screen:
  16. Click ‘Finish’.
  17. Test the view by running the query above in SQL Server Management Studio:
  1. So far all is OK. Last step is to create a data source and enable the OData feed in the PowerBI Admin center. You will have to have a working Data Management Gateway configured in order to do this. When done you can use the feed in Power Query. See my post on how to configure the Data Management Gateway.

Working with Azure and HDInsight from SSIS

A while ago a whitepaper was published on how to work with Azure and HDInsight (Hadoop on Azure) from SSIS. In that whitepaper some code samples were given. That code is also available here (including some components): http://code.msdn.microsoft.com/SSIS-Packages-Sample-for-2ffd9c32 . After you have downloaded the zip make sure you have Visual Studio installed, start the Developer Command for Visual Studio as Administrator and run the ‘deploy_SSIS_packages_and_components.bat’ file which is included in the zip. This will install some of the DLLs included into the GAC. Then, you can open the solution in Visual Studio.

The solution includes the following sample SSIS packages:

  • PigSqoopPackage: shows how to work with Pig and SQOOP tasks
  • HadoopJobAutomation: shows how to start jobs on Hadoop and how to consume results
  • ComplexSourceDestination: shows how to get data from Azure Blog Storage and save the results into various targets, including Azure Blob Storage.
  • BlobSourceTestPackage: sample package showing how to read data from Azure Blob Storage.
  • BlobDestinationTestPackage: sample package showing how to write data to Azure Blob Storage.

The first two packages essentially contain some script tasks with complete samples on how to work with Piq, SQOOP and Hadoop jobs respectively. The other packages use the components provided and provide a quick start on getting data from Azure Blob Storage and getting data into Azure Blog Storage using SSIS.

SQL Server Data Tools Business Intelligence Project Templates in Visual Studio 2012

If you installed SQL 2012 you have probably noticed that you development environment for Integration Services, Analysis Services and Reporting Services is still hosted in a Visual Studio 2010 shell (SQL Server Data Tools). However, with a free download you can get the Microsoft Business Intelligence Project Templates for SSIS, SSAS and SSRS in your Visual Studio 2012 installation. All you need to do is to download and install it.

SQL Server 2012 Unboxing

Just about every new consumer technology device will be greeted with “unboxing” videos on YouTube. A lot of the people I talk to really need to start unboxing SQL Server 2012 and start to understand what is in the box. Most of them already have access to SQL Server 2012 and still think it is just a database. There is so much more! This post is aimed to providing a quick overview of what exactly is in the box with pointers to where you can find documentation.

  1. Database Engine (SSDE)
    First off, let’s start with the product that gave SQL its name: the database. This is without doubt the best known product of the whole SQL suite and also the most used. More often than not this is also the only product people use and know. Find out more here: http://technet.microsoft.com/en-us/library/ms187875.aspx
  2. Data Quality Services (DQS / SSDQS)
    Introduced with SQL Server 2012, DQS is a knowledge-driven data quality solution that works on the premise of specifying what defines data quality in a knowledge base and using to cleanse data automatically during ETL (see SSIS below), Master Data Management (see MDS below) processes or manually.
    See: http://technet.microsoft.com/en-us/library/ff877925.aspx
  3. Analyis Services (SSAS)
    Analysis Services is SQL Server’s analytical database or cube. It features both more traditional cubes and tabular models, provides self-service analysis capabilities and includes data mining. See http://technet.microsoft.com/en-us/library/bb522607.aspx
  4. Integration Services (SSIS)
    Integration Services is a full-blown ETL tool and can be used for all sorts of data integration solution. SSIS features a drag and drop interface to build the solution and provides a lot of components out of the box with connectors to and from just about any database, file storage or file format. If need be, you can also use the power of .NET to build the exact behavior required. SSIS also integrates with DQS to use data quality knowledge bases during ETL processes. For more info visit: http://technet.microsoft.com/en-us/library/ms141026.aspx
  5. Master Data Services (MDS)
    Master Data Services enables users to build a Master Data Management solution on top of SQL Server. MDS integrates with DQS to make data quality aspects a part of the overall MDM solution. See http://technet.microsoft.com/en-us/library/ee633763.aspx
  6. Reporting Services (SSRS)
    Reporting Services is the enterprise reporting solution that delivers web-enabled reports that can get information from a variety sources and be rendered in various formats (including Excel, Word and PDF). Also, reports can be retrieved on demand, on subscription bases or based on a alert. Find out more here: http://technet.microsoft.com/en-us/library/ms159106.aspx
  7. StreamInsight
    StreamInsight is Microsoft’s Complex Event Processor (CEP). CEP technology enables high throughput and real-time (low latency) processing of streams of data (events). Examples include financial trading, Web analytics, sensor data, etc. StreamInsight is provides a familiar development platform based on .NET to quickly start using real-time information. See: http://technet.microsoft.com/en-us/library/ee391416.aspx

That concludes the quick unboxing of SQL Server 2012. Although there is a lot more to say (about features, but also around editions and capabilities) , this should give you a good idea of what is in the box. Bottom line: there is a lot more to SQL Server than just a database!

%d bloggers like this: