02/21/2019
by Marlon Ribunal
1 Comment

Copy Data From On-Premise SQL Server To Azure Database Using Azure Data Factory

Let’s take a break from our SQL Server 2017 Reporting Services Basics Series and jump to Azure Data Factory (v2). The latest installment on our SSRS Series is about adding a simple parameter to the report, which you can find here.

Azure Data Factory (ADF) is a data integration service for cloud and hybrid environments (which we will demo here). ADF provides a drag-and-drop UI that enables users to create data control flows with pipeline components which consist of activities, linked services, and datasets. of course, there’s an option to set up components manually for more control. More info on ADF can be found here.

ADF is basically a fully-managed Azure service that provides a platform for orchestrating data movement or Extraction, Transformation, and Loading (ETL) of data from sources to target databases or analytics services. If you’re thinking of SSIS, that would be a similar concept.

In this post, we’ll learn how to copy data from our local instance of SQL Server into an Azure SQL Database by using Azure Data Factory (v2).

First thing first, if you do not have access to an Azure Subscription, sign up for a trial account here. Then, create the following services:

  • Azure SQL Database
  • Azure Data Factory (v2)
  • Local Instance of SQL Server 2017
  • WideWorldImporters Database (in your local instance)

Choosing a Pipeline Template

From your Azure Portal, navigate to your Resources and click on your Azure Data Factory. In the ADF blade, click on Author & Monitor button.

That will open a separate tab for the Azure Data Factory UI. For this demo, we’re going to use a template pipeline.

From the Template Gallery, select Copy data from on-premise SQL Server to SQL Azure.

That will open the panel for setting up the pipeline. Since this is the first time we’re copying a local SQL Server into Azure SQL Database on this ADF, we need to set up the connection between our local database and ADF.

Create the Datasource

From the panel, in the Datasource field, click the dropdown and select New. You can also click the link to the documentation for this template which gives you information about capabilities, prerequisites, and other info of this pipeline.

Create A Linked Service

Clicking New as shown above will open another panel for setting up a Linked Service that will facilitate the connection between our local database and the Azure database. Let’s name our SQL Server linked service LocalDBToAzureDB. Add a description.

In the Connect via Integration Runtime, select New.

“But what is Integration Runtime?”, you ask:

The Integration Runtime (IR) is the compute infrastructure used by Azure Factory to provide data integration capabilities across different network environments. Azure Integration Runtime can be used to connect to data stores and compute services in public network with public accessible endpoints. Use a self-hosted Integration Runtime for Private/On-Premise Networks.

Azure Data Factory Integration Runtime Info

In the Integration Runtime Setup panel, select Self-Hosted.

Click Next. Let’s name our runtime LocalDBAzureDBRuntime. Add a description if you want. The runtime Type, Self-Hosted, should already be selected by the system. Click Next again.

The next panel will provide two options to install the run time – Express or Manual. It also provides a couple of authentication keys to use if you want to add more nodes.

Let’s select Express setup. The link will download an executable. Save it to a convenient location. Run the executable.

The installer will download, install, and register all the necessary components for the runtime. It will also install a configuration manager that we can use to set up, diagnose, and update the runtime. When you get the successful installation message, click Close.

Setting up the Linked Service to Local SQL Server

Next, we need to change settings of the runtime. Find and open the Microsoft Integration Runtime Configuration Manager.

Click the Settings tab. On the Status, there’s a link that says Change. Click that. For the purpose of this demo, let’s select the basic option. Of course, you may want to encrypt the service if this is a production server.

Click OK. That will restart the runtime. Wait until it reconnects to the cloud. You can test the connections on both ends – local SQL Server and Azure Database – in the Diagnostics tab.

Go back to the New Linked Service panel to complete the datasource setup. Provide the local Server name and Database name. I have a local sysadmin account (sqladmin) that I used to access the local database. Test the connection.

Click Finish.

Create the Destination Table

Now before we set up the destination Azure SQL Database, let’s connect to the Azure Database via SQL Server Management Studio (SSMS) and create a new table called StockGroupLineProfit.

CREATE TABLE StockGroupLineProfit ( 
       StockGroupName VARCHAR (255)
     , LineProfit FLOAT ) ;

Let’s go back to the template setup and select the DataDestination. I have already set up my Azure SQL Database in advance. Select the Azure SQL Database. Set a new one if you haven’t done so.

Click Use This Template.

In the pipeline designer UI, set the Timeout of the General properties to 30 minutes. That’s enough time for the service to copy the amount of data that we’re going to set in the pipeline.

Set The Pipeline Data Source

Let’s create a new Source. Let’s name it LocalSQLWideWorldImporters. For the Connection, let’s select the Linked Service that we created above.

Let’s go back to the Source setting of our pipeline. Under the Source Dataset, select Query and input the following query.

SELECT sg.StockGroupName
	 , SUM( il.LineProfit ) AS LineProfit
FROM Sales.InvoiceLines il
JOIN Warehouse.StockItems si
  ON il.StockItemID = si.StockItemID
JOIN Warehouse.StockItemStockGroups sis
  ON si.StockItemID = sis.StockItemID
JOIN Warehouse.StockGroups sg
  ON sis.StockGroupID = sg.StockGroupID
GROUP BY sg.StockGroupName ;

If that T-SQL Code is familiar, it is because it’s the same query we used for our SSRS Report here.

Preview the data so we can have an idea of what we are loading to our Azure SQL Database.

In the Sink tab, let’s create a new sink dataset. Click New. Select Azure SQL Database. Click Finish.

Let’s name our sink dataset AzureStockGroup. For the linked service, I am selecting the Azure SQL Database that I have already set up prior. You have to create a new service if you haven’t done so. This is our target or destination Azure SQL Database. For the Table, select the table that we just created via SSMS, StockGroupLineProfit.

In the Schema tab of the sink setup, add the following columns:

  • StockGroupName (of type String)
  • LineProfit (of type Decimal)

The preview of the destination should be empty at this point.

Now that both the data source and destination for the pipeline are set up, go back to the pipeline designer. Select the Mapping tab. The source/destination mapping should now show up; if not, click the Import Schemas button.

Publish the Pipeline

To publish the pipeline and the linked services that we just created, click Publish All button at the top of the designer.

You should get a notification on the status of the published objects.

Run the Pipeline

Let’s click Debug in the pipeline designer. What debug does here is to actual run the pipeline and copy from the data set we defined in our data source and into our destination. Wait until the Succeeded message appears.

And, if we query our Azure SQL Database, we should now have the data in our destination table.

Enjoy orchestrating your data using Azure Data Factory!

02/15/2019
by Marlon Ribunal
2 Comments

SQL Server 2017 Reporting Services: The Basics Part 4

Note: I’ve been meaning to start a series on the basics of SQL Server Reporting Services (SSRS). The last time I did this was on a book on SQL Server 2012 Reporting Services published by Packt. SSRS has since evolved into a better reporting platform with the addition of KPI’s, mobile reports, etc.

We’re now in Part 4 of our SSRS Basics Series, in which we cover simple parameterized report. Before we proceed, if you are a total newbie to SSRS and you’ve never created nor deployed an SSRS report, Part 1 shows you how to install SSRS. Part 2 shows you how to create a simple table report. And, Part 3 shows you how to deploy that report to the SSRS Report Server.

First thing first: Create the following stored procedure. This contains the dataset of our next report.

CREATE PROCEDURE OrderTotalQuery ( @vOrderTotal FLOAT = NULL )
 AS 
 BEGIN
    --Set Default OrderTotal Value
    IF @vOrderTotal IS NULL
       SET @vOrderTotal = 1000 ;
    SELECT c.CustomerName
         , SUM( sol.Quantity * sol.UnitPrice ) AS OrderTotal
    FROM Sales.Orders so
    JOIN sales.OrderLines sol
      ON so.OrderID = sol.OrderID
    JOIN sales.Customers c
      ON so.CustomerID = c.CustomerID
    GROUP BY c.CustomerName
           , sol.OrderID
    HAVING SUM( sol.Quantity * sol.UnitPrice ) >= @vOrderTotal
    ORDER BY OrderTotal DESC ;
 END ;

We have one parameter in this stored procedure, called vOrderTotal. We are assigning it the NULL marker as a default. But let’s aside this NULL for our next post. For now, take note that it’s there and that we will use it later in the next post.

Basically, what this query does is return the Customers with equal to or more than X amount, where X = variable @vOrderTotal.

What a parameter does is it allows us to specify a value on runtime. We don’t need to go back in the database and hard-code the value. But of course, you already know that.

Create the Report

Open the existing project. Go back to Part 2 here if you’re not sure what I am referring to. Create a new report and name it rdl_SSRSTutorial_001_OrderTotalQuery.

Create the Dataset

Create a new dataset under the Shared Dataset in the project. Again, if you’re not sure how to do that, check the Part 2 of this series. Name it ds_SSRSTutorial_001_OrderTotalQuery. For the data source, we use the same Data Source that we previously created, DS_SSRSTutorial_001. We’re not creating a new Data Source because we’re basically connecting to the same database.

Select Stored Procedure for the Query Type. And select the OrderTotalQuery stored procedure that we created above.

Select the Parameters in the pane on the left. You may notice that the parameter of our stored procedure has been automatically detected. Let’s leave it as is for now. We’re going to go back here in the next post.

Click OK.

Parameterized Report

Drag the Table component into the report canvas. Like what we’ve seen in Part 2, dragging a table component into the designer displays the Dataset Properties dialog. Let’s name our report’s dataset as rpt_table_ds_SSRSTutorial_001_OrderTotalQuery. Use Shared Dataset and select the ds_SSRSTutorial_001_OrderTotalQuery dataset.

Again, you may notice that our parameter is detected automatically.

Click OK.

Preview the Report

Let’s preview the report to make sure it’s working. Input 10000 in the Order Total box. Click the View Report button.

You may notice that the parameter is named v Order Total, which is automatically copied from the parameter of our stored procedure. Let’s rename that by removing the letter v.

Rename the Report Parameter

Click the Design tab. Make sure you select the the design tab, or you won’t see the Report Data option in the View menu. Navigate to Menu > View > Report Data or Click CTRL + ALT + D.

In the Report Data pane, navigate to Parameters > vOrderTotal. Right-click the parameter and select the Parameter Properties. Select the General properties if it’s not already selected.

Change the Prompt. Remove the letter v. Click OK.

Build and Deploy the Parameterized Report

Build and deploy both the dataset and report. If you’re not sure how to do that, refer to Part 3 of our series which covers report deployment.

Parameterized Report in Action

Here’s our basic parameterized report in action.

Let’s make our parameter intelligent in the next installment of our SQL Server 2017 Reporting Services Basics Series. Next is about Range Value Parameter.

02/08/2019
by Marlon Ribunal
2 Comments

SQL Server 2017 Reporting Services: The Basics Part 3

Note: I’ve been meaning to start a series on the basics of SQL Server Reporting Services (SSRS). The last time I did this was on a book on SQL Server 2012 Reporting Services published by Packt. SSRS has since evolved into a better reporting platform with the addition of KPI’s, mobile reports, etc.

This is the Part 3 of our SQL Server 2017 Reporting Services (SSRS) Basics Series. You can find Part 1 here and Part 2 here. Part 3 covers report deployment.

You may remember that in part 1 we set up and installed the SSRS Web Service in the localhost http://localhost/reportserver. In our case, the hostname is the computer name (instead of the literal localhost).

You may want to check your SSRS Web Server URL on your Report Server Configuration Manager (you can find it in your start menu).

There are two ways of deploying a report:

  • Visual Studio Project Deployment
  • Manual Upload and Setup of the Report Files
Project Properties

In part 2, we created a report server project. Open the project on Visual Studio. Build the solution if you haven’t done so. Navigate to Build > Build Solution. You can also build via the context menu of the Solution in the Solution window (see the screenshot below for reference).

In the Solution Explorer window ( press CTRL + ALT + L if it’s not showing), select the project (we named it SSRSTutorial_001 last time). Right-click the project and select Properties in the context menu.

That will display the Project Properties dialog box. You can set up different Configurations depending on your target environment (e.g., Production, QA, Dev, UAT, etc). Let’s pretend we’re deploying this to Production so we choose Release as our configuration. Pay attention to the TargetServerURL property. Make sure that it’s the same as the Web Service URL in the Report Configuration Manager.

Project Deployment

Click OK. Select the Project again, right-click and select Deploy.

You may also deploy the individual components one by one.

A successful deployment message and other related info are now displayed in the Output window (press CTRL + ALT + O if it’s not showing).

Go to the Web Portal (http://localhost/reports). Check the Report Server Configuration Manager if you’re not sure of the URL. Click Browse to navigate to the deployed folders. You should have three folders containing the different components of our reports: Data Source, Dataset, and the Reports Definition Language (RDL) File. You can set the name of these folders in the project properties dialog box (see above). The properties are called TargetDataSourceFolder, TargetDatasetFolder, and TargetReportFolder respectively.

Manual Deployment

Let’s create three new folders in the Web Portal. Let’s name these folders with the same name above, plus the prefix term manual to distinguish them from the folders we deployed via Visual Studio.

In the Web Portal, click New and select Folder. Create three folders: Manual Data Source, Manual Dataset, and Manual SSRSTutorial_001.

Create The Data Source

Click the first folder, Manual Data Source, to open it. Click New and select Data Source. Name this data source as Manual_DS_SSRSTutorial_001. Select Microsoft SQL Server for the Connection Type. Input the following in the Connection String (you can basically copy this from the Data Source property of your solution in Visual Studio):

Data Source=./;Initial Catalog=WideWorldImporters

Leave the Credentials as is. Make sure to check the option Enable this data source. You may want to test your connection to make sure you’re connecting to the SQL Server. Click Create.

Upload the Dataset and Report

Click the Manual Dataset folder to open it. Click Upload from the menu. Navigate to your project bin files. In my case, that’s C:\Users\Marlon.Ribunal\Documents\Visual Studio 2017\Projects\SSRSTutorial_001\SSRSTutorial_001\bin\Release. Select the
Dataset file (ds_SSRSTutorial_001.rsd) . Navigate back Home in the web portal. Upload the Report file (rdl_SSRSTutorial_001.rdl) to the Manual SSRSTutorial_001 folder.

Set up the Dataset

Navigate to the Manual Dataset folder. Select the ds_SSRSTutorial_001 dataset or the ellipses on the top right-hand corner and select Manage. Select Data Source on the panel.

You get a warning that says, We can no longer find this data source. If it was moved, choose it from its new location. Click the ellipses under the warning. That will open a dialog box showing all the folders in the web portal.

Select the Manual Data Sources folder. Select the
Manual_DS_SSRSTutorial_001data source. Click Apply.

Set up the Report

Navigate back to Home and select the Manual SSRSTutorial_001 folder. Click the ellipses on the top right-hand corner of the report file. Select Manage. Select Shared Datasets from the panel.

You will also get a warning that says, We can no longer find this dataset. If it was moved, choose it from its new location. Click the ellipses under this warning. Select the Manual Dataset folder. Then select the ds_SSRSTutorial_001 dataset. Click Save.

Viewing the Reports

Navigate to Home. Select the SSRSTutorial_001 folder. Select rdl_SSRSTutorial_001. This contains the report we deployed via Visual Studio Project Deployment. Navigate back to Home. Now, select the
Manual SSRSTutorial_001 folder. This contains the report we set up manually. Select rdl_SSRSTutorial_001.

You should be able to view both reports.

And we’re done with the deployment part. We’ll set more properties in the future posts. For the meantime, stand by for the next installment of this SSRS Basics Series, basic parameterized report.