User Guide : Map Connectors : Source and Target Map Connectors : SQL Script
 
Share this page                  
SQL Script
This connector generates a SQL script text file to be used to manipulate the data within any SQL database. The SQL Script connector allows you to perform multiple operations (such as table drops, table inserts) upon your target database. However, the integration platform does not actually run the transformation in this case. The transformation can be completed on the SQL system, running the script file generated from the SQL Script connector. You can also create a process to call the SQL script file from a SQL step.
SQL Script is a format that the integration platform can write to as a target type. It is not available as a source type.
Multimode connectors allow you to perform multiple operations (such as table drops, table inserts) directly on your target database.
This connector specifies field width in characters, which means the width of a field is literally that number of characters.
Note:  Most of the "Multimode" connectors run the transformation in your target database. However, in the case of SQL Script, the integration platform does not actually run the transformation. With SQL Script, the transformation can be completed on the SQL system, running the script file generated from the SQL Script connector. You can also create a process to call the SQL script file from a SQL step.
Connector-Specific Notes
• Because the integration platform has no method to determine your target type with the SQL Script connector, there is no target table list in the Event Action Parameters window. You must enter the table name.
• When using a SQL Script file, the integration platform attempts to pass a precision indicator if your data type is Number. A precision indicator is not accepted and an error is returned. To prevent this, open your completed SQL Script in an external text editor and remove the precision indicator next to each Number data type. Save your changes and run again.
• In multimode targets, modifications to column names, data types, and sizes are not permitted.
Property Options
Because the SQL Script connector is meant to connect to all SQL databases, you need to know your target formats to set the properties correctly. The integration platform includes this format information in a note beneath each property.
You can set the following source (S) and target (T) properties.
Property
S/T
Description
Binary Format
T
Specifies how to represent Binary data within your target SQL script. The default is SQL Hexadecimal Literal. The other available options are C/C++ Literal and Hexadecimal Number.
ByteOrder
T
Allows you to specify the byte order of Unicode (wide) characters. The default is Auto and is determined by the architecture of your computer. The other list box options are Little Endian and Big Endian. Little Endian byte order is generally used by Intel machines and DEC Alphas and places the least significant portion of a byte value in the left portion of the memory used to store the value. Big Endian byte order is used by IBM 370 computers, Motorola microprocessors and most RISC-based systems and stores the values in the same order as the binary representation.
Date Format
T
Specifies how to format dates within your target SQL script. The default format is month/day/four-digit year (or mm/dd/yyyy). If you wish your date formats to appear in another format, highlight the default value and change it to the format you wish (for example, yyyy/mm/dd).
Date Time Format
T
Specifies how to format dates that include times within your target SQL script. The default format is mm/dd/yyyy H24:MM:SS. If you wish to use another format, highlight the default value and change it to the format you wish (for example, yyyy/mm/dd H12:MM:SS).
Encoding
T
This allows you to select the type of encoding used with your source and target files.
• Shift-JIS encoding is meaningful only in Japanese operating systems.
• UCS-2 is no longer considered a valid encoding name, but you may use UCS2. In the XML file, change UCS-2 to UCS2.
• The Encoding property is not the encoding of the database that you connect to, but rather the encoding in which the connector expects to receive SQL query statements to be sent to the database.
Line Break
T
Specifies which character(s) to write to the target SQL script as line breaks. The default is CR-LF. The available options are CR-LF (default), LF, CR, LF-CR, Empty Line, and None.
Name Quote
T
Specifies how to delimit the table and column names within the target SQL script. The default is double quotes. The available options are double quotes, single quote, and None.
Order Mark
T
The Order Mark is a special character value that is sometimes written to a Unicode text file to indicate the byte order used for encoding each of the Unicode characters. You have the option of writing byte order mark at the beginning of Unicode (wide) output or not. The default is false. If you wish to have the byte order mark placed at the beginning of the output, change this option to true.
UpdateNullFields
T
Null values are sent to the database when inserting or updating records. The default is true. If you select False, null values are not sent to the database when you insert or update records. When set to false, this property forces the connector to operate in unbound mode, which may cause slower performance.
Best Practice — If fields in the target record are not mapped, then the null values are passed to the target. If you do not want to write to these fields, then it is recommended to set the value for UpdateNullFields to False.
Statement Separator
T
Specifies what characters to output as a statement separator to your target SQL script. The default value is a semicolon ( ; ). The available options are semicolon and None.
String Quote
T
Specifies how to delimit strings within your target SQL script. The default is a single quote ('). The available options are double quote ("), single quote (") and None.
Time Format
T
Specifies how to format times within the target SQL script. The default format is H24:MM:SS. If you wish your time format to appear in another form, highlight the default value and change it to the format you wish.
Time Stamp Format
T
Specifies how to format times within the target SQL script. The default format is H24:MM:SS. If you wish the time to appear in another form, highlight the default value and change it to the format you wish. This format may not have a Null value.
Length
These are field lengths in your data. If you need to change field lengths, reset them in the schema.
Caution!  A field length shorter than the default may truncate data.
Data Types
The integration platform does not support ID field types. The default text data type is Char.
The following data types are available for SQL Script connections:
• Binary
• Bit
• Bit Varying
• Char
• Char Varying
• Character
• Character Varying
• Date
• DateTime
• Decimal
• Double Precision
• Float
• Integer
• Long Binary
• Long Varbin
• Long VarChar
• National Character
• National Character Varying
• NChar
• NChar Varying
• Number
• Number ( )
• Numeric
• NVarChar
• Real
• RowID
• SmallInt
• Text
• Text ( )
• Time
• TimeStamp
• VarBinary
• VarChar
• VarChar2