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:

ZipFiles.jpg

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

BatchHistory.jpg

Last edited Dec 15, 2010 at 12:07 PM by Larry_Barnes, version 6

Comments

morlin Oct 28, 2011 at 7:54 AM 
Got there! – Eventually. – But only with a significant amount of fitting and tweaking. Main points (only) were: (i) [to be able deploy reports:] to enter Report Manager and grant necessary rights, in 2 places; - and (ii) e.g. InsertModifyReports refer to demo db AdventureWorks2008R2, but in practice I used (and had to use) the earlier AdventureWorks2008 [non-R2] demo db.