/************************************************************************************************/
/*ReadMe.txt */
/************************************************************************************************/
Project: US Census Data

/****************/
/*Release 1.0 */
/****************/
The purpose of this project in release 1.0 is to load two types of files from the US Census Bureau's
American Community Survey. In release one we will load the ACSSF Geography file for all 53 states
and territories. We will also load the ACSSF Summary File with the sequence number of 0001 for all
53 geography files.

This project consists of a number of SQL Scripts, PowerShell Scripts, and text files. The text
files are from the US Census Bureau, while all code is part of this project. This project requires
the use of PowerShell scripts and as such PowerShell Execution Policy needs to be set correctly.
Please see Get-ExecutionPolicy and Set-ExecutionPolicy as well as about_Execution_Policies in
PowerShell documentation. The PowerShell scripts are used to deploy all of the code to your
SQL Server. All of the scripts can be deployed manually if you want but it is a tedious process.

The Execute_US_Census_Data.ps1 PowerShell Script takes input to direct it to the correct SQL Server
and Database. This works with SQL Server2008R2 and higher as well as SQL Azure.

/****************/
/*Deployment */
/****************/
Open a Powershell change directory to the project folder and type .\Execute_US_Census_Data.ps1 <and press enter>

Example 1 (Trusted Local): .\Execute_US_Census_Data.ps1 "ServerName\SQLInstanceName" "DatabaseName"
Example 2 (SQL Login Local): .\Execute_US_Census_Data.ps1 "ServerName\SQLInstanceName" "DatabaseName" "UserName" "Password"
Example 3 (SQL Azure): .\Execute_US_Census_Data.ps1 "tcp:ServerName\SQLInstanceName,PortNumber" "DatabaseName" "UserName@ServerName" "Password"

Note: All steps are logged to Execute_US_Census_Data.log

/********************/
/*System Requirments*/
/********************/
PowerShell
PowerShell with the ablity to execute scripts
PowerShell with the Invoke-SQLCmd (import-module sqlps)
BCP.exe
SQL Server at least 2008R2 or SQL Azure
SQL Server account with ablity to create/alter/drop schemas, tables, and views

/************/
/*Manifest */
/************/
ReadMe.txt - This file
ACSSF - Folder to hold the ACSSF 0001 formated summary files
Geography - Folder to hold the ACSSF Geography files
ACSSF_Geography_File.fmt - BCP Format file for the ACSSF 0001 summary files
ACSSF_Summary_File_0001.fmt - BCP Format file for the ACSSF Geography files
ACS_2013_SF_1YR_Appendices.xls - US Census Bureau documentation
ACS_2013_SF_Tech_Doc.pdf - US Census Bureau documentation
BCP-in.ps1 - PowerShell script file to BCP files
BCP-inACSSF0001.ps1 - PowerShell script file to BCP ACSSF 0001 formated summary files (uses BCP-in.ps1)
BCP-inGeography.ps1 - PowerShell script file to BCP ACSSF Geography formated files (uses BCP-in.ps1)
CensusData.ssmssln - SQL Server Solution file
CensusData.ssmssqlproj - SQL Server Solution Project file
CensusData_CreateDB.sql - Create Database SQL Script (Not executed by default)
CensusData_V0R0000.sql - SQL Script to create dbo.database_version_information table (UtilityDB)
CensusData_V0R0001.sql - SQL Script to create ACSSF schema
CensusData_V0R0002.sql - SQL Script to create a table to hold the ACSSF Summary File 0001
CensusData_V0R0003.sql - SQL Script TBD (Most likely Counts)
CensusData_V0R0004.sql - SQL Script to create the ACSSF Geography file
Create-BCPFormatFile.ps1 - PowerShell script to generate BCP format files
Execute_US_Census_Data.ps1 - PowerShell script to execute all of the SQL and PowerShell scripts in the correct order
Sequence_Number_and_Table_Number_Lookup.xls - US Census Bureau documentation

Last edited Mar 6, 2015 at 12:37 AM by sfibich, version 3