Stonemeadow Solutions ETL Framework Documentation

User's Guide

The ETL Framework Users Guide can be downloaded from the following link:
StonemeadowSolutions_EtlFramework.docx

Installation Guide

This ETL Framework is packaged as a zip file and contains the following files:



Files:

• SQL - Directory containing SQL scripts used in the installation and testing
• SSISPackages - Contains two SSIS Solutions
  • Templates - ETL Framework compliant Master package and Execution package templates
  • AWDW - AdventureWorks ETL scenario
• SSRS - Reporting Services solution containing the ETL Framework reports
• Batch files
  • InstallEtlFramework.bat - Batch file that installs the ETL Framework and sample destination database and adds configurations for the AdventureWorks ETL sample
  • InsertModifyRecords.bat - Batch file that Inserts and modifies records in the AdventureWorks OLTP database. Used to test the installation
  • dtexsyntax.bat - Batch file that runs the AdventureWorks master package

Installation steps:

• Unzip SMSEtlFramework.zip into a directory on your system
• Edit the InstallEtlFramework.bat file (in the SMSEtlFramework sub-directory) and Modify the variables passed into loadconfiguration.sql to reflect your environment (the command line syntax is shown below)
  • sqlcmd -i sql\loadconfiguration.sql -v PACKAGE=MPAdventureWorks CONFIGURATIONID= 1 SERVER=localhost SOURCEDB=AdventureWorks2008R2 ETL_DIRECTORY="c:\test\SMSEtlFramework\SSISPackages\AWDW\"
  • SERVER - Server name
  • SOURCE_DB - AdventureWorks OLTP database
  • ETL_DIRECTORY - Directory where the ETL packages reside (make sure "\")
• Save your changes run this file from a Command line prompt
• Open the SSRS Reports solution (Reports.sln within the SSIS sub-directory)
  • Deploy the reports, make sure the TargetServerUrl value within Project properties is set to a valid instance of SSRS
• Run the DtexecSyntax.bat file from a command prompt twice.
• Go to the SSRS Batch History report to review the batch summary results
  • The first invocation will fully load all records
  • The second invocation should ignore all of the records
• See Batch 1.1 and 2.1 results within Figure 2 in the StonemeadowSolutions_EtlFramework.doc to confirm that the status and record counts are the same
• Run the insertmodifyrecords.bat file, this will create a new customer and modify an existing customers information
• Run the DtexecSyntax.bat file from a command prompt
• Invoke the ETL Framework Batch History Report. The results should be the same as the screenshot below