Help Manual - Premium Derived Column

Documentation on the SSIS Derived Column component

Using the Premium Derived Column Component

The Premium Derived Column Component offers a familiar UI to that of the built-in Derived Column Component. Like the built-in component, the user adds derived columns and utilizes expressions to transform data. The following are some of the additional features that the Premium Derived Column has:

  • Over 294 functions
  • Advanced Expression Editor
  • Expression validation and testing with test values
  • Advanced data type detection

Main Editor

The Main Editor is where the user adds and removes derived columns. To add a derived column, just start editing the last blank row. To remove, either press 'Delete' or right-click and select 'Delete'.

Premium Derived Column Editor

List of Columns & Variables

Drag and drop a column or variable from the tree view to the Expression column to add a column or variable to your expression.

List of Functions

Drag and drop a function, cast, or operator from the tree view to the Expression column to add a function to your expression. Information about the selected function will appear in the panel below. See a full list of the available expressions and their description below.

Search For Function

Filter the list of functions.

Reevaluate Expressions

This will evaluate and validate every expression in the data grid view.

Columns

The Columns page is where derived columns are added and given expressions. Add a derived column by editing the last row in the data grid view and a new row will automatically be added. Remove a derived column by selecting a row and pressing the 'Delete' key or right-click and select 'Delete'.

Premium Derived Column - Columns

The data grid view contains the following columns:

Derived Column Name

The name of the output column that will contain the result of the specified Expression.

Derived Column

The input column to derive from meaning the output value of the selected column will be replaced with the result of the specified Expression. To instead add a new output column select '<add as new column>'.

Expression

The expression that will be evaluated for each row and sent to the output column specified in Derived Column Name. When editing the Expression, click the '...' button to launch the Expression Editor. Construct an expression by using:

  • Columns: Specify the column name in square brackets or the column LineageID prefixed with a '#'. During runtime, this is replaced with the value of the specified column of the current row.
  • Variables: Specify the fully qualified name of the variable in square brackets and prefixed with a '@'. During runtime, this is replaced with the current value of the specified variable.
  • Functions: Specify the function name followed by parentheses. Inside the parentheses should be the respective functions' required parameters. During runtime, this is replaced with the result of the function.
  • Casts: Specify the data type in parentheses. The cast may also require parameters, specify those inside the brackets after the data type separated by commas (,). During runtime, this will attempt to cast the value to the right of the specified data type.
  • Operators: Specify different unary, binary, or ternary operations to perform during runtime.
  • String Literals: Specify strings in the expression by placing text inside of double quotation marks (").
  • Numbers: Specify numbers (integers and decimals) in the expression as is.
Data Type

The data type of the derived column. This is not editable because we automatically detect the datatype based on the expression. You can change the data type by using a type cast from the list of functions tree view.

Length, Precision, Scale, Code Page

Like the Data Type column, these properties are automatically detected but these columns are editable based on the detected data type.

Error Handling

The Error Handling page allows you to specify how errors should be handled when they happen.

Premium Derived Column Editor - Error Handling

There are three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represents rows that were successfully sent, and the red 'Error Output' connection represents erroneous rows. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by the server or the component itself.

Premium Derived Column Editor - Error Output

Expression Editor

The Expression Editor offers a multi-line colored textbox to construct more complex expressions. Expressions can also be tested using test values for column data.

Premium Derived Column - Expression Editor

List of Columns & Variables

Drag and Drop a column or variable from the tree view to the Expression column to add a column or variable to your expression. Below the list of columns, there is a panel with details about the selected variable or column. When a column is selected you can change the value of the Test Value. This is the value that will be used when the expression is tested.

List of Functions

Drag and Drop a function, cast, or operator from the tree view to the Expression column to add a function to your expression. Information about the selected function will appear in the panel below.

Search for Function

Filter the list of functions.

Expression Textbox

The expression that will be evaluated for each row.

Validate

Validates the expression using test values. Any errors will appear in the error box below.

Test

Tests the expression using test values and displays the result in a message box.

Premium Derived Column Functions

Below is a list of function categories included within the Premium Derived Column. You may click on each category to see the list of available functions with a brief description of what it achieves.

Comments (2)

  • /* */ (Multiline Comment)
  • // (Singleline Comment)

Compression Functions (6)

  • DeflateCompress: Returns the «bytes» as compressed DEFLATE bytes.
  • DeflateDecompress: Returns the DEFLATE «bytes» as uncompressed data.
  • GzipCompress: Returns the «bytes» as compressed GZIP bytes.
  • GzipDecompress: Returns the GZIP «bytes» as uncompressed data.
  • Unzip: Returns the «bytes» of the file whose name is «filename» from a zipfile («bytes») whose password is «password».
  • Zip: Returns the «bytes» of a zip file that contains a file with the name of «filename» and contents of «bytes». The zip file is also password protected using the specified «password».

Conversion Functions (17)

  • BinToDec: Converts «binary_expression» to a decimal value.
  • BinToHex: Converts «binary_expression» to a hexadecimal value.
  • BinToOct: Converts «binary_expression» to a octal value.
  • BytesToHex: Converts each byte in «bytes» into a hexadecimal and returns the concatenated value.
  • BytesToString: Converts «bytes» to a string using the provided «code_page».
  • Char: Converts «decimal expression» to a char.
  • DecToBin: Converts «bytes» to a string using the provided «code_page».
  • DecToHex: Converts «bytes» to a string using the provided «code_page».
  • DecToOct: Converts «decimal_expression» to a octal value.
  • HexToBin: Converts «hex_expression» to a binary value.
  • HexToBytes: Converts each hexadecimal in «hex_expression» into a byte and returns a byte array containing all of the bytes.
  • HexToDec: Converts «hex_expression» to a decimal.
  • HexToOct: Converts «hex_expression» to a octal value.
  • OctToBin: Converts «octal_expression» to a binary value.
  • OctToDec: Converts «octal_expression» to a octal decimal.
  • OctToHex: Converts «octal_expression» to a hexadecimal value.
  • StringToBytes: Converts «character_expression» to bytes using the provided «code_page».

Cryptography Functions (33)

  • AdvancedDecryptUsingAES: Returns «bytes» decrypted using the AES algorithm.
  • AdvancedDecryptUsingDES: Returns «bytes» decrypted using the DES algorithm.
  • AdvancedDecryptUsingRC2: Returns «bytes» decrypted using the RC2 algorithm.
  • AdvancedDecryptUsingRijndael: Returns «bytes» decrypted using the Rijndael algorithm.
  • AdvancedDecryptUsingTripleDES: Returns «bytes» decrypted using the TripleDES algorithm.
  • AdvancedEncryptUsingAES: Returns «bytes» encrypted using the AES algorithm.
  • AdvancedEncryptUsingDES: Returns «bytes» encrypted using the DES algorithm.
  • AdvancedEncryptUsingRC2: Returns «bytes» encrypted using the RC2 algorithm.
  • AdvancedEncryptUsingRijndael: Returns «bytes» encrypted using the Rijndael algorithm.
  • AdvancedEncryptUsingDES: Returns «bytes» encrypted using the DES algorithm.
  • ComputeMD5Hash: Returns the MD5 hash of «bytes».
  • ComputeMD5HMAC: Returns the MD5 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeRIPEMD160Hash: Returns the RIPEMD160 hash of «bytes».
  • ComputeRIPEMD160HMAC: Returns the RIPEMD160 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA1Hash: Returns the SHA1 hash of «bytes».
  • ComputeSHA1HMAC: Returns the SHA1 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA256Hash: Returns the SHA256 hash of «bytes».
  • ComputeSHA256HMAC: Returns the SHA256 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA384Hash: Returns the SHA384 hash of «bytes».
  • ComputeSHA384HMAC: Returns the SHA384 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA512Hash: Returns the SHA512 hash of «bytes».
  • ComputeSHA512HMAC: Returns the SHA512 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • DecryptUsingAES: Returns «bytes» decrypted using the AES algorithm.
  • DecryptUsingDES: Returns «bytes» decrypted using the DES algorithm.
  • DecryptUsingRC2: Returns «bytes» decrypted using the RC2 algorithm.
  • DecryptUsingRijndael: Returns «bytes» decrypted using the Rijndael algorithm.
  • DecryptUsingTripleDES: Returns «bytes» encrypted using the TripleDES algorithm.
  • EncryptUsingAES: Returns «bytes» encrypted using the AES algorithm.
  • EncryptUsingDES: Returns «bytes» encrypted using the DES algorithm.
  • EncryptUsingRC2: Returns «bytes» encrypted using the RC2 algorithm.
  • EncryptUsingRijndael: Returns «bytes» encrypted using the Rijndael algorithm.
  • EncryptUsingTripleDES: Returns «bytes» encrypted using the TripleDES algorithm.
  • PBKDF2: Derives a byte array key (of length «key_size») from the specified password.

Date/Time Functions (11)

  • DateAdd: Returns «date» with «number» added to the «datepart».
  • DateDiff: Returns the number of date and time boundaries crossed between two specified dates.
  • DatePart: Returns part of «date» as specified by «datepart».
  • DateTimeToUnixTimeStamp: Returns the Unix time stamp equivalent of «date_time» as seconds or milliseconds specified by the «as_seconds_or_milliseconds» parameter. Unix time stamp is the number of seconds or milliseconds since Unix epoch (1970-01-01 00:00:00.000 UTC).
  • Day: Gets the day value from «date».
  • GetDate: Returns the current date of the system.
  • GetUtcDate: Returns the current date of the system in UTC.
  • Month: Gets the month value from «date».
  • TruncateTimeFromDateTime: Returns «date» with the time part removed.
  • UnixTimeStampToDateTime: Returns the DateTime equivalent of «time_stamp». Specify whether the Unix time stamp is in seconds or milliseconds with the «is_seconds_or_milliseconds» parameter. Unix time stamp is the number of seconds or milliseconds since Unix epoch (1970-01-01 00:00:00.000 UTC).
  • Year: Gets the year value from «date».

Encoding Functions (11)

  • DecodeBase64: Returns the Base64 encoded «character_expression», decoded into bytes.
  • DecodeBase64: Returns the Base64 encoded «character_expression», decoded into bytes then converted into string using the provided «code_page».
  • EncodeBase64: Returns «bytes» as a Base64 encoded string.
  • EncodeBase64: Returns «character_expression» as a Base64 encoded string using «code_page» to first get the bytes of «character_expression».
  • FormContentURLDecode: Returns the decoded value of the URL form encoded «character_expression».
  • FormContentURLEncode: Returns «character_expression» as a URL form encoded value.
  • HtmlDecode: Returns the HTML encoded «character_expression» as a decoded string.
  • HtmlEncode: Returns «character_expression» as a HTML encoded string.
  • HtmlEncode: Returns «character_expression» as a HTML encoded string based on the «use_entity_names» parameter.
  • UrlDecode: Returns the URL encoded «character_expression» as a decoded string.
  • UrlEncode: Returns «character_expression» as a URL encoded string.

File I/O Functions (13)

  • FileExists: Returns a boolean if a file exists at the specified «file_path».
  • FileLockedBy: Returns the process name that is currently locking the file specified at «file_path».
  • FolderExists: Returns a boolean if a folder exists at the specified «folder_path».
  • IsFileLocked: Returns a boolean if the file at the specified «file_path» is currently locked.
  • ReadBinaryContent: Returns all of the bytes in the binary file at the specified «file_path».
  • ReadLineCount: Returns the number of lines in the file at «file_path» with code page of «code_page».
  • ReadTextContent: Returns all of the text in the file at the specified «file_path».
  • ReadTextContent: Returns all of the text in the file at the specified «file_path» using the specified «code_page».
  • ReadTextContent: Returns the lines from «starting_line_number» to the end in the file at the specified «file_path» using the specified «code_page».
  • ReadTextContent: Returns the «number_of_lines» starting at «starting_line_number» in the file at the specified «file_path» using the specified «code_page». Speify 0 «number_of_lines» to read to end. Specify a negative «number_of_lines» to read to end excluding the last number of lines.
  • WriteBinaryContent: Writes «bytes» to the binary file at the specified «file_path» and returns true if successful.
  • WriteTextContent: Writes «text» to the file at the specified «file_path» using the specified «code_page» and returns true if successful.
  • WriteTextContent: Writes «text» to the file at the specified «file_path» and returns true if successful.

Mathematical Functions (25)

  • Abs: Returns the absolute value of «numeric_expression».
  • Acos: Returns the angle whose cosine is «numeric_expression».
  • Asin: Returns the angle whose sine is «numeric_expression».
  • Atan: Returns the angle whose tangent is «numeric_expression».
  • Avg: Returns the average value of all provided numeric expressions.
  • Ceiling: Returns the smallest integer that is greater than or equal to «numeric_expression».
  • Cos: Returns the cosine of «numeric_expression» in radians.
  • CosH: Returns the hyperbolic cosine of «numeric_expression» in radians.
  • Exp: Returns the exponential of «numeric_expression».
  • Floor: Returns the largest integer that is less than or equal to «numeric_expression».
  • Ln: Returns the largest integer that is less than or equal to «numeric_expression».
  • Log: Returns the base-10 logarithm of «numeric_expression».
  • Max: Returns the maximum value of all provided numeric expressions.
  • Min: Returns the minimum value of all provided numeric expressions.
  • Power: Returns the result of raising «numeric_expression» to «power».
  • Random: Returns a random number between the values of «min_value» and «max_value» (not including «max_value»).
  • Round: Returns the integer that is closest to «numeric_expression».
  • Sign: Returns the positive (+1), negative (-1), or zero (0) sign of «numeric_expression».
  • Sin: Returns the sine of «numeric_expression» in radians.
  • SinH: Returns the hyperbolic sine of «numeric_expression» in radians.
  • Sqrt: Returns the square root of «numeric_expression».
  • Square: Returns the square of «numeric_expression».
  • Sum: Returns the sum value of all provided numeric expressions.
  • Tan: Returns the tangent of «numeric_expression» in radians.
  • TanH: Returns the hyperbolic tangent of «numeric_expression» in radians.

Null Functions (31)

  • IsNULL: Returns a Boolean result based on whether «expression» is null.
  • NULL(DT_BOOL): Returns NULL.
  • NULL(DT_BYTES): Returns NULL.
  • NULL(DT_CY): Returns NULL.
  • NULL(DT_DATE): Returns NULL.
  • NULL(DT_DBDATE): Returns NULL.
  • NULL(DT_DBTIME): Returns NULL.
  • NULL(DT_DBTIME2): Returns NULL.
  • NULL(DT_DBTIMESTAMP): Returns NULL.
  • NULL(DT_DBTIMESTAMP2): Returns NULL.
  • NULL(DT_DBTIMESTAMPOFFSET): Returns NULL.
  • NULL(DT_DECIMAL): Returns NULL.
  • NULL(DT_FILETIME): Returns NULL.
  • NULL(DT_GUID): Returns NULL.
  • NULL(DT_I1): Returns NULL.
  • NULL(DT_I2): Returns NULL.
  • NULL(DT_I4): Returns NULL.
  • NULL(DT_I8): Returns NULL.
  • NULL(DT_IMAGE): Returns NULL.
  • NULL(DT_NTEXT): Returns NULL.
  • NULL(DT_NUMERIC): Returns NULL.
  • NULL(DT_R4): Returns NULL.
  • NULL(DT_R8): Returns NULL.
  • NULL(DT_STR): Returns NULL.
  • NULL(DT_TEXT): Returns NULL.
  • NULL(DT_UI1): Returns NULL.
  • NULL(DT_UI2): Returns NULL.
  • NULL(DT_UI4): Returns NULL.
  • NULL(DT_UI8): Returns NULL.
  • NULL(DT_WSTR): Returns NULL.
  • ReplaceNULL: Returns the value of «expression_2» if the value of «expression_1» evaluates to NULL.

Operators (22)

  • - (Negate)
  • - (Subtract)
  • ! (Logical NOT)
  • != (Unequal)
  • % (Modulo)
  • & (Bitwise AND)
  • && (Logical AND)
  • () (Parentheses)
  • * (Multiply)
  • / (Divide)
  • ? : (Conditional)
  • ^ (Bitwise Exclusive OR)
  • | (Bitwise Inclusive OR)
  • || (Logical OR)
  • ~ (Bitwise NOT)
  • + (Add)
  • + (Concatenate)
  • < (Less Than)
  • <= (Less Than or Equal To)
  • == (Equal)
  • > (Greater Than)
  • >= (Greater Than or Equal To)

Other Functions (15)

  • IfElse: Returns the «result» whose «condition» evaluates to true. If none of them evaluate to true then «default_result» is returned.
  • In:  Returns a Boolean is any of the <<Items>> are equal to  <<value>>.
  • IncrementalValue: Returns an incremental value starting at «starting_value» and incrementing by «incremental_value» for each row.
  • IncrementalValue(): Returns an incremental value starting at 1 and incrementing by 1 for each row.
  • NotIn: Returns a Boolean is none of the <<Items>> are equal to  <<value>>.
  • RowIndex: Returns the current row index.
  • RowIndex(): Returns the current row index.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message» and «caption». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message», «caption», and «buttons». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message», «caption», «buttons», and «icon». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message», «caption», «buttons», and «icon» with the «default_button» selected. This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • Sleep: Pauses execution for «milliseconds», then returns the value of «milliseconds».
  • SwitchCase: Returns the «result» whose «case» equals «value_to_compare». If no matches are found «default_result» is returned.
  • WriteValueToVariable: Sets the value of «variable» to the result of «value_expression».

Registry Functions (2)

  • RegistryGetValue: Gets the value of registry key at «key_name» with the name of «value_name».
  • RegistrySetValue: Sets the value of registry key at «key_name» with the name of «value_name» to the result of «value_expression» and the type to «value_type».

Error Handling (7)

  • FireError: Writes an error «message» to the SSIS log.
  • FireInformation: Writes an information «message» to the SSIS log.
  • FireWarning: Writes a warning «message» to the SSIS log.
  • RaiseError: Raises an error with the specified «message».
  • RaiseError: Raises an error with the specified «message» when the evaluated «condition» is true, otherwise returns false.
  • RaiseError: Raises an error with the specified «message» when the evaluated «condition» is true, otherwise returns «default_value».
  • Try: Attempts to return the evaluated «expression», if any errors occur then «default» is returned instead.

Regular Expression Functions (5)

  • RegexFindMatch: Returns the match at the specified position in «character_expression» using the «regular_expression». Returns NULL if the specified match position cannot be found.
  • RegexGetTokenAtPosition: Splits «character_expression» using the «regular_expression» pattern as the delimiter and returns the token at «position».
  • RegexGetTokenCount: Returns the number of tokens when «character_expression» is split by «regular_expression».
  • RegexIsMatch: Returns a Boolean based on if «character_expression» respects «regular_expression».
  • RegexReplace: Returns «character_expression» with all matching occurrences of «regular_expression» replaced with «replace_string».

String Functions (52)

  • Capitalize: Returns «character_expression» with the first character in every word converted to upper case and all other characters converted to lowercase.
  • CodePoint: Returns the Unicode code value of the leftmost character in «character_expression».
  • Contains: Returns a Boolean value representing if «character_expression» contains any occurrences of «search_expression».
  • DateTimeToString: Returns the «date_time» as a string with the format specified by «format_string».
  • EndsWith: Returns a Boolean value representing if «character_expression» ends with «search_expression».
  • EscapeRegex: Returns the «string_expression» with Regex characters escaped.
  • EscapeUri: Returns the «string_expression» with URI characters escaped.
  • EscapeUriDataString: Returns «character_expression» with any character that is not an unreserved character, including percent signs, escaped.
  • EscapeXml: Returns the «string_expression» with XML characters escaped.
  • FindString: Returns the location of the specified occurrence of a string in «character_expression».
  • FormatString: Inserts all of the values into «character_expression» by using curly brackets around an index to refer to a value to be inserted. For example: {0} will be replaced with the first value, {1} with the second, etc.
  • FormUrlEncodedToJson: Returns the form urlencoded «character_expression» as JSON.
  • GenerateJWT: Generates a JSON Web Token (JWT) using the «header_json», «payload_json», «secret».
  • GetItemAtAtPositionInStringArray: Parses «string_array» using «delimiter» and «text_qualifier» and returns the item at «position».
  • GetItemCountInStringArray: Parses «string_array» using «delimiter» and «text_qualifier» and returns number of items.
  • GetTokenAtPosition: Splits «character_expression» by «delimiter» and returns the token at «position».
  • GetTokenCount: Returns the number of tokens when «character_expression» is split by «delimiter».
  • Hex: Returns the hexadecimal value of an integer.
  • InStringArray: Parses «string_array» using «delimiter» and «text_qualifier» and returns a boolean if it contains «value».
  • JoinStringArray: Returns the list of «items» delimited by «delimiter» and qualified by «text_qualifier».
  • Left: Returns the part of «character_expression» that ends at and is to the left of «number» character position counting from the left.
  • Len: Returns the length of «character_expression».
  • Lower: Returns «character_expression» as all lower case characters.
  • LPad: Returns «character_expression» with added spaces on the left to make the length equal to «length».
  • LPad: Returns «character_expression» with «padding_value» added to the left until the length is as close to «length» as possible.
  • LTrim: Returns «character_expression» with no leading spaces.
  • LTrim: Returns «character_expression» with no leading occurrences of «trim_value».
  • NewGuid: Returns a new random GUID.
  • NullifyEmptyString: Returns null if «character_expression» is empty otherwise returns «character_expression».
  • NumberToString: Returns the «number» as a string with the format specified by «format_string».
  • RemoveDiacriticalMarks: Returns the «string_expression» without any diacritical marks (accents). ex: 'éléphant' will return 'elephant'.
  • Replace: Returns «character_expression» with all occurrences of «search_expression» replaced with «replace_expression».
  • Replicate: Returns «character_expression» replicated «times» number of times.
  • Reverse: Returns «character_expression» in reverse order.
  • Right: Returns the part of «character_expression» that ends at and is to the right of «number» character position counting from the right.
  • RPad: Returns «character_expression» with added spaces on the right to make the length equal to «length».
  • RPad: Returns «character_expression» with «padding_value» added to the right until the length is as close to «length» as possible.
  • RTrim: Returns «character_expression» with no trailing spaces.
  • RTrim: Returns «character_expression» with no trailing occurrences of «trim_value».
  • StartsWith: Returns a Boolean value representing if «character_expression» begins with «search_expression».
  • StrCount: Returns the number of occurrences of «search_expression» in «character_expression».
  • StripTagsFromHtml: Returns «source_html» without any HTML tags except for those specified in «except1, except2, except3...».
  • SubString: Returns a section of «character_expression» starting at position «start» and with a length of «length».
  • Token: Splits «character_expression» by «delimiter_expression» and returns the token at «occurance».
  • TokenCount: Returns the number of tokens when «character_expression» is split by «delimiter».
  • Trim: Returns a section of «character_expression» starting at position «start» and with a length of «length».
  • Trim: Returns «character_expression» with no leading or trailing occurrences of «trim_value».
  • UnescapeRegex: Returns the «string_expression» with Regex characters escaped.
  • UnescapeUri: Returns the «string_expression» with URI characters escaped.
  • UnescapeDataString: Returns «character_expression» with escaped URI characters, unescaped.
  • UnescapeXml: Returns the «string_expression» with XML characters escaped.
  • Upper: Returns «character_expression» as all upper case characters.

Type Casts (33)

  • (DT_BOOL): Attempts to cast the expression immediately to the right of the cast to a DT_BOOL.
  • (DT_BYTES): Attempts to cast the expression immediately to the right of the cast to a DT_BYTES.
  • (DT_CY): Attempts to cast the expression immediately to the right of the cast to a DT_CY.
  • (DT_DATE): Attempts to cast the expression immediately to the right of the cast to a DT_DATE.
  • (DT_DBDATE): Attempts to cast the expression immediately to the right of the cast to a DT_DBDATE.
  • (DT_DBTIME): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIME.
  • (DT_DBTIME2): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIME2.
  • (DT_DBTIMESTAMP): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIMESTAMP.
  • (DT_DBTIMESTAMP2): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIMESTAMP2.
  • (DT_DBTIMESTAMPOFFSET): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIMESTAMPOFFSET.
  • (DT_DECIMAL): Attempts to cast the expression immediately to the right of the cast to a DT_DECIMAL.
  • (DT_FILETIME): Attempts to cast the expression immediately to the right of the cast to a DT_FILETIME.
  • (DT_GUID): Attempts to cast the expression immediately to the right of the cast to a DT_GUID.
  • (DT_I1): Attempts to cast the expression immediately to the right of the cast to a DT_I1.
  • (DT_I2): Attempts to cast the expression immediately to the right of the cast to a DT_I2.
  • (DT_I4): Attempts to cast the expression immediately to the right of the cast to a DT_I4.
  • (DT_I8): Attempts to cast the expression immediately to the right of the cast to a DT_I8.
  • (DT_IMAGE): Attempts to cast the expression immediately to the right of the cast to a DT_IMAGE.
  • (DT_NTEXT): Attempts to cast the expression immediately to the right of the cast to a DT_NTEXT.
  • (DT_NTEXT, «format_string»): Attempts to cast the expression immediately to the right of the cast to a DT_NTEXT using the «format_string» to format the result.
  • (DT_NUMERIC, «precision», «scale»): Attempts to cast the expression immediately to the right of the cast to a DT_NUMERIC.
  • (DT_R4): Attempts to cast the expression immediately to the right of the cast to a DT_R4.
  • (DT_R8): Attempts to cast the expression immediately to the right of the cast to a DT_R8.
  • (DT_STR): Attempts to cast the expression immediately to the right of the cast to a DT_STR.
  • (DT_STR): Attempts to cast the expression immediately to the right of the cast to a DT_STR using the «format_string» to format the result.
  • (DT_TEXT): Attempts to cast the expression immediately to the right of the cast to a DT_TEXT.
  • (DT_TEXT): Attempts to cast the expression immediately to the right of the cast to a DT_TEXT using the «format_string» to format the result.
  • (DT_UI1): Attempts to cast the expression immediately to the right of the cast to a DT_UI1.
  • (DT_UI2): Attempts to cast the expression immediately to the right of the cast to a DT_UI2.
  • (DT_UI4): Attempts to cast the expression immediately to the right of the cast to a DT_UI4.
  • (DT_UI8): Attempts to cast the expression immediately to the right of the cast to a DT_UI8.
  • (DT_WSTR): Attempts to cast the expression immediately to the right of the cast to a DT_WSTR.
  • (DT_WSTR): Attempts to cast the expression immediately to the right of the cast to a DT_WSTR using the «format_string» to format the result.

Value Lookup Functions (6)

  • GetValueFromJson: Returns the value in the source «json» at the location specified by «jpath».
  • GetValueFromNameValueCollection: Returns the first value with the name of «name» from the «name_value_collection» where the name-value pairs are delimited by the «outter_delimiter» and separated by the «inner_delimiter».
  • GetValueFromXml: Returns the value in the source «xml» at the location specified by «xpath».
  • NameExistsInNameValueCollection: Returns a boolean value if the name of «name» exists in the «name_value_collection» where the name value-pairs are delimited by the «outter_delimiter» and separated by the «inner_delimiter».
  • ValueExistsInJson: Returns a boolean specifying if there is a value in the «json» at the specified «jpath».
  • ValueExistsInXml: Returns a boolean specifying if there is a value in the «xml» at the specified «xpath».

XML Functions (3)

  • ReplaceInvalidXmlChars: Returns «character_expression» with all invalid XML characters replaced with «replace_string».
  • XslTransform: Returns the result of performing an XSL transformation on «source_xml» using the provided «xsl».
  • XslTransform: Returns the result of performing an XSL transformation on «source_xml» using the provided «xsl».

Italicized Text indicates the function is also included in the native Derived Column SSIS component.