MSSQL Dataset Export/Import

Background

With Pervasive the database resides in the dataset folder, allowing the dataset to be copied directly with the folder
With SQL Server the data does not reside in the dataset folder
Dataset Export is the method to get a single dataset (or a portion of a dataset) out of SQL Server
Dataset Import is the method to get a dataset into SQL Server

Maintenance > Dataset Maintenance
Press the Export button. Only the current dataset can be exported. The Export button is available only if the current dataset is MSSQL.
Select the tables to export
Choose to clear any columns upon export (such as Social Security Number). The syntax is Table:Column. Separate each table:column combination by commas. (i.e. N01_NAM_ADR_INF:N01_PHN_NUM_S,N01_NAM_ADR_INF:N01_SSN_FED_ID_S)
Press Save to save this configuration as the default export setup for this dataset
Press Export to export the selected tables
The export process puts several files in the dataset folder
- BulkIOService.Export.zip - the dataset data
- BulkIOService.Schema.zip - the database schema
- BulkIOService.Export.Version.txt - the AGRIS version that is exported
The export process is complete. Copy the dataset folder to the intended destination (archive, send to Cultura, move to another db server, etc)
- The entire dataset folder is needed after export. There is non-database information in the dataset folder (package initialization files, CFT setup, spooled reports and forms, and more)

Maintenance > Dataset Maintenance
Press the Import button. The Import button is available only if the current dataset is MSSQL.
Select the folder to import
- If searching for the import path, note that the AGRIS search window is a file search, not a folder search. Choose any file in the import folder.
- The folder selected must have the 3 export files in it (BulkIOService.Export.zip, BulkIOService.Schema.zip, BulkIOService.Export.Version.txt)
- Do not unzip the export zip files
Optionally set the dataset number. If blank the next available dataset number will be assigned.
A dataset from a prior AGRIS version may be imported. A data conversion will run as part of the import process.

Dataset Export and Import use SQL Server Bulk Copy functionality on the AGRIS client
Bulk Copy 11.0 is installed with AGRIS. This is so AGRIS clients are able to export and import.
Bulk Copy is also installed with SQL Server. This is likely to be a newer version of Bulk Copy (12.0 or 13.0).
Bulk Copy is backward compatible on version. So a dataset export done with Bulk Copy 11.0 can be imported using Bulk Copy 12.0. The reverse is not true - a dataset export done with Bulk Copy 12.0 cannot be imported by Bulk Copy 11.0.
If AGRIS is installed on the same machine as SQL Server (perhaps in a oneWeigh environment), there are likely to be competing versions of Bulk Copy on that machine.
if dataset import is not successful, it may be because the export was done with a newer version of Bulk Copy.
- One cause may be multiple versions of Bulk Copy - search the import machine for bcp.exe and rename the older version, to force the newer version to be used for Import.
- To confirm the export version, open up BulkIOService.Export.zip (do not unzip it into the dataset folder, just open the zip file to get a look at what is inside it). Open one of the .fmt files in a text editor. The first line contains the Bulk Copy version. Compare this to the version of bcp.exe on the import machine.