SSIS Applications using SQL Azure

Exclusive offer: get 50% off this eBook here
Microsoft SQL Azure Enterprise Application Development

Microsoft SQL Azure Enterprise Application Development — Save 50%

Build enterprise-ready applications and projects with Microsoft SQL Azure using this book and eBook

$29.99    $15.00
by Jayaram Krishnaswamy | December 2010 | Enterprise Articles Microsoft

Microsoft SQL Server Integration Services 2008 (SSIS) is a full service Extract, Transform, and Load (ETL) program tightly integrated with SQL Server 2008 with a Rapid Application Development (RAD) user interface. Microsoft SQL Server Reporting Services 2008(SSRS) is a third generation reporting program that is also tightly integrated with SQL Server 2008, which hosts the Report Server providing full support with a web service frontend for a variety of reporting needs—from web-based reporting to embedded reporting.

In this article by Jayaram Krishnaswamy, author of Microsoft SQL Azure Enterprise Application Development, we will be leveraging SSIS, SSRS, and the tools used to address ETL processes, and Report authoring with SQL Azure as the source of data. We will be looking at the following data-related items in some detail:

  • Moving a MySQL database to SQL Azure database
  • Creating a report using SQL Azure as data source
  • Accessing SQL Azure from Report Builder 3.0

 

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.

Microsoft SQL Azure Enterprise Application Development Build enterprise-ready applications and projects with Microsoft SQL Azure using this book and eBook
Published: December 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:
        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:


Microsoft SQL Azure Enterprise Application Development Build enterprise-ready applications and projects with Microsoft SQL Azure using this book and eBook
Published: December 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


Microsoft Azure: Enterprise Application Development
Microsoft Azure: Enterprise Application Development

Software Testing using Visual Studio 2010
Software Testing using Visual Studio 2010

Microsoft Dynamics NAV Administration
Microsoft Dynamics NAV Administration

Microsoft Visio 2010 Business Process Diagramming and Validation
Microsoft Visio 2010 Business Process Diagramming and Validation

Microsoft Dynamics NAV 2009 Programming Cookbook
Microsoft Dynamics NAV 2009 Programming Cookbook

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration

Microsoft Windows Workflow Foundation 4.0 Cookbook
Microsoft Windows Workflow Foundation 4.0 Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
M
a
q
b
f
A
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software