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.
Accept the Terms and Conditions
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
Copy the subscription ID to your clipboard (I’ve obviously obscured mine)
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 –
Give your Account a friendly name and hit next
Choose “Microsoft Azure” from the dropdown and paste the Subscription ID into the second box and hit Next
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.
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
Choose “Upload”, browse to the file you downloaded from the SAP site, then hit the checkmark.
It should take between 30 – 60 seconds to upload and confirm the certificate has been added.
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.
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
Give your instance a name again (this will be the name of the VM that is spun up in Azure)
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.
Now you get to set a password for the instance. Do this and hit Next.
You can setup when you’d like the solution to suspend/terminate so it isn’t running all the time.
Or simply bring it up or down as you need to manually.
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.
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 –
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
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
and choosing “Dashboard”
The IP address is then listed down the right hand side of the screen towards the bottom
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 –
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/
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.
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 –
If successful, you should see a little note saying it ran with success
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.
A new project will open, and you’ll see the following tabs –
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
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.
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.
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.
Double-click on the source to create a new connection to my SQL Server information by clicking New –
Then New again
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.
I can now select my table/view from my SQL database that I want to transfer to SAP HANA.
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.
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.
Now I can setup my HANA database as my destination. I’ll add an ODBC destination to the data task
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.
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.
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.
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.
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.
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
Yay – it worked!
To confirm, I’ll re-run my query in HANA and see the results. Yes, the data is there!
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!
Christopher Finlan 