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

Dataset Export 

• 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)

• NOTE:  The resulting BulkIO files can be quite large.  If the exported dataset needs to be moved across a WAN or transferred to ftp.culturatech.com, it is highly recommended to first zip up the numbered dataset directory that contains the BulkIO files (i.e.  AGRIS\Datasets\001) and then move/transfer the resulting zip (i.e. 001.zip).  

Dataset Import

• 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.

Notes regarding SQL Bulk Copy

• 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.