Ivan Paniagua Monroy {Blog about BI, SQL, Azure & Microsoft.NET Technologies}


Adventure Works uses various software applications to manage different aspects of the business, and each application has its own data store. Specifically:

  • Internet sales are processed through an e-commerce web application. This data source contains data about customers and the orders that they have placed through the e-commerce web application
  • Reseller sales are processed by sales representatives, who use a reseller sales application. This data source contains data about resellers and the orders that they have placed through Adventure Works reseller account managers. Details of the sales employees themselves are stored in a separate human resources system.
  • Reseller payments are processed by an accounting application. Details of these payments are exported to comma-delimited files.
  • Products are managed in a product catalog and inventory system. This database contains data about products that Adventure Works sells, and that products are organized into categories and subcategories.
  • Some business partners, such as the marketing agency that Adventure Works uses to conduct marketing campaigns, provide data to Adventure Works through cloud-based data stores.

This distribution of data has made it difficult for business users to answer key questions about the overall performance of the business.

Additional requirements include:

  • The marketing department has requested demographic data about population by gender in the international sales territories in which Adventure Works operates.
  • The data stewards have noticed some data quality issues in the customer data, and requested that you provide a way for them to cleanse data so that the data base is based on consistent and reliable data. The data stewards have provided you with an Excel workbook containing some examples of the issues found in the data.
  • The data stewards are also concerned that customer data may include duplicate entries for the same customer that they would like to resolve.
  • The solution you are building for Adventure Works Cycles consumes product data from an application database. However, product data is created and updated in various systems throughout the enterprise, and you need to ensure that there is a single, consistent definition for each product.


Your challenge is to study the business scenario and requirements, and then design an solution to help Adventure Works build an enterprise data base


I return to old days working in a new proyect with mysql, and one of my task was move data from one table to another. Working with cursors inevitably when in MS SQL server you can do it in minutes.

This is my second error found working with the new Release of SQL Server 2012. This time with the version Release Candidate 0 (RC0, which was recently launched 02-12).

I create a package from Management Studio to import data from txt files (soo simple task), and I create successfully this package with the option that I show here:


But I have problems with the data itself, so I need to create another flow, validate data better and filter wrong rows. This can be do with SSIS, SQL Server Integration Package, and this new release comes with Visual Studio 2010 Shell.

This problem was reported through Microsoft Connect now fortunately this problem was solved in the final version.