I am currently hiring a new developer and since these opportunity are far and between nowadays, I want to make sure I pick the best one for our environment.  I am already doing all of the basics: recruiter interviews, phone interviews, in person interviews, each with increasingly more in-depth technical questions.  But, I wanted to add an additional step of a practical interview.  Basically, come up with a development request, with as little information as possible, so that I could see where these people go from there.

Here is the example:

Development Request:

We need an end-to-end solution that will display the results of data collected from a set of field vendors.  We need the data available in the report as soon as the upload is complete.

This is the information that we will be collecting:

• Name
• Age
• Gender
• Favorite Color

We need a report that will display this information in the following ways:

• Percent of Favorite Color broken down by Gender/Age (age broken at 25 into Under/Over)
• This report should allow us to filter on Vendor.

What questions do you have?

What technologies will you use to facilitate this request?

Pretty simple, right?  It’s actually a more complete request that I usually get, so I think it’s more than fair and something that shouldn’t take more than 1 hour to whip out.

Since we are primarily a Microsoft tech stack shop, I was looking for the person to answer the question, “What technologies will you use to facilitate this request?” with:

• SQL Server 2008
• SQL Server Integration Services
• SQL Server Reporting Services
• Visual Studio 2008 (.NET 3.5) in C#

Here is how I went about the project:

1. Open SQL Server Management Studio 2008
   1. Create a new database called ApplicationX
   2. Create a new user call ApplicationX_DBO and give this user dbo_owner privledge on the newly created database.
   3. Create tables called Respondent and Vendor (use nvarchar() datatypes for the fields that will be text in Excel)
   4. Create a Foreign Key Relationship between Respondent and Vendor
   5. Create a View called SurveyData
   6. Add a few Vendor records (4 should be good for demo)
   7. Open Excel
      1. Create the following columns VendorID, Name, Age, Gender, FavoriteColor
      2. Create a random data generator, using the IF() and RandBetween() functions
      3. Save this file 6 times as Vendor#.xls (replace # with 1,2,3,4,5,6), make sure that you change the VendorID column to correspond the respective file.
      4. Copy Vendor(5 & 6).xls to c:\temp\upload
      5. Make sure c:\temp\upload has ASPNET account has Read/Write access to this folder.
      6. Open SQL Server Business Intelligence Studio 2008
         1. Create a new Integration Services Project called ApplicationX_SSIS
         2. Right click the SSIS Package “package.dtsx” in the Solution Explorer and rename it to ApplicationX_SSIS.dtsx.
            
         3. Create an Excel connection to Vendor5.xls file
         4. Create an OLE DB Connection to your name database called ApplicationX.
            
         5. Drag a Data Flow Task to Control Flow design surface and double click on it to go to the Data Flow design surface.
            
         6. Drag an Excel Source object to the design surface and configure the Connection and Columns properties.
         7. Drag an SQL Server Destination object to the design surface and connect the output from the Excel Source by dragging the green connection link over it.
            
         8. Configure the Connection by linking it to our Respondent table and define the mappings (should be automatic).
         9. Run it, and you should now have data in your Respondent table J
         10. Now, we need to add logic to loop through all of the files in our c:\temp\upload folder.
         11. Drag a For Loop Container control to the Control Flow design surface
         12. Open the properties for the Excel Connection Manager and copy the ConnectionString value.  It will look like this:
             
         13. Create a Package Variable called FileName and set it to a datatype of String, with scope as Package
             
         14. Update the connection string to the following (copy exactly):
             “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] + “;Extended Properties=\”Excel 8.0;HDR=YES\”;”
         15. Paste the new connection string into the “Expression” property of the Excel Connection Manager:
             
         16. Configure the Foreach Loop Container as a Foreach File Enumerator in the Collection settings, as well as the other necessary settings.
             
         17. Drag your original Data Flow Control inside the Foreach Loop Container.
         18. Lastly, let’s clean up our files that we have imported, drag a File System Task control inside of your Foreach Loop Container and connect the Data Flow Control to it.
         19. Configure the File System Task Operation as Delete File and the IsSourcePathVariable to True and set the SourceVariable to our User::FileName.
             
         20. You are all set, run it and you should have a whole bunch more records in your Respondent table.
             
         21. All is good, let’s build our SSIS package.  Click Build > Build Solution
         22. Copy the ApplicationX_SSIS.dtxs from source folder C:\Programming\Visual Studio 2008\Projects\ApplicationX_SSIS\ApplicationX_SSIS\bin to c:\temp\SSIS and make sure that ASPNET has Read/Execute on this folder.
      7. Go to Report Manager
         1. Open the Report Manager URL (http://COMPUTERNAME:8080/Reports_MSSQLSERVER2008/Pages/Folder.aspx)
         2. Click New Data Source
            
         3. Fill in the Data Source details.
      8. Open Report Builder 2.0
         1. Create a new Report called ApplicationX_Results
         2. Double Click the Table or Matrix icon
            
         3. Select our newly created Data Source from the list.
            
         4. Select our View called SurveyData
            
         5. Add Gender to the Column Group, RespondentID to the Values as Count and FavoriteColor as Row Group.
            
         6. Finish the wizard and save your report to the Report Server.
         7. Copy the path to the report and save it in Notepad (or wherever).

1. Open Visual Studio 2008
   1. Create a new Web Site… project using the ASP.NET Web Site template called ApplicationX
   2. Go to Design Mode (unless you are really cool, then stay in the Source window)
   3. Add a Label and set the Text to Application X: File Uploader
   4. Add a Label and set the Text to “Select File to Upload:”
   5. Add a File Upload control and set the (ID) to FileUpload1
   6. Add a Button and set the (ID) to UploadButton and the Text to Upload
   7. Add the following code to the UploadButton_Click event:
      

      • // Before attempting to save the file, verify
        // that the FileUpload control contains a file.
        if (FileUpload1.HasFile)
        // Call a helper method routine to save the file.
        SaveFile(FileUpload1.PostedFile);
        else
        // Notify the user that a file was not uploaded.
        UploadStatusLabel.Text = “You did not specify a file to upload.”;

   8. While in the code view, add the SaveFile method to page class (_Default):

      void SaveFile(HttpPostedFile file)
      {
      // Specify the path to save the uploaded file to.
      string savePath = “c:\\temp\\uploads\\”;

      // Get the name of the file to upload.
      string fileName = FileUpload1.FileName;

      // Create the path and file name to check for duplicates.
      string pathToCheck = savePath + fileName;

      // Create a temporary file name to use for checking duplicates.
      string tempfileName = “”;

      // Check to see if a file already exists with the
      // same name as the file to upload.
      if (System.IO.File.Exists(pathToCheck))
      {
      int counter = 2;
      while (System.IO.File.Exists(pathToCheck))
      {
      // if a file with this name already exists,
      // prefix the filename with a number.
      tempfileName = counter.ToString() + fileName;
      pathToCheck = savePath + tempfileName;
      counter ++;
      }

      fileName = tempfileName;

      // Notify the user that the file name was changed.
      UploadStatusLabel.Text = “A file with the same name already exists.” +
      “<br />Your file was saved as ” + fileName;
      }
      else
      {
      // Notify the user that the file was saved successfully.
      UploadStatusLabel.Text = “Your file was uploaded successfully.”;
      }

      // Append the name of the file to upload to the path.
      savePath += fileName;

      // Call the SaveAs method to save the uploaded
      // file to the specified directory.
      FileUpload1.SaveAs(savePath);

      }
      }

   9. Add a Button and set the (ID) to LaunchSSIS and the Text to Import Files.
   10. Add the following code to the LaunchSSIS_Click event:

       Application app = new Application();
       Package package = null;

       //Load DTSX
       package = app.LoadPackage(@”C:\\temp\\SSIS\\Package.dtsx”, null);

       //Execute DTSX.
       Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

   11. Add a Label and set the (ID) to UploadStatusLabel and the Text to blank
   12. Add a HyperLink and set the NavigateURL to the report link from above.
   13. You should have something that look similar to this
       
   14. And when you click the link, you should get this:
       

All set.  A simple application that allows a user to upload data, have it ETL’d, and reported on and most importantly, tests that the developer know what Microsoft tool to use for the job.