Write Spreadsheet to Database - Configuration

With a workspace open, access the Write to Spreadsheet node from the Deployment group on the DataMining tab on the ribbon or from the Alert group of the Publish tab.

The Write to Spreadsheet node allows you to:

Write spreadsheet data to database tables.

Create a new table if none exists

Append data to an existing table

Remove the data before writing by truncating the table or by dropping and recreating the table before writing.

Amend existing database tables with the insert/update/delete operations using key values

Handle duplicate records – by key value(s)

Insert – keep the rows in the table and add the duplicate rows

Update – update the rows in the table to match the cases from the spreadsheet

Keep – keep the rows in the table but do not add the duplicate rows

Handle unmatched records in table – by key value(s)

Keep – keep the rows in the table

Delete – delete the rows from the table

Note: When updating (this is the Update option), all columns in a row will be updated to match the variables from the corresponding value from the spreadsheet. Records from the source spreadsheet that do not have data in the corresponding rows in the target spreadsheet will always be added.
Element Name Description
Database connection This tab configures the setting to specify what happens to the data being written to the target table in the databaseDatabase connection (required) 

In this text box, specify the target database connection (either as an Enterprise Database Connection object name or as an OLEDB connection string).

Name of the target table (required)  In this text box, specify the name of the table in the database to which the data will be written. If the table does not already exist, it will be created.
Existing table handling In this drop-down list, specify if the data in an existing target table is to be overwritten instead of appended (default setting) from the following options:
Append The incoming data will be appended to any pre-existing data without overwriting it. The target table schema must match that of the spreadsheet.
Drop and recreate The target table will be dropped and recreated before writing the incoming data (previously this was the overwrite option).
Truncate and insert The target table will have all rows deleted before writing the incoming data.
Export labels as text Select this checkbox to export spreadsheet text labels as text. If it is not selected, the numeric mappings of the text labels will be used instead.
Default text label field size Use this microscroll to select the default maximum length for the text label columns in the database. If a specific text label column's content is shorter than the default length, this field's length in the target table automatically adjust to the value specified here. By setting this value, you can accommodate potentially larger future values not present in the current sample.
Database type From this dropdown list, select the type of database being written to, if you know it. Setting this property can help when errors are encountered using auto detection. 
Force DATETIME data type Selecting this checkbox forces the use of the DATETIME data type on SQL Server versions that support DATETIME2. This setting can help address some issues when using the OLEDB provider for SQL Server instead of the native client.