How to use SSIS to enable oData and other data sources in SQL Server 2016 Reporting Services

Screen12

Ah, it’s been awhile since I’ve done one of my longer blog posts.  There’s one I’ve been itching to do for the last few weeks around SQL Server Integration Services.  If you follow this blog, you know I have a great affinity for SSIS.  A question came up a few weeks back around how could someone enable SSIS as a data source in Reporting Services, since the currently “documented” way is not only several years old, it is completely unsupported by Microsoft.

Well, a colleague of mine pointed out there was a way to do this using the Data Streaming Destination option in an SSIS package.  This option allows you to query the output like you would any other SQL Server view in Reporting Services.  Needless to say, I was eager to put together a walkthrough for folks so they could try this themselves.

To create a SQL Server Integration Services Project, you’ll need to make sure you have a program called SQL Server Data Tools  installed.  It integrates with Visual Studio, but you don’t need Visual Studio already installed to use this program.  If you do have Visual Studio installed, however, this will add new project types you can select. The version for SQL Server 2016 is located here to download and use – https://msdn.microsoft.com/en-us/library/mt204009.aspx.

To get started, select File – > New -> Project, then select the Integration Services Project option.

Screen1

I’m going to create a very simple project that exposes an oData data source, which isn’t available in Reporting Services natively (yet . .).  I’m using the good ol’ Northwind oData feed, but you could also use an oData feed you expose from a LightSwitch project, for example.

To do this, I’ll add a Data Flow Task to my SSIS project.

clip_image016Double click on the task to open the Data Flow Task tab. Here is where you will add your source and destination locations. First, I am going to use an oData source, which I’ll find under the “Common” items in my toolbox.  I can then drag that onto my Data Flow Task.

image

Double-click on the source to create a new connection to my oData source.  Click New and enter the information for the Northwind data feed –

image

Once that’s done, click OK and select the data collection to expose.  Since this is just an example, we’ll leave the Columns or Error Output tabs as is and hit OK to save this.

image

Now we can add the Data Streaming Destination item from the toolbox to the Data Flow Task

image
and connect it using the arrow so it looks like so.
Screen5
While we could change the columns we pass through or change the column names, we’ll leave everything as is for this example and simply publish the package by choosing “Deploy” under the Project menu.

Screen6

The wizard is pretty self-explanatory, but one thing to keep in mind is you need to have an SSIS Catalog already setup on your SQL Server instance.  Assuming you have that done, walk through the steps and deploy to your catalog in SQL Server.

Screen8

Open up SQL Server Management Studio and login to the SQL Server Instance you deployed your project to.  You should see it under your “Integration Services Catalogs” folder.
image
Now you’ll need to enable the “Allow inprocess” option on the SSISOLEDB linked server provider that’s setup in SQL Server.  Browse to the Providers folder under the Server Objects –> Linked Servers folder path
image

Right-Click on the provider and select Properties.  Simply check the “Allow inprocess” option and click OK to save.

image

Head the Start Menu on your server and open the SQL Server 2016 Data Feed Publishing Wizard.  Here’s where you’ll enter the settings to select your SSIS package and the SQL database you want to publish it to.  You can name the SQL view whatever you’d like.  Hit Publish to execute the wizard and create the new view.

screen10

If it’s successful, you should see the view now in the Management Studio when you browse the database.
image

But does it work in Reporting Services?  Let’s give it a try – I’ll setup my shared data source as I would any other SQL Server data source in Reporting Services.

image

Then, create a shared dataset from it in Report Builder and publish it to the server.  If it is working properly, you should be able to preview the dataset in the portal –
image

We’ll create a mobile report against the shared dataset.  There’s no issues doing this, and it recognizes all the columns properly from the oData source just like it was a SQL Server view.

image

Finally, I’ll publish the mobile report to the server and try running it in the Reporting Services web portal.  Success!

image

Keep in mind, the data connection could potentially be slow depending on the amount of data you’re accessing.

That’s it – you not only have a way to use an oData feed with Reporting Services now, but you have a way to use dozens of new data sources available from several third party providers.

image
I’ll be doing a blog post on this provider in SSIS whenever it is available

Now this was a fun blog post to do.  Thanks for reading and enjoy your weekend!

How to use SQL Server Integration Services and Azure to move data between Microsoft SQL Server and SAP HANA

This blog post falls into the category of “I’d better make sure I document somewhere how I just did all of this.”  I really enjoy using SQL Server Integration Services, because it’s like a “Get out of Jail Free Card” for me.  In the past, if all else failed, I knew I could always use SSIS to bring my data into a SQL Server database and build my reports against that with ease.

But did you know that SSIS can also be used to pull data OUT of SQL Server and into another database?  This is something I rarely did, but since it seems some folks aren’t aware you can do this, I figured I’d flip the script and redo an SSIS job I’d put together.  Instead of pushing data in a SQL database from SAP HANA, I’ll send the data the other direction for a change.  So I put together this very long post that walks you through the following steps to show off a little bit of what you can do with SSIS –

– Spinning up a HANA database in Microsoft Azure
– Creating a table in SAP HANA
– Creating the SSIS package that moves the data between HANA and SQL Server (or Azure SQL database, in this example)

Let’s get started!

Step 1 – Provision your instance of HANA in Azure

Go to https://cal.sap.com/

Choose the instance you will want to provision and choose “Try Now” – for this exercise, I choose an edition of SAP HANA developer edition because I’m just testing some stuff and won’t be using this for production purposes.

image

image

Accept the Terms and Conditions

image

Now it’s time to add your Azure account details.  Open a new tab in your web browser and go to the Azure Management Portal and select the “Settings” option

image

Copy the subscription ID to your clipboard (I’ve obviously obscured mine)

image

Now jump back to your SAP Cloud Portal and go to the Accounts Tab.  From here, you’ll add your Azure account so the HANA VM can be provisioned –

image

Give your Account a friendly name and hit next

image

Choose “Microsoft Azure” from the dropdown and paste the Subscription ID into the second box and hit Next

image

You can just hit next to jump through the next three screens at this point – there are no users you need to add, and there isn’t any financial information available.  Once you hit Finish, a pop-up window will appear prompting you to download a management certificiate so SAP has permission to create the VM in Azure for you.

image

Download the certificate somewhere on your hard drive that you can easily access, then go back to your Azure tab.  Assuming you are still sitting on the “Subscriptions” screen under settings, you can click the “Management Certificates” and jump to that screen.  Here is where you will upload the certificate

image

Choose “Upload”, browse to the file you downloaded from the SAP site, then hit the checkmark.

image

image

It should take between 30 – 60 seconds to upload and confirm the certificate has been added.

image
Ta-Da!  It’s all done!

Now jump back to your SAP Cloud Tab – you should see the following in your Accounts tab if the process completed successfully.  The Cloud Provider ID should match your Subscription Name – if it does, you’re all set.

image

Now you can pick the solution you want to transfer to the Azure account you just setup.  This next part is a little confusing.  All this step does is allow you provision a VM, it doesn’t actually provision it for you.

So just select “Activate” for the HANA solution and it’ll change to “Create Instance”.  Click on it to walk through the provisioning wizard

image

Give your instance a name again (this will be the name of the VM that is spun up in Azure)

image

Choose a region to deploy the server to from the dropdown.  Your Azure account will be pre-populated in the Account box, then hit Next.  The next screen gives you the VM size option (there’s only one currently) and pre-defines a number of endpoints.  Just leave it as is unless you are comfortable re-defining these for your particular dev environment.

image

Now you get to set a password for the instance.  Do this and hit Next.

image

You can setup when you’d like the solution to suspend/terminate so it isn’t running all the time.

image

Or simply bring it up or down as you need to manually.

image

Hit Next Twice (step 5 is not applicable right now) and you’ll see the Summary of your options.  Assuming everything looks good, hit “Finish” and your VM will be spun up in your Azure account.

image

It’ll take about 5 – 10 minutes or so to spin up (at least that’s how long it did for me).  You can see when it is finished on either the SAP or the Microsoft Azure portal.

On the SAP Cloud Portal, the status is shown under the instances tab.  If the Status icon is green, your VM is up and running successfully –

image

On the Microsoft Azure portal, choose the VM option from the left menu and if it is running successfully, you’ll see the status as “Running” for the VM

image

Once it’s running, you can check if it is actually working by going to the website it spins up on the VM automatically by putting in the IP address it was assigned in Azure as the web address.  You can find the IP address in the Azure portal by clicking on the arrow next to the VM name

image

and choosing “Dashboard”

image

The IP address is then listed down the right hand side of the screen towards the bottom

image

Copy that address to your clipboard, open IE, and then paste it into the address bar.  You’ll be taken to the following screen if it is running properly –

image

Congratulations – you have successfully setup a HANA instance running in Microsoft Azure.

Part 2 – Creating a destination table in your SAP HANA database –

If you already know how to create a new schema and data table in SAP HANA, you can skip this section.

1. Go to the SAP HANA Web-based Development Workbench: Catalog on your HANA server. The web url will look like the following – http://yoursaphanadevboxaddress/sap/hana/ide/catalog/

clip_image002

2. Right-click on the Catalog folder to create a new schema – you can call the schema whatever you’d like. This is the equivalent of creating a new database in SQL Server.

clip_image003

3. When you’ve created the new schema, right-click on it in the left hand menu and choose “Open SQL Console”. This is where you’ll write the SQL script to create the table. I’m going to create a table based on the [Sales].[vStoreWithDemographics] view from the AdventureWorks database. The script I used was the following –

CREATE COLUMN TABLE “YOURSCHEMANAME”.”STORES”

(“NAME” NVARCHAR(50) null,
“ANNUALSALES” DECIMAL(16,2) null,
“ANNUALREVENUE” DECIMAL(16,2) null,
“BANKNAME” NVARCHAR(50) null,
“BUSINESSTYPE” NVARCHAR(5) null,
“YEAROPENED” INT null,
“SPECIALTY” NVARCHAR(50) null,
“SQUAREFEET” INT null,
“BRANDS” NVARCHAR(30) null,
“INTERNET” NVARCHAR(30) null,
“NUMBEREMPLOYEES” INT null)

Click the little run button to run it –

clip_image004

If successful, you should see a little note saying it ran with success
clip_image006

Part 3 – Create the SQL Server Integration Services package to bring your data in.

To create a SQL Server Integration Services Project, you’ll need to make sure you have a program called SQL Server Data Tools – Business Intelligence installed.  It integrates with Visual Studio, but you don’t need Visual Studio already installed to use this program.  If you do have Visual Studio installed, however, this will add new project types you can select. The version for SQL Server 2014 is located here to download and use – https://www.microsoft.com/en-us/download/details.aspx?id=42313.

To get started, select File – > New -> Project, then select the Integration Services Project option.

clip_image008

A new project will open, and you’ll see the following tabs –

clip_image010

I’ll need to create the proper data flow to move the information from one data source to another. SQL Server drivers are already available out of the box, but I need to make sure I have my HANA ODBC drivers installed on my development machine.

You can set up the ODBC connections once you’ve downloaded and installed the drivers from either SAP or a third party by going to Control Panel -> Administrative Tools and selecting that

clip_image012
then choosing the ODBC data source you wish to setup, which in this case would be SAP HANA. I usually install both the 32-bit and 64-bit drivers, just in case.  You will either need to get these drivers from SAP, or some third parties offer them as well.

clip_image014

IMPORTANT! MAKE SURE YOU HAVE THE 32-BIT HANA DRIVER INSTALLED ON THE SYSTEM WHERE YOU HAVE VISUAL STUDIO INSTALLED – YOU CAN’T BUILD THE PACKAGE IF YOU ONLY HAVE THE 64-BIT DRIVER INSTALLED.

Once that is done, you can now setup both of your data sources in Integration Services.

In your SSIS toolbox on the left-hand of your screen, drag the Data Flow Task onto your Control Flow.

clip_image016

Double click on the task to open the Data Flow Task tab. Here is where you will add your source and destination locations. For SQL Server, I am going to use an OLE DB source, which I’ll find under the “Other Sources” in my toolbox.  I can then drag that onto my Data Flow Task.

clip_image018

Double-click on the source to create a new connection to my SQL Server information by clicking New –

clip_image020

Then New again

clip_image022

I’ll be prompted to setup my SQL Server connection details. I’m using an Azure SQL Database as my source in this example, so I entered my details and hit Test Connection to make sure it was successful.

clip_image024

I can now select my table/view from my SQL database that I want to transfer to SAP HANA.

clip_image026

And I’ll select the columns I want to transfer. I didn’t bring over the BusinessEntityID (just because I wanted to show this) that existed in my SQL view, so I’m unchecking that column and then hitting OK.

clip_image028

NOTE: If you are selecting a view where one of the field types is Money, please note it is treated as a text field in HANA, which means you’ll get an error message if you try to bring it into the table that you scripted in an earlier step. I updated the view in my database to cast those fields as decimal, and this then worked as written.

I should now see the following item in my data task view. If there is no red X, it means I don’t have any errors and can proceed to setting up my destination. First I’ll rename it SQL Database to make it easier to remember.

clip_image029

Now I can setup my HANA database as my destination. I’ll add an ODBC destination to the data task

clip_image031

and rename it SAP HANA. I also moved it right under my data source item and dragged the blue arrow between the items to show the direction I am having the data flow go.

clip_image032

Now I add my HANA database connection information. I’m going to create a new ODBC Connection Manager with the HANA details I previously configured. All I need to do is select the existing information and hit OK.

clip_image034

It will connect to the HANA system and show me a list of tables where I can put the data.  I’ll select the table I had previously created in my HANA database.

clip_image036

You can see that I still need to map the columns on the Mappings page. Because I had created the tables to match the names and data types, the system will automatically map these items for me. I hit OK to save this information.

clip_image037

As you can see, I don’t have a red X, so I can proceed to the moment of truth – testing the job to see it moves the data properly.

We’ll run a quick select statement in the HANA console to show there is currently no data in the table I am loading my data to.

clip_image039

Yep, it is still completely empty. Now let’s run our SSIS job and add the SQL data to this table. In Visual Studio, I can just hit the “Start” button to run the job and confirm it works properly

clip_image041

Yay – it worked!

clip_image043

To confirm, I’ll re-run my query in HANA and see the results. Yes, the data is there!

clip_image045

That’s it – all I’d need to do now is save my project and deploy the package to my SQL Server instance. If you’ve never deployed an SSIS package before, there is a good tutorial here to help you out –

https://msdn.microsoft.com/en-us/library/ms365338.aspx

This is a very, very basic example of what you can do with SSIS, and I encourage you to spend more time with it, as you could find yourself in a situation where you need this kind of freedom to meet your customers needs.

Hope this was helpful – thanks for reading!