SSU - Import/Export
The Import/Export routines available in SSU enable the exchange of individual data tables or whole companies between multiple servers. This is done using flat files which are either generated (export) or read (import) using bcp (MSSQL) or IMP/EXP (ORACLE). The purpose of this screen help is to explain the different functions available through SSU.
BCP
The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.
Export
By default the export process will extract details for the current company and place all data files in a subdirectory of the root folder named after the company code. If individual tables are selected the data files will reside in the root folder itself. A log is generated named BCPexport.log and this will be shown at the end of the extract (and is also available after export in root folder). The following features relate to the export process:
Export Current Company Details Only
This option is selected by default as it controls the most widely used function; exporting a whole company. As well as listing (and pre-selecting) all company specific tables (identified as such by being pre-fixed with the company code e.g. xx_phys01) this function will also extract all data held in our general company data tables (identified as gf_xxxxx). This will allow for a simple extraction of all data related to the selected company. If deselected, any table across all companies can be chosen for export but no general company data will be extracted.
Exclude Company Tables
If this option is selected the only data to be exported will be that held in the gf tables. This may be required when the company specific tables are to be exported using a third-party tool such as MS DTS. This is useful as the DTS will only extract whole tables and in the case of company export only certain records need to be extracted from these gf tables.
Valuation Tables
These are not always necessary as they are generally used for historical enquiries. By default all tables are included but by using this option the list of tables to be restricted.
Wildcard
Used to enter a search pattern against the table name, e.g. phys when refreshed will show tables such as xx_phys01, xx_phystext and so on.
Use Format Files *
If selected format files will be created for each of the selected tables. These will be named as the data files but with an extension of .fmt and contain information about the table definition. They will reside in the same folder as the main data files. These files are useful when moving data between servers that may have inconsistent table definitions. See using format files for more details.
Create Output Files *
These files are used to hold information returned by the bcp request. In the case of a successful export this will contain the number of rows exported, but if this failed, information as to the cause of failure will therefore be available. These are useful as this sort of information usually just flashes up of the screen for a few moments. These files will reside in a subfolder of the root named output.
Batch Mode *
When operating in normal mode the bcp command are executed individually which can lead to the users screen becoming unusable during the export process as these commands are flashed across the screen. If this option is selected however, a command file is generated (export.bat in root folder) which will contain all bcp commands. This is then executed and the result is that a single window is created which can be minimized thus allowing the user to carry on working on the terminal while the extraction takes place. The main disadvantage to this mode is that the status window will not be refreshed until the command-file has completed.
Import
This is the default option on entry to the screen. Select the path where the data files reside – if whole company from previous export then files should reside in subfolder named as company code – and click Look Up. This will refresh the file list with any data files found (NB these will be those with extension of .txt) and these can be selected individually or in full.
NB the functions above marked with * are also available for the import facility. The following features are explicit to the import process:
Drop & Recreate Tables
If selected the original table(s) associated by name with the selected data file(s) will be dropped i.e. deleted. A new empty version of the table will then be created and so after the import process, only data contained in the data file will be available in the table. This ensures a fresh copy that will basically duplicate the data held on the source server.
If ticked, and within the data files selected there are general company files (gf_xxxx), then the user will be prompted as to how this data is to be handled.
By default the gf data will be imported to replace the existing data on the host server. In this way all other companies and their data will be unaffected. If however, the intention is to import as another company code click Alternate Company on the dialog and select a different code.
It may also be necessary to drop the gf tables – such as in the case of importing general non-company data such as country codes, currencies and so on – in which case select Delete data for other companies and the gf tables will contain ONLY data from the data files. NB this option should only be chosen when the associated tables do not contain company information, otherwise data may be lost irrecoverably – only choose this option if certain the tables can be dropped.
Show Row Count
This extra step can be used to check the integrity of the data as, after each data file is imported, the associated table is read and its row count is displayed. This is only performed when the data file holds data (i.e. its file size is greater than zero bytes) and so if zero rows are counted this can alert the user to import problems. If the data file is empty the log will display no rows to import.
Using Format Files
As mentioned above, these files are useful for moving data between servers that may have inconsistent table definitions. The format file itself holds information about the source data table as will look something like this:
The principle use for these files requires no user-input. In general where there are discrepancies it will be due to data from an out of date system being imported into a newer version. Extra columns may exist in the host server’s tables but assuming the columns are in the same sequence, the bcp import procedure will be able to import the data without manipulation.
On occasions there may be problems with the import process and when these occur the output log will give information about them. The main causes will be extra columns in data file or columns held in a different sequence. Both of these issues can be resolved by changing the format file:
· if there are extra columns in the data file than in host server table these columns will need to be flagged by changing the value of their server column order. For example for the above illustration, if the column GroupName did not exist in the host server table this can be indicated by setting this number to 0 (zero). As well as this, all columns following the flagged column need to be re-sequenced, so again following the above example, the column ModifiedDate would then have its server column order set to 3. When the import is then executed the columns marked with a 0 will be ignored.
· if the sequence differs again, the server column order can be changed to match the sequence of the host server table.
Tip: TABROS has a feature for showing the column number and it is this that the data file needs to match.
ORACLE
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to another. The imp/exp utilities use an Oracle proprietary binary file format and can thus only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.
Export
See bcp export above for general usage. The main differences are there is no option to create format files (not required as the table definitions are held within the data files) and no option to create output files (a bcp function unique to bcp).
Import
See bcp import above for general usage. The only difference is there is no option to show the row count (this may be included in the future but for now as it is not possible to determine if data actually resides in the data file it would mean ALL tables would be read which would slow the import process down considerably).