SSIS Applications using SQL Azure

Moving business applications and data to the cloud can be a smooth operation when you use this practical guide. Learn to make the most of SQL Azure and acquire the knowledge to build enterprise-ready applications.

 

Microsoft SQL Azure Enterprise Application Development

Microsoft SQL Azure Enterprise Application Development

Build enterprise-ready applications and projects with SQL Azure

  • Develop large scale enterprise applications using Microsoft SQL Azure
  • Understand how to use the various third party programs such as DB Artisan, RedGate, ToadSoft etc developed for SQL Azure
  • Master the exhaustive Data migration and Data Synchronization aspects of SQL Azure.
  • Includes SQL Azure projects in incubation and more recent developments including all 2010 updates

Appendix

        Read more about this book      

(For more resources on Microsoft Azure, see here.)

SSIS and SSRS are not presently supported on SQL Azure. However, this is one of the future enhancements that will be implemented. While they are not supported on Windows Azure platform, they can be used to carry out both data integration and data reporting activities.

Moving a MySQL database to SQL Azure database

Realizing the growing importance of MySQL and PHP from the LAMP stack, Microsoft has started providing programs to interact with and leverage these programs. For example, the SSMA described previously and third-party language hook ups to Windows Azure are just the beginning. For small businesses who are now using MySQL and who might be contemplating to move to SQL Azure, migration of data becomes important. In the following section, we develop a SQL Server Integration Services package, which when executed transfers a table from MySQL to SQL Azure.

Creating the package

The package consists of a dataflow task that extracts table data from MySQL (source) and transfers it to SQL Azure (destination). The dataflow task consists of an ADO.NET Source connecting to MySQL and an ADO.NET Destination connecting to SQL Azure. In the next section, the method for creating the two connections is explained.

Creating the source and destination connections

In order to create the package we need a connection to MySQL and a connection to SQL Azure. We use the ADO.NET Source and ADO.NET Destination for the flow of the data.

In order to create an ADO.NET Source connection to MySQL we need to create an ODBC DSN as we will be using the .NET ODBC Data Provider. Details of creating an ODBC DSN for the version of MySQL are described here: http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008. Configuring a Connection Manager for MySQL is described here: http://www.packtpub.com/article/mysql-data-transfer-using-sql-server-integration-servicesssis.

The Connection Manager for SQL Azure Destination uses a .NET SQLClient Data Provider and this is described here (when SQL Azure was in CTP but no change is required for the RTM): . The authentication information needs to be substituted for the current SQL Azure database.

Note that these procedures are not repeated step-by-step as they are described in great detail in the referenced links. However some key features of the configuration details are presented here:

  • The ODBC DSN created is shown here with the details:

  • The settings used for the MySQL Connection Manager are the following:

    Provider: .NET Providers\Odbc Data Provider
    Data Source Specification
    Use user or system data source name: MySqlData
    Login Information: root
    Password: <root password>

  • The settings for the SQL Azure are the following:

    Provider: .Net Providers\SQLClient Data Provider
    Server name: xxxxxxx.database.windows.net
    Log on to the server
    Use SQL Server authentication
    User name: mysorian
    Password: ********
    Connect to a database
    Select or enter database name: Bluesky (if authentication is correct, it should appear in the drop-down)

Creating the package

We begin with the source connection and after configuring the Connection Manager, by editing the source as shown in the following screenshot. You may notice that the SQL command is used rather than the name of the table. It was found however, that choosing the name of the table results in an error. Probably a bug, and as a workaround we use the SQL command. With this you can preview the data and verify it.

After verifying the data from the source, drag-and-drop the green dangling line from the source to the ADO.NET Destination component connected to SQL Azure. Double-clicking the destination component brings up the ADO.NET Destination Editor with the following details:

Connection manager: XXXXXXXXX.database.windows.net.Bluesky.mysorian2
Use a table or view: "dbo"."AzureEmployees"
Use Bulk Insert when possible:
checked

There will be a warning message at the bottom of screen: Map the columns on the Mappings page.

The ADO.NET Destination Editor window comes up with a list of tables or views displaying one of the tables. We will be creating a new table. Clicking New… button for the field Use a table or view brings up the Create Table window with a default create table statement with all the columns from the source table and a default table name, ADO.NET Destination. Modify the create table statement as follows:

CREATE TABLE fromMySql(
"Id" int Primary Key Clustered,
"Month" nvarchar(11),
"Temperature" float,
"RecordHigh" float

When you click on OK in this screen you will have completed the configuration of the destination. There are several things you can add to make troubleshooting easier by adding Data Viewers, error handling, and so on. These are omitted here but best practices require that these should be in place when you design packages.

The completed destination component should display the following details:

Connection manager: XXXXXXX.database.windows.net.Bluesky.mysorian2
Use a table or view: fromMySql
Use Bulk Insert when possible:
Checked

The columns from the source are all mapped to the columns of the destination, which can be verified in the Mappings page, as shown in the following screenshot:

When the source and destination are completely configured as described here you can build the project from the main menu.

When you execute the project, the program starts running and after a while both the components turn yellow and then go green indicating that the package has executed successfully. The rows (number) that are written to the destination also appear in the designer.

You may now log on to SQL Azure in SSMS and verify that the table fromMySql2 has been created and that 12 rows of data from MySQL's data have been written into it.

        Read more about this book      

(For more resources on Microsoft Azure, see here.)

Creating a report using SQL Azure as data source

Although Reporting Services is not installed in the Cloud as of this writing, Microsoft is considering placing it in the near future. Moving SQL Server Reporting Services has the main advantage of reduced latency as the data is directly accessed in the datacenter. This may entail providing access to the database to a larger group of report authoring personnel. Given the fact that Reporting Services is not in the cloud, does not prevent the generation of reports based on the data on SQL Azure. In fact, SQL Azure is included as one of the data sources that can be configured using SSRS. In the following section, a procedure is described to connect to SQL Azure from BIDS to generate the report.

In order to generate a report using BIDS, perform the following steps:

  1. Start the SQL Server Business Intelligence Development Studio (BIDS) and create a Report Server Project in the Business Intelligence Projects type. Provide a custom name for the project.
  2. Herein it is named CloudReport.
  3. Click on OK.
  4. This creates the CloudReport project with three folders, Shared Data Sources, Shared Datasets, and Reports.
  5. Highlight the Report folder, right-click and choose Add New Report from the drop-down menu.
    This wakes up the Report Wizard and displays the Welcome to the Report Wizard page. Make sure you read the information on this page.
  6. Click on Next.
  7. The Select the Data Source page of the Report Wizard is displayed as shown in the following screenshot. Observe that you can connect to a variety of data sources including those from third parties.

    We have the option to make this a shared source or not. We do not check this box. We use the default data type source, which is Microsoft SQL Server. We choose a name for the data source. Herein it is named SQLAzureDS.

    Copying the connection string for ADO.NET from the portal, saving it to clipboard, and pasting it in the text area reserved for the connection string in the previous screenshot will not work as this is a connection string that will be constructed by the wizard.

  8. Click on Edit….
  9. The Connection Properties window is displayed, as shown in the following screenshot:

  10. Enter the details for Server name, User name, and Password after choosing Use SQL Server Authentication and the Select or enter a database name. You may test the connection. Click on OK.
    The Connection string field of the Select Data Source page gets updated with the server name and the database name.
  11. Click on Next. The Enter Data Source Credential window pops up.
  12. Enter the credentials once again and click OK on this window.
  13. The Design the Query page of Report Wizard is displayed.
  14. Click the Query Builder button. The Query Designer is displayed. This is a common interface in most of Microsoft's database products (for example, Microsoft Access and Query Designer in SSMS).
  15. Dismiss the Query Designer by closing it. We will not use the Query Designer but directly enter the following statement in the Design the Query window.
    Select * from fromMySql2

    Here, the table fromMySql2 on the SQL Azure database, Bluesky, is used. You could use any other table you might have on your SQL Azure database as long as the connection string correctly points to the data source.

  16. Click on Next. The Select the Report Type page of the Report Wizard is displayed with two options Tabular or Matrix with Tabular being the default.
  17. Click on Next. The Design the Table page of the wizard is displayed. fromMySql2 is a simple table with four columns and 12 rows.
    Pick each of the items in the Available fields, drag-and-drop it on the Displayed fields area of the page (alternatively, you could also use the Details> button to achieve the same after highlighting all items in the area of the Available fields).
  18. Click on Next. The Choose the Table Style page of the wizard is displayed.
  19. Choose a style and click on Finish.
    The Report1.rdl file generated by the Report Wizard is displayed in the Visual Studio 2008 IDE. The Report Data window with Built-in Fields, Data Sources, and Datasets folders are also displayed, as shown in the following screenshot:

    At this point the report is essentially complete.

  20. Right click the Report1.rdl in the project folder and click the Run menu item from the drop-down. The Report1.rdl - Report Preview page is displayed.
  21. Enter authentication information at the top of this page and click View Report. The report will be processed and will be displayed, as shown in the following screenshot:

    This can now be deployed to the Report server.

  22. Right click the report file in the project folder and choose Deploy.
  23. You need to provide the correct target URL for deployment. The URL for the present installation is: http://hodentek3/ReportServer_KUMO.

Accessing SQL Azure from Report Builder 3.0

Report Builder 3.0 is a standalone report authoring and deploying tool that is totally integrated with SQL Server 2008 R2. It can be downloaded from the link given here although it can also be installed when you install SQL Server 2008 R2: http://www.microsoft.com/downloads/details.aspx?FamilyID=D3173A87-7C0D-40CCA408-3D1A43AE4E33&displaylang=en.

For the purpose of the following example, it is assumed that Report Builder 3.0 is installed on the computer. It is also assumed that the Report Server has been configured. By following these steps we can develop a report using data on the SQL Azure server.

We start with Report Builder 3.0 and use it to connect to SQL Azure data source and obtain a dataset from one of its tables. After obtaining the data, we will create a report based on the data and then deploy to the Report Server.

  1. Start Report Builder 3.0 from its shortcut. The first time it takes some time to connect to the Report Server. Make sure you run it as administrator. When you get connected to the Report Server you should see the server you are connected to, at the bottom of the window.

  2. Right-click on the Data Sources folder and choose Add New Data Source….
  3. This opens the Data Source Properties page where you can choose the connection type (of the data source) that includes SQL Azure.

  4. Choose Microsoft SQL Azure.
  5. The Data Source Properties window is displayed.

  6. Click on the Build… button.
  7. The Connection Properties page is displayed, as shown in the next screenshot.
  8. Fill in the details as shown here. Click on Test Connection and verify if the connection is ok.

  9. Click on OK in the two screens.
    The Connection string information gets updated as shown. Note that the default Datasource1 is changed to BuilderSRC.

  10. Click on Credentials.

    With this choice of the credential, the user will be asked to provide login information every time he/she accesses the report.

  11. Click on OK.

Now you can drag-and-drop a table (or you can start with a Table Wizard) and drag-and-drop fields from the dataset onto the report to author a report. The user can refer to the SSRS online references to learn about authoring reports. The following URLs provide detailed examples of creating a report using Report Builder 2.0, which is not very different from using Report Builder 3.0.

A complete view is shown in the following screenshot where three items from the dataset were dragged-and-dropped on the table designer interface:

The designed report can be saved on the Report Server. It may be noted however, that this report will not render due to the fact that the data extension SQLAZURE—which is needed for validation and rendering—is not supported in the CTP edition. You will typically get the error shown in the following screenshot when you try to display the report in Report Builder.

Note that a report created in Visual Studio will not have this problem. In the RTM version of SQL Server 2008 R2, this is known to work.

In Windows 7, even if you are the owner of the computer, you need to open the browser explicitly as owner of the computer to view the folders and files on the Report Manager and the Report Server.

Summary

In this article a SSIS project showed how easy it is to migrate data from a MySQL database to SQL Azure. Two reporting services projects were described: one using BIDS and the other using Report Builder 3.0, to author a report using data on SQL Azure.


Further resources on this subject:


Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free