Column Mappings and Transformations
Use this dialog box to specify the mappings between the source and destination columns and to specify the transformations the data goes through, if any, as it is moved to the destination. You also can change the data type of the data if a valid data conversion is applicable.
Column Mappings Tab
Use this tab to specify how to treat the destination table and to specify column attributes on any newly created table.
Create destination table
Create the destination table before copying the source data. This is the default option if the destination table you specified does not exist. If this option is selected and the destination table already exists, an error occurs, unless you select the Drop and re-create destination table check box.
Customize the Transact-SQL CREATE TABLE statements used to create the destination table.
Delete rows in destination table
Delete all rows in the destination table before copying the source data. This option is only available if the destination table already exists. Existing indexes and constraints on the destination table are not affected by this option.
Append rows to destination table
Insert source data into the destination table. This option is available only if the table already exists. Existing data, indexes, and constraints on the destination table are not affected. However, rows are not necessarily appended to the end of the destination table. You can determine where rows will be inserted only by having a clustered index on the destination table.
Drop and re-create destination table
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.
Enable identity insert
Allow explicit values to be inserted into the identity column of a table (Microsoft® SQL Server™ only). This option is only available if an identity column is detected. An identity column is a column that has been defined as having a data type of bigint, decimal, integer, numeric, smallint, or tinyint, where the Identity property for the column is set to yes.
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:
- 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.
- 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.
timestamp columns cannot be copied by the Data Transformation Services (DTS) Import/Export Wizard. If the destination column is a timestamp column, the value in that field will be a new timestamp indicating when the row was inserted, not a copy of the timestamp data from the source table.
Select a data type for the destination column. The default setting matches the data type of the destination column to the source.
Note Invalid data conversions can be specified without causing an error, as the default transformation setting allows all possible conversions. For example, converting an int data type to a tinyint data type will result in data truncation, but the conversion will proceed.
- Specify if destination can allow null values.
- 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.
- 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.
- 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.
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.
Copy the source column directly to the destination columns
Copy the source column to the destination tables without changing the data or the source and destination mappings.
Transform information as it is copied to the destination
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.
Select a scripting language. The default language is Microsoft Visual Basic® Scripting Edition (VBScript). Available languages are VBScript or JScript.
Display the Select File dialog box, where you can select a file that contains previously written transformation code, if the code already has been written and saved to a .txt, .vbs or .bas file. When a file is selected, the text from the file is copied into the Query statement text area.