User Guide > Map Connectors > Source and Target Map Connectors > SQL Server BCP
Was this helpful?
SQL Server BCP
A SQL Server BCP (Bulk Copy Program) file is a special bulk loader format. Using BCP boosts performance when writing large amounts of data to a SQL Server table. SQL Server BCP provides the best performance in simple transformations that do not contain such features as lookups, many expressions, and event actions. With this connector, you can read and write bulk copy program files to SQL Server tables.
You may notice that SQL Server BCP appears on both the source and the target connection lists. It appears on the source connection primarily for reading and verifying loader files created by the integration platform, when the user does not have the application in which to check the files.
Note:  Target Properties must be adjusted if SQL Server BCP is the target connector. See property options below.
Connector-Specific Notes
The integration platform does not support milliseconds in DateValMask conversions. Although a mask is provided for them, the integration platform returns zeros in those places.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target. The fields that are available depend on the connector you select.
For a list of all parts for source connectors, see Specifying Source Connector, Parts, and Properties.
For a list of all parts for target connectors, see Specifying Target Connector, Parts, and Properties.
Property Options
Note:  The properties FormatSeparator and RecordSeparator must be different values for the BCP file to be recognized by a loader utility. The default values for these properties are identical and therefore at least one property must be changed when SQL Server BCP is the connector. Check the documentation on your utility to determine preferred separators and alter the properties accordingly.
You can specify the following source (S) and target (T) properties:
Property
S/T
Description
FieldSeparator
T
By default, the integration platform assumes that SQL Server BCP files use tabs to separate fields. To specify a different field separator, click the FieldSeparator value and then click the arrow to select either comma (,), space, carriage return-line feed (CR-LF), line feed (LF), carriage return (CR), line feed-carriage return (LF-CR), or ctrl-R. To use a field separator that is not on the list, type it here.
If the field separator is not one of the choices from the list and is a printable character, highlight the default value and then type the correct character. For example, if the separator is a pipe (|), type a pipe from the keyboard.
If the field separator is not a printable character, replace CR-LF with a backslash, an X, and the hexadecimal value for the separator. For example, if the separator is a check mark, then enter \XFB. For a list of the 256 standard and extended ASCII characters, see Source and Target Map Connectors.
FormatSeparator
T
Sets the line separator in the format file. The default is carriage return-line feed (CR-LF). To use a different line separator, click the FormatSeparator value and then click the arrow to select either line feed (LF), carriage return (CR), or line feed-carriage return (LF-CR). To use a separator that is not on the list, type it here.
If the format separator is not one of the choices from the list and is a printable character, highlight the CR-LF and then type the correct character. For example, if the separator is a pipe (|), type a pipe from the keyboard.
If the format separator is not a printable character, replace CR-LF with a backslash, an X, and the hexadecimal value for the separator. For example, if the separator is a check mark, then enter \XFB. For a list of the 256 standard and extended ASCII characters, see Source and Target Map Connectors.
Caution!  The FormatSeparator cannot be the same as the RecordSeparator.
RecordSeparator
T
By default, the integration platform assumes that SQL Server BCP files use carriage return-line feeds (CR-LF) between records. To use a different character as a record separator, click the RecordSeparator value cell and then click the arrow to select either line feed (LF), carriage return (CR) , or line feed-carriage return (LF-CR), form field (FF), empty line, or none. To use a separator that is not on the list, type it here.
If the record separator is not on the list and is a printable character, highlight the CR-LF and then type the correct character. For example, if the separator is a pipe (|), type a pipe from the keyboard.
If the record separator is not a printable character, replace CR-LF with a backslash, an X, and the hexadecimal value for the separator. For example, if the separator is a check mark, then enter \XFB. For a list of the 256 standard and extended ASCII characters, see Source and Target Map Connectors.
Caution!  The FormatSeparator cannot be the same as the RecordSeparator.
StripLeadingBlanks
S/T
By default, the integration platform does not strip leading blanks from SQL Server BCP data. To remove them, set StripLeadingBlanks to true.
StripTrailingBlanks
S/T
By default, the integration platform does not strip trailing blanks from SQL Server BCP data. To remove them, set StripTrailingBlanks to true.
StyleSampleSize
S
Sets the number of records analyzed to a set width for each source field. The default is 5,000. You can change the value to any number between 1 and the total number of records in your source file. As the number gets larger, more time is required to analyze the file and it may be necessary to analyze every record to ensure that no data is truncated.
To change the value, click StyleSampleSize, highlight the default value, and type a new value.
Version
T
Allows you to select the same version as the SQL Server BCP source file. The default setting is 6.0. To select 4.2 or another version, click the Version value and type the version number you want.
Supported Data Types
The only data type available is Text.
Last modified date: 12/03/2024