SSIS Basics: Adding Data Flow to Your Package

12 June 2012

by Annette Allen

Annette continues her popular series for SSIS beginners by showing how a data flow task can be used in a package to move data from a SQL Server database to an Excel file, and insert an additional column into the Excel file that’s based on derived data.

In my previous article, "SSIS Basics: Setting Up Your Initial Package", I showed you how to create an SSIS package and configure connection managers, data sources, and data source views. In this article, I will show you how to use some of those data connections to retrieve data from a SQL Server database and load the data into an Excel file. I will also show you how to add a computed column based on data derived from the data flow. In addition, I will demonstrate how to run the package.

SSIS supports many control flow items that manage a package’s workflow, but the one I think to be the most important and most often used is the Data Flow task. For this reason, I focus on that task in this article. In future articles, I’ll cover other control flow items.

Note:
If you want to try out the examples in this article, you’ll need to create an OLE DB connection manager that points to the AdventureWorks database and a Flat File connection manager that points to an Excel file. You can find details about how to set up these connection managers in my previous article, which is referenced above. I created the examples shown in this article and the last one on a local instance of SQL Server 2008 R2.

Adding a Data Flow Task

Our goal in creating this package is to move data from a SQL Server database to an Excel file. As part of that goal, we also want to insert an additional column into the Excel file that’s based on derived data.
To carry out our goal, we must add a Data Flow task to our control flow. The task lets us retrieve data from our data source, transform that data, and insert it into our destination, the Excel file. The Data Flow task is one of the most important and powerful components in SSIS and as such has it’s own workspace, which is represented by the Data Flow tab in SSIS Designer, as shown in Figure 1.

Figure 1: The Data Flow tab in SSIS Designer

Before we can do anything on the Data Flow tab, we must first add a Data Flow task to our control flow. To add the task, drag it from the Control Flow Items window to the Control Flow tab of the SSIS Designer screen, as illustrated in Figure 2.

Figure 2: Adding a Data Flow task to the control flow

To configure the data flow, double-click the Data Flow task in the control flow. This will move you to the Data Flow tab, shown in Figure 3.

Figure 3: The Data Flow tab in SSIS Designer

Configuring the Data Flow

You configure a Data Flow task by adding components to the Data Flow tab. SSIS supports three types of data flow components:

• Sources: Where the data comes from
• Transformations: How you can modify the data
• Destinations: Where you want to put the data

A Data Flow task will always start with a source and will usually end with a destination, but not always. You can also add as many transformations as necessary to prepare the data for the destination. For example, you can use the Derived Column transformation to add a computed column to the data flow, or you can use a Conditional Split transformation to split data into different destinations based on specified criteria. This and other components will be explained in future articles.
To add components to the Data Flow task, you need to open the Toolbox if it’s not already open. To do this, point to the View menu and then click ToolBox, as shown in Figure 4.

Figure 4: Opening the Toolbox to view the data flow components

At the left side of the Data Flow tab, you should now find the Toolbox window, which lists the various components you can add to your data flow. The Toolbox organizes the components according to their function, as shown in Figure 5.

Figure 5: The component categories as they appear in the Toolbox

To view the actual components, you must expand the categories. For example, to view the source components, you must expand the Data Flow Sources category, as shown in Figure 6

Figure 6: Viewing the data flow source components

Adding an OLE DB Source

The first component we’re going to add to the data flow is a source. Because we’re going to be retrieving data from a SQL Server database, we’ll use an OLE DB source. To add the component, expand the Data Flow Sources category in the Toolbox. Then drag an OLE DB source from to the Data Flow window. Your data flow should now look similar to Figure 7.

Figure 7: Adding an OLE DB source to your data flow

You will see that we have a new item named OLE DB Source. You can rename the component by right-clicking it and selecting rename. For this example, I renamed it Employees.
There are several other features about the OLE DB source noting:

• A database icon is associated with that source type. Other source types will show different icons.
• A reversed red X appears to the right of the name. This indicates that the component has not yet been properly configured.
• Two arrows extend below the component. These are called data paths. In this case, there is one green and one red. The green data path marks the flow of data that has no errors. The red data path redirects rows whose values are truncated or that generate an error. Together these data paths enable the developer to specifically control the flow of data, even if errors are present.

To configure the OLE DB source, right-click the component and then click Edit. The OLE DB Source Editor appears, as shown in Figure 8.

Figure 8: Configuring the OLEDB source

From the OLE DB connection manager drop-down list, select the OLE DB connection manager we set up in the last article, the one that connects to the AdventureWorks database.
Next, you must select one of the following four options from the Data access mode drop-down list:

• Table or view
• Table name or view name variable
• SQL command
• SQL command from variable

For this example, we’ll select the Table or View option because we’ll be retrieving our data through the uvw_GetEmployeePayRate view, which returns the latest employee pay raise and the amount of that raise. Listing 1 shows the Transact-SQL used to create the view in the AdventureWorks database.

CREATE VIEW uvw_GetEmployeePayRate
AS
    SELECT  H.EmployeeID ,
            RateChangeDate ,
            Rate
    FROM    HumanResources.EmployeePayHistory H
            JOIN ( SELECT   EmployeeID ,
                            MAX(RateChangeDate) AS [MaxDate]
                   FROM     HumanResources.EmployeePayHistory
                   GROUP BY EmployeeID
                 ) xx ON H.EmployeeID = xx.EmployeeID
                         AND H.RateChangeDate = xx.MaxDate
GO

Listing 1: The uvw_GetEmployeePayRate view definition

After you ensure that Table or view is selected in the Data access mode drop-down list, select the uvw_GetEmployeePayRate view from the Name of the table or the view drop-down list. Now go to the Columns page to select the columns that will be returned from the data source. By default, all columns are selected. Figure 9 shows the columns (EmployeeID, RateChangeDate, and Rate) that will be added to the data flow for our package, as they appear on the Columns page.

Figure 9: The Columns page of the OLE DB Source Editor

If there are columns you don’t wish to use, you can simply uncheck them in the Available External Columns box.
Now click on the Error Output page (shown in Figure 10) to view the actions that the SSIS package will take if it encounters errors.

Figure 10: The Error Output page of the OLE DB Source Editor

By default, if there is an error or truncation, the component will fail. You can override the default behavior, but explaining how to do that is beyond the scope of this article. You’ll learn about error handling in future articles.
Now return to the Connection Manager page and click the Preview button to view a sample dataset in the Preview Query Results window, shown in Figure 11. Previewing the data ensures that what is being returned is what you are expecting.

Figure 11: Previewing a sample dataset

After you’ve configured the OLE DB Source component, click OK.

Adding a Derived Column Transformation

The next step in configuring our data flow is to add a transformation component. In this case, we’ll add the Derived Column transformation to create a column that calculates the annual pay increase for each employee record we retrieve through the OLE DB source.
To add the component, expand the Data Flow Transformations category in the Toolbox window, and drag the Derived Column transformation (shown in Figure 12) to the Data Flow tab design surface.

Figure 12: The Derived Column transformation as its listed in the Toolbox

Drag the green data path from the OLE DB source to the Derived Column transformation to associate the two components, as shown in Figure 13. (If you don’t connect the two components, they won’t be linked and, as a result, you won’t be able to edit the transformation.)

Figure 13: Using the data path to connect the two components

The next step is to configure the Derived Column component. Double-click the component to open the Derived Column Transformation Editor, as shown in Figure 14.

Figure 14: Configuring the Derived Column transformation

This editor is made up of three regions, which I’ve labeled 1, 2 and 3:

1. Objects you can use as a starting point. For example you can either select columns from your data flow or select a variable. (We will be working with variables in a future article.)
2. Functions and operators you can use in your derived column expression. For example, you can use a mathematical function to calculate data returned from a column or use a date/time function to extract the year from a selected date.
3. Workspace where you build one or more derived columns. Each row in the grid contains the details necessary to define a derived column.

For this exercise, we’ll be creating a derived column that calculates a pay raise for employees. The first step is to select the existing column that will be the basis for our new column.
To select the column, expand the Columns node, and drag the Rate column to the Expression column of the first row in the derived columns grid, as shown in Figure 15.

Figure 15: Adding a column to the Expression column of the derived column grid

When you add your column to the Expression column, SSIS prepopulates the other columns in that row of the grid, as shown in Figure 16.

Figure 16: Prepopulated values in derived column grid

As you can see, SSIS has assigned our derived column the name Derived Column 1 and set the Derived Column value to <add as new column>. In addition, our [Rate] field now appears in the Expression column, and the currency[DT_CY] value has been assigned to the Data Type column.
You can change the Derived Column Name value by simply typing a new name in the box. For this example, I’ve renamed the column NewPayRate.
For the Derived Column value, you can choose to add a new column to your data flow (which is the default value, <add as new column>) or to replace one of the existing columns in your data flow. In this instance, we’ll add a new column, but there may be times when overwriting a column is required.
The data type is automatically created by the system and can’t be changed at this stage.
Our next step is to refine our expression. Currently, because only the Rate column is included in the expression, the derived column will return the existing values in that column. However, we want to calculate a new pay rate. The first step, then, is to add an operator. To view the list of available operators, expand the list and scroll through them. Some of the operators are for string functions and some for math functions.
To increase the employee’s pay rate by 5%, we’ll use the following calculation:

[Rate] * 1.05

To do this in the Expression box, either type the multiplication operator (*), or drag it from the list of operators to our expression (just after the column name), and then type 1.05, as shown in Figure 17.

Figure 17: Defining an expression for our derived column

You will see that the Data Type has now changed to numeric [DT_NUMERIC].
Once you are happy with the expression, click on OK to complete the process. You will be returned to the Data Flow tab. From here, you can rename the Derived Column transformation to clearly show what it does. Again, there are two data paths to use to link to further transformations or to connect to destinations.

Adding an Excel Destination

Now we need to add a destination to our data flow to enable us to export our results into an Excel spreadsheet.
To add the destination, expand the Data Flow Destinations category in the Toolbox, and drag the Excel destination to the SSIS Designer workspace, as shown in Figure 18.

Figure 18: Adding an Excel destination to your data flow

Now connect the green data path from the Derived Column transformation to the Excel destination to associate the two components, as shown in Figure 19.

Figure 19: Connecting the data path from the transformation to the destination

As you can see, even though we have connected the PayRate transformation to the Excel destination, we still have the reversed red X showing us that there is a connection issue. This is because we have not yet selected the connection manager or linked the data flow columns to those in the Excel destination.
Next, right-click the Excel destination, and click Edit. This launches the Excel Destination Editor dialog box, shown in Figure 20. On the Connection Manager page, under OLE DB connection manager, click on the New button then under Excel File Path click on the Browse button and select the file you created in the previous article and click on OK, then under Name of the Excel Sheet select the appropriate sheet from the file.

Figure 20: Configuring the Excel destination component

At the bottom of the Connection Manager page, you’ll notice a message that indicates we haven’t mapped the source columns with the destination columns. To do this, go to the Mappings page (shown in Figure 21) and ensure that the columns in the data flow (the input columns) map correctly to the columns in the destination Excel file. The package will make a best guess based on field names; however, for this example, I have purposefully named my columns in the excel spreadsheet differently from those in the source database so they wouldn’t be matched automatically.

Figure 21: The Mappings page of the Excel Destination Editor

To match the remaining columns, click the column name in the Input Column grid at the bottom of the page, and select the correct column. As you select the column, the list will be reduced so that only those columns not linked are available. At the same time, the source and destination columns in the top diagram will be connected by arrows, as shown in Figure 22.

Figure 22: Mapping the columns between the data flow and the destination

Once you’ve properly mapped the columns, click OK. The Data Flow tab should now look similar to the screenshot in Figure 23.

Figure 23: The configured data flow in your SSIS package

Running an SSIS Package in BIDS

Now all we need to do is execute the package and see if it works. To do this, click the Execute button. It’s the green arrow on the toolbar, as shown in Figure 24.

Figure 24: Clicking the Execute button to run your SSIS package

As the package progresses through the data flow components, each one will change color. The component will turn yellow while it is running, then turn green or red on completion. If it turns green, it has run successfully, and if it turns red, it has failed. Note, however, that if a component runs too quickly, you won’t see it turn yellow. Instead, it will go straight from white to green or red.
The Data Flow tab also shows the number of rows that are processed along each step of the way. That number is displayed next to the data path. For our example package, 290 rows were processed between the Employees source and the PayRate transformation, and 290 rows were processed between the transformation and the Excel destination. Figure 25 shows the data flow after the three components ran successfully. Note that the number of processed rows are also displayed.

Figure 25: The data flow after if has completed running

You can also find details about the package’s execution on the Progress tab (shown in Figure 26). The tab displays each step of the execution process. If there is an error, a red exclamation mark is displayed next to the step’s description. If there is a warning, a yellow exclamation mark is displayed. We will go into resolving errors and how to find them in a future article.

Figure 26: The Progress tab in SSIS Designer

Now all that’s needed is to check the Excel file to ensure that the data was properly added. You should expect to see results similar to those in Figure 27.

Figure 27: Reviewing the Excel file after package execution

Summary

In this article of the "SSIS Basics" series, I’ve shown you how to add the data flow to your SSIS package in order to retrieve data from a SQL Server database and load it into an Excel file. I’ve also shown you how to add a derived column that calculates the data to be inserted into the file. In addition, I’ve demonstrated how to run the package.
In future articles, I plan to show you how to deploy the package so you can run it as part of a scheduled job or call in other ways. In addition, I’ll explain how to use variables in your package and pass them between tasks. I also aim to cover more control flow tasks and data flow components, including those that address conditional flow logic and for-each looping logic. There is much much more that can be done using SSIS, and I hope over the course of this series to cover as much information as possible

Test Automation Using Visual Studio 2010 Coded UI

Raj Kamal
Download the Code Sample
You can use Visual Studio 2010 Ultimate or Visual Studio 2010 Premium to create automated tests of the user interface (UI) known as coded UI tests. These tests provide functional testing of the UI and validation of UI controls. Automated UI tests enable you to test that the UI is functioning correctly after code changes. Coded UI tests are quicker to run than manual tests, meaning that you can run them more frequently.
This article provides test automation guidance that helps you leverage the coded UI features of Visual Studio 2010 to foster a continuous, consistent and standard automation approach. Such an approach allows you to build, deploy and test, taking advantage of Visual Studio Lab Management features, which let automation testers reap the benefits of integrating with the build process for automatic build deployment, executing on VMs and reporting test automation results as part of Microsoft Test Manager (MTM).
Before walking you through an example that illustrates the coded UI automation process, let's cover some basics.

Test Automation Using Visual Studio 2010

Automating your tests is just one part of the test automation process. You also need to automate the build process and provide automatic installation and deployment. Automating each part of the test automation process eliminates the need to trigger automation manually because of build and deployment dependencies.
The automated build, deploy and test framework also enables you to find out the build quality at the earliest opportunity by publishing the test results for every run against a new build. You can choose to run only the test suites that are most important for your continuous builds, since too many tests can delay the completion of a build. Make sure that you run these tests for the current iteration.
Here are the steps in the build, deploy and test workflow:

• Choose the virtual environment you want to use.
• Select a snapshot for that environment to use as your starting point for deployment.
• Select the build definition or the build to use to deploy your application.
• Add scripts that will deploy the application from the drop folder.
• Select the test suites, test configuration and test settings for your test run.

Figure1 shows the topology required for setting up the build, deploy and test environment using Visual Studio components such as Build Controller and Test Controller and a virtual environment for deployment and execution.

Figure1  Requirements for setting up build, deploy and test environment using various Visual Studio components
To run unit or functional automation tests, you can create a set of environments that are managed Hyper-V images in MTM. Tests that are created at the start can be used as part of continuous integration. For daily builds, you want to run a minimal set of tests, such as Build Verification or Smoke tests. For weekly builds, you should run regression tests.
If you plan to run your automated tests using MTM, you must use a physical or virtual environment that contains a set of roles to run your tests from your test plan.

Coded UI Automation Example

In this example the basic functionality of Bing is tested using coded UI to automate the functional tests. The assumption is that the functional tests using MTM are already written.
Figure 2 shows a sample snapshot of the test cases in MTM. Notice in the Automation Status field that none of the tests are automated yet.

Figure 2 A sample snapshot of test cases in MTM
Let's now go through the steps in the automation scope and planning process. Other parts of the automation development lifecycle are covered later in the article.

Automation Planning

Like project planning or test planning, automation planning is important to ensure that teams are not jumping into automation scripting without considering the relevant factors. These factors are critical for providing accurate estimates, deciding the right framework, choosing the right approach, enabling long-term automation maintenance and achieving early return on investment (ROI).
These are the factors in the automation planning process:

• Identify and mark automatable test cases and common scenarios
• Identify data-driven test cases
• Identify shared steps that can be automated for reusability
• Decide (and flag) the order of execution
• Develop proof of concept
• Perform automation estimation

Identify Automatable Test Cases and Common Scenarios

The first step in automation planning is to determine the test cases that are good automation candidates and the scenarios that can be converted into library methods for reuse. If the tests are dependent on each other, you must also plan the sequence of execution.
In MTM, you first identify the automatable tests and then mark them as Planned in the Automation Status field, as shown in Figure 3. Tests that are not automated yet or are not good candidates for automation are marked as Not Automated.


Figure 3 Planned and Not Automated candidates

Identify Data-Driven Test Cases

Next, you identify any data-driven test cases. This information is required during automation design. Figure 4 shows an example of a data-driven test that involves searching multiple times for the list of values provided as test data.

Figure 4 Data-driven test example
For more information about data-driven testing using coded UI, go to Mathew Aniyan's blog and http://msdn.microsoft.com/en-us/library/ee624082(VS.100).aspx.
To find out how to use Microsoft Excel for test data, refer to http://msdn.microsoft.com/en-us/magazine/cc163536.aspx.

Identify the Shared Steps That Can Be Automated for Reusability

Shared steps launched with Visual Studio 2010 MTM. This feature helps you write modular and reusable test cases. Ensure that you identify the shared steps so that you can later convert them into reusable test methods inside your application-specific automation libraries to reduce your overall automation effort and redundancy. Figure 5 shows how shared steps appear in MTM.

Figure 5 Sample Shared Steps in MTM

Define the Order of Execution for Your Automated Tests

Ensure that you have defined the order of execution for the automated tests that have to be run. Figure 6 shows this configuration, leveraging the Order field.

Figure 6 Sample order of execution defined for automation scripts inside MTM

Develop Proof of Concept

Once you know the scope of your project, you must ensure that Visual Studio coded UI testing supports your target application or technology by doing a proof of concept (POC) on key scenarios. In many cases custom controls and other components are not automatable or supported by coded UI out of the box, they need further customization, or they aren’t worth automating because of low ROI. The outcome of this activity should help you decide your automation design and framework. Make sure to mark the non-automatable test cases as out of scope for automation using the Automation Status field in MTM.
Your POC must address the following factors:                                                                                             

• Do a feasibility study to ensure Visual Studio 2010 coded UI supports your application. Identify key scenarios of your application and ensure that Visual Studio 2010 coded UI supports them by coding a few scripts and checking the results.
• Identify and track third-party or custom controls in your application. Ensure that you call out the tests that Visual Studio 2010 doesn’t support as out of scope for automation.
• For testability, work with the development team to ensure that the properties of the objects are exposed and set appropriately for smooth automation. Working with the development team right from the design phase is crucial to making automation testing easier later in the cycle.

Perform Automation Estimation

The automation estimation will decide the success or failure of your automation project. Be aware that automation can take much more time than anticipated if the key tasks involved in the development of the automation lifecycle are not clearly thought through. Inaccurate estimation can result in less automation coverage and inability to obtain ROI that was promised to the stakeholders.
The following major activities in each phase of automation must be estimated.

Automation Initiation

• Doing a tool/framework feasibility study
• Developing a proof of concept

Framework Design/Implement

• Designing and implementing various framework components
• Testing the framework components and end-to-end testing
• Training resources on the framework/tool
• Setting up the environment

Script Development

• Creating scripts
• Debugging scripts
• Reviewing scripts
• Incorporating review comments

Script Execution

• Setting up environment
• Analysing the results

Your initial estimation should also include efforts in the following areas in addition to the automation design and execution effort:

• Estimating automation planning time and tasks, including POC.
• Estimating the environment preparation (installation, configuration).
• Identifying the shared steps.
• Writing reusable automation components (libraries).
• Handling any third-party or custom controls in your application.
• Reviewing code, including walkthroughs and compliance with the defined automation standards.
• Dealing with automation reporting and analyzing results.

Once the scope, feasibility and estimates are clarified, you can move to the next logical step, which is creating your automation solution.

Creating Your Automation Solution

Whether you create a project from scratch or use an existing template, you need to add your project to source control for version management and change tracking. To create a new project using Visual Studio, in the New Project dialog box, just select Test and make sure that the Add to Source Control check box is checked.
Here are some recommendations that can help when you're using Team Foundation Server (TFS) for automation source control:

• For the project check-in path, all application-specific and project-specific automation code should be maintained under the respective project node of the TFS being used for the projects.
• The recommended source control path can be something similar to <Project-Node>/Main/Test/Test Automation or can follow your TFS folder structure.
• Follow the standard and custom policies the project administrator enforces, such as entering “comment” every time you check in the code and checking in the code at the end of every day. For more information about configuring check-in notes, go to http://msdn.microsoft.com/en-gb/library/ms181460.aspx.

For more on version control in Visual Studio 2010, go to http://msdn.microsoft.com/en-us/library/ms181368.aspx.
To open an existing test project and use it as a template, launch Visual Studio, go to File, open an existing project and browse to the CodedUISampleFramework template, which is available for download with this article. Then add it to source control and change the properties as needed for your new project.
The following folder structure is used for the template project in this example:

• Config: Includes all the application configuration details and playback settings, as shown here:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key ="URL" value="http://bing.com"/>
    <add key="Test" value="Test Value"/>
  <appSettings>
<configuration>

For more on application settings, refer to http://msdn.microsoft.com/en-us/library/a65txexh(v=VS.100).aspx.

• Library: All the libraries, including application level, data access, exceptions and utilities, go here.
• Resources: All the keys/strings (e.g., SearchTimeOut, WaitForReadyTimeOut settings and so on) that need to be globally defined can be added here, rather than hard-coding their values. The resource file key SearchTimeOut is used here to set playback settings rather than hard-coding each setting:

/// <summary>
/// Set the SearchTimeOut Playback Option
/// <summary>
public static void SetThinkSearchTimeOut()
{
    Playback.PlaybackSettings.SearchTimeOut = 
        Convert.ToInt32(PlaybackConfiguration.SearchTimeOut);
}

• Scripts:  Store the test and components such as Test Initialize and Test Cleanup in these .cs files.
• Test Data: Contains the .CSV or .XML test data files for the data-driven automation test.
• UI Maps: Contains the test object repository along with any test methods or assertions.
• Main Class file: One class that acts as a single point interface to all the test methods and contains instantiations of the UI maps.

Designing and Developing Your Automation Framework

The most important phase of automation lifecycle development involves designing the automation framework. If you don’t spend time up front to come up with a good design, you’ll face a lot of rework later. Even if you're eager to start scripting, don't skimp on this phase. You need to spend a good deal of effort in coming up with a design/framework that suits your project requirements before your write your automation tests. This design/framework then serves as a guiding light for team members to follow a standard and consistent automation approach.
This section goes over the automation framework design for coded UI projects using the CodedUISampleFramework template project. Figure 7 illustrates the proposed architecture, which is designed in three tiers for maintaining automated testing that promotes high modularity and maximum reuse with minimum maintenance effort in the longer run.

Figure 7 Proposed three-tier automation architecture
Follow the steps in the following paragraphs to design your automation framework.
If you have created a project from scratch, add multiple UI Map files. In the template project, there are already two UI Map files. For managing object repositories, there are two scenarios: a single UI Map file and multiple UI Map files. By default, only one UI Map file is created per project, which is appropriate for a small project.
For large applications, using multiple UI Map files is recommended. Using multiple UI Map files can provide the following benefits:

• Each map can be associated with a logical subset of the application, making changes easier to manage.
• Each tester can work on a section of the application and check in the code without interfering with other testers working on other sections of the application.
• Additions to the application UI can be scaled incrementally with minimal effect on tests for other parts of the UI. For information on testing a large application with multiple UI Map files, refer to http://msdn.microsoft.com/en-us/library/ff398056.aspx.

After you add a UIMap file, the Visual Studio window minimizes and the Coded UI Test Builder dialog box is displayed, which can be used to record steps or add specific object properties to your UI Map file.
Open the UI Map .cs files to add user-friendly names to the constructor, as shown here:

#region Constructor
public HomepageUI()
{
    searchText = BingBrowser.BingHomepage.SearchEdit;
    searchSubmit = BingBrowser.BingHomepage.SearchButton;
}
#endregion

Now go to the Main Class file (Bing.cs in the example) and instantiate each multi UI Map file, as shown in Figure 8.
Figure 8 Instantiating each multi UI Map file

namespace CodedUISampleFramework
{
    public class BING
    {
        private static HomepageUI homepageUI;
        private static SearchResultsUI searchResultsUI;
        public static HomepageUI HomepageUI
        {
            get
            {
                if (homepageUI == null)
                {
                    return new HomepageUI();
                }
                return homepageUI;
            }
            set { BING.homepageUI = value; }
        }
        public static SearchResultsUI SearchResultsUI
        {
            get
            {
                if (searchResultsUI == null)
                {
                    return new SearchResultsUI();
                }
                return searchResultsUI;
            }
            set { BING.searchResultsUI = value; }
        }
}

The UI Test Editor is a handy new feature in Feature Pack 2 that makes it easier to manage your object repository. To find out about the Coded UI Test Editor, refer to http://msdn.microsoft.com/en-us/library/gg269469.aspx.
Next you need to add libraries to your project or create and add them if necessary. You need to manage two library types: application functional libraries using multiple UI Map files, and common library and helper classes. For the example in this article, the libraries for Bing are already created.
For application functional libraries using multiple UI Map files, you must ensure that common functionalities you identify in the planning phase are automated as reusable methods. These methods are then invoked from your coded UI test methods. You create functional libraries the same as you would functional modules for your application.
For common libraries and helper classes, first ensure that common operations related to database connections, Excel data access (read/write), XML parsing and string/date manipulations are referenced from the common library repository and shared across the projects. Don't rewrite them from scratch for every project.
All common libraries need to be browsed and added to the automation solution as References through the Add Reference dialog box.
Now you need to ensure that any common helper classes that are technology specific (e.g., Windows Presentation Foundation, Windows Communication Foundation, SharePoint, Silverlight or .NET Framework) are referenced in your solution.
All common code and libraries need to be maintained at a centralized node that is accessible (read-only) to all the projects and applications included for reusability. Only the automation group member has add/edit permission on this node to control the change.
The libaries created for and referenced in the example in this article are shown in Figure 9. The libraries are based on the shared steps and scenarios identified in the automation planning process.

Figure 9 Libraries referenced in the article's example
The code in Figure 10 shows an example of the application-specific library methods used in the solution.
Figure 10 Application-specific library methods

/// <summary>
/// Enter the search text in Search Edit
/// </summary>
/// <param name="searchText">The text to be Searched in Bing</param>
public static void EnterSearchText(string searchText)
{
    BING.HomepageUI.searchText.Text = searchText;
}
/// <summary>
/// Click on the Search Submit button
/// </summary>
public static void SubmitSearch()
{
    Mouse.Click(BING.HomepageUI.searchSubmit);
}

Figure 11 is an example of common and utilities-based methods.
Figure 11 Common and utilities-based methods

/// <summary>
/// Navigate to specified URL
/// </summary>
/// <param name="urlString">The url to navigate</param>
public static void Navigate(string urlString)
{
    browser.NavigateToUrl(new System.Uri(urlString));
}
/// <summary>
/// Launch and Navigate to specified URL
/// </summary>
/// <param name="urlString">Teh url to navigate</param>
public static void LaunchAndNavigate(string urlString)
{
    Launch();
    Maximize();
    Navigate(urlString);
}

The next step is to add the coded UI scripts and test methods to your project. You automate your automatable test cases as coded UI test methods using the application libraries, utilities and UI Maps defined earlier.
First, set the test context, which provides information about and functionality for the current test run:

private static TestContext bingTestContext;
public static TestContext BingTestContext
{
    get { return BingScriptsBase.bingTestContext; }
    set { BingScriptsBase.bingTestContext = value;}
}

Second, perform a test initialize. Use the following code before running each test, for tasks such as instantiating UI Map files, setting TestContext for logging and the like.

#region TestInitialize
//Use TestInitialize to run code before running each test
[TestInitialize()]
public virtual void MyTestInitialize()
{
    Browser.CloseAllBrowsers();
    BING.HomepageUI = new HomepageUI();
    BING.SearchResultsUI = new SearchResultsUI();
    BingTestContext = testContextInstance;
    //PlaybackSettings();
}
#endregion

Third, add your Test Methods, as shown in Figure 12.
 Figure 12 Adding test methods

[TestMethod]
[Description("This test is to test that the search string is displayed in the second page")]
[Owner("Viswa")]
[Priority(0)]
[TestProperty("TestcaseID","12342")]
public void SearchText()
{
   BingScriptsBase.BingTestContext.WriteLine("Test Start");
   Browser.LaunchAndNavigate(AppConfig.GetValue("URL"));
   Homepage.EnterSearchText("Bill Gates"); //Not recommended to Hardcode values in scripts    
   Homepage.SubmitSearch();
   //Not recommended to use Static Wait Statements as given below 
   Playback.Wait(5000);
   //Not recommended to Hardcode values in scripts 
   Verify.WaitForControlPropertyEqual(BING.SearchResultsUI.searchText, 
      "Text", "Bill Gates", 15000);
   BingScriptsBase.BingTestContext.WriteLine("Test Stop");
}

Certain elements are not recommended, such as using a static playback interval or hard-coding test data. In the next section you'll find out how to handle these instances.
Finally, you need to perform test cleanup. Run code similar to the following after each step.

#region TestCleanup
//Use TestCleanup to run code after each test has run
[TestCleanup()]
public virtual void MyTestCleanup()
{
    BING.HomepageUI = new HomepageUI();
    BING.SearchResultsUI = new SearchResultsUI();
    Browser.CloseAllBrowsers();
}
#endregion

Now it’s time to add assertions and checkpoints. For validation in coded UI testing, you need to use Assertions. There are two ways to create assertions: using Coded UI Test Recorder (CUIT) or coding.
Obviously, creating assertions with CUIT is faster than coding them by hand. CUIT, shown in Figure 13, comes in the box with coded UI. It’s simple to use: Just add the comparator and comparison value you want to use and click OK.

Figure 13 Coded UI Test Recorder
You can also create assertions by writing your own code and capturing properties against expected values to validate at run time. Here’s an example:

Verify.WaitForControlPropertyEqual(BING.SearchResultsUI.searchText, "Text", "Bill Gates", 15000);

Here a Verify method in the utility waits for the control property to be equal to some text value for a certain period of time, returning PASS if found or FAIL if not.

public static void WaitForControlPropertyEqual(UITestControl control, 
    string property, string expectedValue, int millisecondsTimeout)
{
    Assert.IsTrue(control.WaitForControlPropertyEqual(property, 
        expectedValue, millisecondsTimeout),
        string.Format("Expected : {0} Actual : {1}", 
        expectedValue, control.GetProperty(property)));
}

Next you must ensure that every variable is parameterized by passing the test data to each test, using the data-driven capabilities of Visual Studio 2010. As shown in Figure 14, you can right-click the coded UI test and select Properties to view the test’s properties.

Figure 14 View your test properties
To make this a data-driven test, you first have to create a data source for the test to draw from. On the Data Connection String property, click the ellipses button to start the New Test Data Source Wizard.
As shown in Figure 15, there are three options for creating a data source:

• Database allows you to connect to a database or an Excel spreadsheet.
• CSV File allows you to use a comma-delimited file.
• XML File allows you to use an XML file.

Figure 15 Options for creating a data source
When you add a data source, a Data Source attribute is added to the coded UI Test, as follows:

[DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV", 
    "|DataDirectory|\\data.csv", "data#csv", 
    DataAccessMethod.Sequential), 
    DeploymentItem("data.csv"), TestMethod]

Let’s look at one of the data-driven tests from the example in this article. Here is a method in the data access library (Utilities) to read the data from data sources using the test context:

namespace CodedUISampleFramework.Library
{
    public class Data
    {
        public static string GetValue(string columnName)
        {
            return (string)BingScriptsBase.BingTestContext.DataRow[columnName];
        }
    }
}

Another method that is part of the application library returns the data from the data source and inputs the same to the application, using the above data access library method:

/// <summary>
/// This method enters Search Text and executes iteratively on the available values
/// </summary>
/// <param name="searchText">The current text used for search</param>
public static void EnterSearchText(out string searchText)
{
    searchText = Data.GetValue("SearchText");
    BING.HomepageUI.searchText.Text = searchText;       
}

Next, as shown in Figure 16, the data source, which is stored in .CSV format, is tested.

Figure 16 Testing the data source
Finally, the data-driven test method in Figure 17 shows the code to consume the data source defined earlier.
Figure 17 Sample data-driven test method

#region Scripts
[Description("This test will search many search strings")]
[Owner("Viswa")]
[Priority(0)]
[TestProperty("TestcaseID", "12341")]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV", 
  "|DataDirectory|\\TestData\\SearchData.csv", 
  "SearchData#csv", DataAccessMethod.Sequential), 
  DeploymentItem("CodedUISampleFramework\\TestData\\SearchData.csv"), 
  TestMethod]
public void SearchTextMany()
{
    string searchText = null;
    Browser.Launch();
    Browser.Navigate(AppConfig.GetValue("URL"));
    Homepage.EnterSearchText(out searchText);
    Homepage.SubmitSearch();
    SearchResults.VerifyResults(searchText);
}

Once your automation test candidates are designed and developed and your scripts are written and debugged, you need to run them in a batch or as part of a suite. There are various way to do this and factors to keep in mind, as you’ll see in next section.

Automation Execution and Deployment

To prepare for automation execution and deployment, ensure that you have configured your test settings to capture logs, code coverage, test impact and the like. You also need to keep in mind that your script should be able to successfully run in multiple environments, for example, test to the user acceptance test (UAT).
After automation design and debugging is completed, you can run and deploy your automation scripts using the following steps: configuring test settings, deploying the test to multiple environments and grouping and classifying your test methods.

Configuring Test Settings

Configuring test settings involves two steps. First, you must select appropriate data diagnostics for code coverage, event log, test impact and so on, as shown in Figure 18.

Figure 18 Selecting data diagnostics
Next, you must define other settings, such as Roles, Test Timeouts, Setup and Cleanup Scripts, as shown in Figure 19.

Figure 19 Defining settings

Deploying Your Test to Multiple Environments

The next challenge is deploying to different environments. You need to run the same test across multiple environments to ensure that everything still works correctly. The sequence to follow is development, testing and UAT at every stage.
For example, say you want to run these tests after each deployment to each environment. In the example in this article, each environment has a different URL, username and password, so the following code is used:

public UIMap()
{
    // Bind the parameters using Environment Variables.
    string testServer =
        Environment.GetEnvironmentVariable("TestServer");
    string testUserName =
        Environment.GetEnvironmentVariable("TestUserName");
    string testUserPassword =
        Environment.GetEnvironmentVariable("TestUserPassword");
}

For more on running the same test in different environments, go to blogs.msdn.com/b/gautamg/archive/2010/02/23/how-to-get-same-test-running-under-different-environments.aspx.

Grouping and Classifying Test Methods

In this step you group and classify test methods using various parameters. This lets you run groups of tests based on their assigned categories without the requirement to maintain test lists, as shown here:

[TestMethod]
[Description("This test will Verify Related Searches for a Search Condition")]
[Owner("Viswa")]
[Priority(0)]
[TestProperty("TestcaseID", "12344")]
[TestCategory("Daily")]
public void VerifyRelatedSearchesLinks()

Now go to Test View and filter and view your tests by setting the parameters shown in Figure 20.

Figure 20 Setting parameters in Test View

Executing at the Command Line: MSTest.exe

MSTest.exe is the command-line utility used to run tests. This application has several options that you can use to customize your test runs. You can specify these options in any order on the MSTest.exe command line. Here’s an example:

mstest /testcontainer:"C:\TestProject2\generictest1.generic" 
           /test:testproject32\generic

For more on MSTest.exe command-line options, refer to http://msdn.microsoft.com/en-us/library/ms182489.aspx.

Coding Standards and Best Practices

You have now completed your brief tour of the automation development lifecycle, from automation planning through automation execution and deployment. Don’t forget that automation scripts are code. As with any code, to ensure high-quality automation code, you need to adhere to coding standards and follow best practices. Automation tests must be readable and easy to troubleshoot as well as optimized for long-term maintainability.
The following recommendations will help guide you through your code automation. Every member of the team is responsible for sticking to best practices throughout the automation development lifecycle.

Object Synchronization

Object synchronization points make scripts more resilient and robust. You need to consider two factors with object synchronization: waiting for control options and dealing with static delay.
When waiting for control options, you insert synchronization points by waiting for the object to be ready and enabled before trying to perform any critical action, such as WaitForControlReady(),WaitForControlExist().
The second approach is static delay. While you should use WaitForControl options as much as possible, in some unavoidable cases you must hard-code wait/sleep and use the Playback.Wait() API instead of the Thread.Sleep() API.

Exceptions and Error Handling

Ensure that you use the exception handling feature that C# provides in all your scripts—that is, be sure to use try, catch and finally. Try encloses the statements that might throw an exception, catch handles an exception if one exists, and finally can be used for any cleanup process.

Logging

Here is some sample logging code:

[TestMethod]
public void CodedUITestMethod1()
{
    Console.WriteLine("Console.WriteLine()");
    Console.Error.WriteLine("Console.Error.WriteLine()");
    TestContext.WriteLine("TestContext.WriteLine()");
    Trace.WriteLine("Trace.WriteLine()");
    Debug.WriteLine("Debug.WriteLine()");
}

If you check the test result window, you should see output that looks like Figure 21.

Figure 21 Test result output
Each statement that represents different logging options in Visual Studio, shown in the sample logging code in Figure 21, has the following advantages and disadvantages.

TestContext.WriteLine

Advantage: Shows up properly in a separate section in test result.
Disadvantage: You’ll have to pass TestContext around from your main test class to other classes (like UI Map class in coded UI Test) to use it.
Recommendation: Prefer this over other methods for tracing in test code.

Trace.WriteLine

Advantage: Shows up in the Visual Studio Output window during debugging.
Disadvantage: The message here could be lost among other trace messages from other components (or product code).
Recommendation: Use it in your product code but avoid in test code. Using Trace.WriteLine is a good way to separate messages from product code and those from test code. You can use Trace.WriteLine in scenarios where either TestContext isn’t present or you want to determine the sequence of operation between test and product code.

Debug.WriteLine

Debug.WriteLIne has the same advantage and disadvantage as Trace.WriteLine, except that it shows up only for the debug build

Console.WriteLine and Console.Error.WriteLine

Although they work, these alternatives are tricky. The test harness redirects the standard output and standard error to capture the output\error from your product code, not to trace. You should avoid using these unless you really know what you’re doing. In short, use TestContext.WriteLine wherever possible.

Naming Conventions

When you create a method, use a meaningful name instead of the default name. A meaningful name helps identify the purpose of the method. In the application being tested, also use meaningful names for the UI controls. Customized control names are more user friendly than automatically generated ones.

Comments

Use extensive and meaningful comments in your code. Remember that the code you write will be around for a long time and has to be understood, extended and debugged by others who don’t have the entire context you had when you originally developed the code. Unless your comments are complete and substantive, even you will be hard-pressed to understand the code a few weeks or months later.

Startup and Cleanup Scripts

Using code, create a test in your test project. In the test class, create methods with the [TestInitialize] and [TestCleanup] attributes. These attributes will be run before and after each test method.
If you want to run any piece of code before and after all tests in the test class, create static methods with the [ClassInitialize] and [ClassCleanup] attributes.
To run any piece of code before and after all tests in the assembly, create static methods with the [AssemblyInitialize] and [AssemblyCleanup] attributes.

The Do’s and Don’ts of UI Automation

UI automation can be a tricky business. To help you get the best results, follow the advice, guidance and best practices for writing coded UI tests in the following Do and Don't lists. The information in these lists comes from various forums.

Do

• Ensure that each recorded method acts on a single page, form or dialog box. Create a new test method for each new page, form or dialog box.
• Use CUIT whenever possible.
• Explicitly set focus to the window on which the test case is expected to input data.
• When possible, limit each recorded method to fewer than 10 actions. This modular approach makes it easier to replace a method if the UI changes.
• Create a separate UIMap file for each module being tested in the application.
• If you’re creating assertions by coding with the API, create a method for each assertion in the part of the UIMap class that is in the UIMap.cs file. Call this method from your test method to execute the assertion.
• Capture screen shots for failures. They will help you in debugging.
• Leave the UI in a known state after a test case is done executing. For example, close the wizard even if the test case is to test the first screen of the wizard by cancelling out of the wizard after the first screen.
• Validate that the object being created using the UI against the one being stored in the back-end system.
• Log before, not after, you do something.
• In code reviews for test cases or in new framework automation support, review the log messages to make sure what the test did is clear from just the log.
• Log the origin of messages. If the framework logs, say that it logged; if the test case logged, say that.
• Have two assertion modes that are configurable: fail instantly and delay assertion of failure until the test has completed.  
• Log nonfatal exceptions so as not to abort a test, bubble fatal exceptions up to the test and let the exception assert failure.
• Log every valid UI operation being performed in the test case.
• Use resource files for all data required to be entered in the UI.
• Use refactoring, which implies removing a block of code that is being used repeatedly throughout the program into a single function. Refactoring helps in code reuse, makes the code cleaner and improves readability. 
• Use class constructors in meaningful ways. In any class, avoid unnecessary overloading of constructors and minimize the amount of work done in a constructor. Use constructors mainly for initializing members. It is always good practice to have accessory methods (get/set) to expose member variables and also to initialize them.
• With strings, use Compare () instead of "==" or "Equals ()"; that is, use string.compare() so you don't have to worry about NullPointerException. With Equals(), you have to make sure that the object that calls Equals() is not null. 
• Use list<class name> instead of arrays. Arrays are not type safe, so it is advisable to use the inherent .NET 2.0 collection objects such as IList<T> and ICollections<T>. They provide additional type checking during compile time.               
• Use “this.” Whenever you use a member variable of a class, put “this” in front of the variable to improve readability and avoid conflicts when the parameter and member variable names are the same.  
• Use .NET attributes for test cases. These attributes provide a mechanism to enable sending additional data to the test code during run time. They also help in keeping information associated with the test case in the code itself.

Don’t

• Don’t modify the UIMap.designer.cs file directly. If you do this, the changes to the file will be overwritten.
• Don’t log multiple times.
• Don’t sleep after every UI operation.
• Don’t give unusually long timeouts for every major operation.
• Don’t fail immediately after an unexpected screen.
• Don’t relaunch the UI application for every test case because this process is too time consuming.
• Don’t ever hard-code strings in test cases. This includes control IDs, menu paths and so on. Even control IDs and menu paths change over time.
• Don’t use too many parameters in a method or a function. Numerous parameters indicate a lack of abstraction. To avoid passing too many individual parameters to a method or function, encapsulate the parameters in a separate class or structure.
• Don’t write lengthy functions. If a function is longer than a page, break it into smaller functions. If the code has a switch statement or straight-line code with no loop back, a longer function is acceptable. In almost all other cases, however, you should simplify the code by splitting it into multiple functions.

Additional Resources

Table 1 provides some links that will help you get started with coded UI.
Table 1 Coded UI Links

Online Help	Testing the User Interface with Automated UI Tests
Forum	Visual Studio UI Automation Testing (includes CodedUI)
Product Overview	Coded UI Test
Webcasts and Videos	Functional UI Testing Visual Studio Team System 2010: Manual Test To Coded UI Test
Guidance and References	Best Practices for Coded UI Tests
Coded UI Test Bloggers	Anutthara Bharadwaj Gautam Goenka Balachander G. Subramaniam Rubel Singhal Rituparna Paul Team Blog
What’s new in Beta2	What’s new for testers doing UI automation

Table 2 shows the details of each UI framework supported as well as their differences, known issues and workarounds.
Table 2 Resources for Coded UI Frameworks

Web	IE Plugin Part 1 IE Plugin Part 2 UI Test Framework Firefox support
Winforms	Winforms Plugin Part 1 Winforms Plugin Part 2
WPF	WPF Plugin Part 1 WPF Plugin Part 2 Calendar support: http://blogs.msdn.com/b/tapas_sahoos_blog/archive/2010/12/10/adding-new-control-support-in-a-coded-ui-test-plugin.aspx
SharePoint	UI Test Framework SharePoint support
MFC/Win32	UI Test Framework MFC support