Hi Baba,
There are two approaches for parameterizing requests in the recorded traffic with data stored in Excel sheets.
A. If you prefer scripting, you can create a handler for the OnLoadTestingRequest event and programmatically modify parameters of requests to be sent to the tested Web server during the load test run. To access data stored in an Excel sheet, you can create a DDTDriver object by calling the DDT.ExcelDriver method. With this object, you can retrieve data from your Excel sheet for subsequent parameterizing of the recorded traffic.
To modify request parameters from the script code of your OnLoadTestingRequest event handler, use the HTTPRequest object. By calling its URLVariables and BodyVariables properties, you can obtain the collections of parameters sent through the URL and body, respectively, of the needed request. You can then modify the needed parameters in these collections by using the values retrieved from your Excel sheet.
For more details, see the Modifying Recorded Traffic step of the Load Testing Advanced Tutorial - Using Scripts tutorial. It demonstrates how you can use scripts to parameterize requests from the recorded traffic by using values retrieved from an Excel sheet.
B. You can use database load testing variables to retrieve data from an Excel sheet and then assign values of such variables to the needed request parameters. This approach is easier than the one described above. It really doesn't require scripting at all. You can learn how to use load testing variables to visually modify your recorded traffic from the Load Testing Advanced Tutorial - Using Variables tutorial. Note that this tutorial demonstrates creation of load testing variables of the list type. To retrieve data from an Excel sheet, you need to create database variables:
1. Open the Tasks editor by double-clicking the appropriate project item in the Project Explorer.
2. Click the New Variable button on the editor's toolbar.
3. In the ensuing wizard, specify the name of the variable to be created and click Next.
4. On the second page, select Database variable and click Next.
5. On the Specify Connection Settings and Data Source page, specify the connection string used to retrieve data from an Excel sheet via the appropriate data provider. If you are going to load data from an .xlsx file (Excel 2007), see http://connectionstrings.com/excel-2007 for examples of the connection string format. If you use an .xls file, see http://connectionstrings.com/excel for examples of connection strings. Also, select Table as the data source type and click Next.
6. From the drop-down list on the Select Database Object page, select the name of the needed sheet stored in the specified Excel file. Note that you must enclose the selected name in brackets (for instance, [Sheet1$]). Otherwise, an error occurs.
7. On the Specify a column and selection type page, select the name of the table column from which you need to load data. Note that one database variable stores a collection of values from one column of the specified sheet. Also, in the Value Access Type section, you need to specify how the values from the collection will be used to parameterize requests of different virtual users (for instance, randomly or selected by a virtual user index). Click Next.
8. After previewing data loaded to the variable from an Excel sheet, click Finish in the wizard. That's all. The variable is ready. Now, you can use it to assign parameters to the needed request as it is described in the above mentioned tutorial.
If you need to use values from several columns of the specified Excel sheet, you should create appropriate database variables for each of the needed columns. Note that after you created one variable for the needed Excel sheet, you can create variables for other columns of this sheet in a much easier manner:
1. Select a previously created variable in the Tasks editor and copy it (for instance, by right-clicking and selecting Copy from the context menu).
2. Paste the copied variable (pressing Ctrl+V, for instance). A new variable with an automatically generated name appears in the editor. This variable has the same properties as the copied one does. It has the correct connection string and the sheet name already specified. So, you just need to modify some other parameters of the variable.
3. Double-click the newly created variable in the list and move through the ensuing wizard to modify the variable name, column name and value access type (if needed).
Hope this information will be helpful for you.
