ImporterDesignNotes

The Importer will be written in SQL Server procedural language and will check three locations for csv extract files:

1) the Solaris server that contains the daily BioSense extract files,

2) the PHINMS queue tables that contain the daily DiSTRIBuTE files,

3) a local directory that will serve as a drop box for authorized CDC programs to supply files.

For location #1, Solaris userid/password will be used for authentication and SCP/SFTP will be used for download.

For location #2, SQL Server userid/password will be used for authentication and OLEDB/ODBC will be used to retrieve the file.

For location #3, Windows ADS will be used for authentication and SQLServer file-IO libraries will be used to retrieve the file.

Codes used by the Importer program

The rolling 10 day CSV file does not require counts of zero to be transmitted.

The rolling 10 days provides the ability for a source to update previous days. Each day, age, indicator and disposition has a single row. Initially this row is inserted once, but it can be updated by the subsequent data imports from a source. Should a source need to zero out a particular record, they will transmit a zero count and the existing GIPSE store record will have its count value updated to zero (not deleted from the store).

For example:

On July 2nd, a source submits a file like this:

Date, Zip3, Age Group, Indicator, Count, Disposition, Temperature

2007-07-01,300,1,EDVisits,51,1,1

A record is inserted into the GIPSE Store with:

date=2007-07-01

indicator_id=1 (the primary key for the "EDVisits-HighTemp" indicator)

zip3=300

age_id=1 (the primary key for the 1 age group-which means "<2")

service_area_id=1 (the primary key for the 1 service area- which means "Admit Disposition")

value=51

On July 3rd, the source submits a file like this:

Date, Zip3, Age Group, Indicator, Count, Disposition, Temperature

2007-07-01,300,1,EDVisits,70,1,1

This will update the existing record created on July 2nd with:

value=70

Finally, on July 4rth, the source submits a file like this:

Date, Zip3, Age Group, Indicator, Count, Disposition, Temperature

2007-07-01,300,1,EDVisits,0,1,1

This will update the existing record created on July 2nd and updated on July 3rd with:

value=0