SQLServer2CSpro

Convert SQL server database to CSPro data file

Takes census/survey data stored in tables in SQL Server and converts to CSPro format for further processing.

Consists of two programs:

1. SQLServer2Dictionary: generates a CSPro data dictionary from selected tables in SQL Server database

2. SQLServer2CSPro: given a CSPro data dictionary generated by SQLServer2Dictionary, converts data from the SQL Server database to a CSPro data file in text format.

Tables in the SQL Server database are mapped to records in the CSPro data dictionary.

The systems makes a few assumptions:

• Table names in the SQL server database will match record names in the CSPro dictionary
• Column names in the SQL server database will match item names in the CSPro dictionary
• The items used as id-items in CSPro will correspond to columns appearing in each table in SQL server, with the same name, in order to link together rows in the same case.

SQLServer2Dictionary is a WPF program allowing the user to select tables and id-items from SQL server. It is meant to be run rarely; only when the database schema changes.

SQLServer2CSPro is a console application that is meant to be run regularly (e.g. on a scheduled task) whenever the data in the database are modified.

Requirements

• Visual Studio 2017
• SQL Server
• CSPro 7.1

SQLServer2Dictionary

Creates a CSPro dictionary from a SQL Server Database. Drag and drop tables from the database onto levels in the dictionary. Drag and drop items from these records to be id-items for the records.

The dictionary that is created will contain a record for each table selected and that record will contain items of the appropriate types for each column in the database table. The

You can also specify a query template to be used to retrieve the value set for each dictionary item. This query should return two columns: the codes and the labels for each response for the item. When the query is run, %item% in the template will be replaced by the label of the dictionary item. For example if each value set is in a table named "tbl_vs_" followed by the column name with columns "code" and "label" then you would use:

SELECT code, label FROM tbl_vs%item%

If all the responses are in a single table named "tbl_response" with columns "code", "label" and "question" you would use:

SELECT code, label FROM tbl_response WHERE question=%item%

SQLServer2CSpro

Using the dictionary generated by SQLServer2Dictionary this program extracts the data from SQL Server and writes out a CSPro data file. It takes as input the CSPro data dictionary and the database connection string.

SQLServer2CSpro extracts data from the tables in the SQL Server database that correspond to records in the CSPro dictionary. These records are written to stdout in CSPro text file format. Records in the CSPro dictionary are matched to tables in the SQL Server database based on the record label in the dictionary. In other words the label (not name) of the record in the CSPro dictionary must match the name of the table in the database. In cases where all tables all have a common prefix like tbl_ it is possible to supply that prefix when matching records to table (see tablePrefix in the usage).

Similarly variables in the CSPro dictionary are matched to columns in the database by taking the item label (not name) from the CSPro data item and finding a column with a matching name in the database table. Columns that do not match an item in the CSPro dictionary are ignored.

Typical usage:

SQLServer2CSPro --dictionary Popstan.dcf -connection "Data Source=(local);Initial Catalog=popstan_db;Integrated Security=true"