Column Mappings and Transformations
This page allows you to define the column mappings and transformation scripts that will occur as the data is published.
Column Mappings tab
Use this tab to specify how to treat the destination table and to specify column attributes on any newly created table.
Options
Drop the existing table and re-create it
Drop the destination table and re-create it before moving data into it. All existing data in the destination table and any indexes are destroyed.
Keep the existing table unchanged
Keep the destination table and do not change it.
Delete all data in the existing table
Drop the data at the destination table.
Mappings
Edit the cells in the table. The table does not list the order in which the destination columns will appear, but rather lists each destination column alongside each source column to which it maps.
For each column in the Mappings table, you can set the following properties:
- Source
- Choose the column name in the source table to copy to the destination. Click <ignore> on the source column name to set the destination column to NULL for a new table. If the table already exists, the data will be NULL if allowed, or set to its default value if one was defined. If the destination is defined as NOT NULL, clicking <ignore> in the source column results in an error when the package is executed if no default value is specified for the destination column.
If you copy a source column defined as an identity column, the data will not be copied unless the Enable identity insert check box is selected.
If you copy a source column defined with user-defined data types to a new table, the data type of the destination column is the system-defined data type that corresponds to the user-defined data type.
You may change the following properties only if a new destination table is being created.
- Destination
- Choose the column name in the destination table to receive the source data. Click <ignore> to prevent the source column from being created in the destination table when creating a new table. If you want to remove a column from an existing table, click <ignore> in the destination column, but also select the Drop and re-create destination table check box in this dialog box. This will re-create the table with the columns specified. Existing data and indexes in the table will be lost.
- Type
- Select a data type for the destination column. The default setting matches the data type of the destination column to the source.
- Nullable
- Specify if destination can allow null values.
- Size
- Specify the length of the Destination column, in units corresponding to the data type. The value is only applicable for the char, varchar, nchar, nvarchar, binary, and varbinary data types. Specifying a size smaller than the length of the source can result in data truncation.
- Precision
- Enter the maximum number of decimal digits that can be stored to the left and to the right of the decimal point. This option applies only to decimal and numeric data types.
- Scale
- Enter the maximum number of decimal digits that are stored to the right of the decimal point. The number must be less than or equal to the number in the Precision column. This option applies only to decimal and numeric data types.
Transformations tab
Use this tab to specify whether unique transformation code must be written. The column mappings set on the Column Mappings tab are reflected in code shown in the text area.
Options
Copy the source column directly
Copy the source column to the destination tables without changing the data or the source and destination mappings.
Transform data using the following script
Edit the script in the text area to customize the columns before copying them from the source to the destination. For complex transformations, consider using DTS Designer, which offers support for Microsoft ActiveX® scripting.
Advanced
Display the Advanced Transformation Properties dialog box, where you can set transformation flags. These provide varying levels of protection from conversion and data loss.