The Premium Derived Column component expands on the native SSIS Derived Column to give developers more power in SSIS to easily perform advanced transformations without any custom scripts.

The Premium Derived Column component part of the SSIS Productivity Pack offers all the functionality of the SSIS out-of-box Derived Column component and expands on it to create a component with greater potentials, features, and ease-of use. The Premium Derived Column component is the ultimate component for SSIS developers. With over 294 functions, the Premium Derived Column component allows developers to create advanced expressions easily for SSIS transformations not once possible without custom scripts.

Try it now for Free

SSIS Premium Derived Column

Feature Out-of-Box Derived Column Component
Premium Derived Column Component

Add Columns


Yes


Yes

Utilize Expressions to Transform Data
Yes

Yes
Number of Functions <100 >294
Function Search Bar
No

Yes

Error Handling

  • Fail on error
  • Ignore error
  • Redirect to output

Yes

Yes
Advanced Data Type Detection
No

Yes
Advanced Expression Editor
No


Yes

Quickly build advanced expressions in SSIS.

Expression Validation

No


Yes

Evaluate and validate every expression with one click.

Expression Testing
No


Yes

Enter test values to test expressions prior to execution.

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 on what it achieves.

  • 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».
  • 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».
  • 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».
  • ComputeMD5Hash: Returns the MD5 hash of «bytes».
  • ComputeMD5HMCA: Returns the MD5 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeRIPEMD160Hash: Returns the RIPEMD160 hash of «bytes».
  • ComputeRIPEMD160HMCA: Returns the RIPEMD160 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA1Hash: Returns the SHA1 hash of «bytes».
  • ComputeSHA1HMCA: Returns the SHA1 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA256Hash: Returns the SHA256 hash of «bytes».
  • ComputeSHA256HMCA: Returns the SHA256 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA384Hash: Returns the SHA384 hash of «bytes».
  • ComputeSHA384HMCA: Returns the SHA384 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA512Hash: Returns the SHA512 hash of «bytes».
  • ComputeSHA512HMCA: Returns the SHA512 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • DecryptUsingAesCbc: Returns «bytes» decrypted using the AES-CBC algorithm and the provided «password».
  • DecryptUsingAesXts: Returns «bytes» decrypted using the AES-XTS algorithm and the provided «password».
  • DecryptUsingTripleDesCbc: Returns «bytes» decrypted using the 3DES-CBC algorithm and the provided «password».
  • DecryptUsingTwofishCbc: Returns «bytes» decrypted using the Twofish-CBC algorithm and the provided «password».
  • EncryptUsingAesCbc: Returns «bytes» encrypted using the AES-CBC algorithm and password set to «password».
  • EncryptUsingAesXts: Returns «bytes» encrypted using the AES-XTS algorithm and password set to «password».
  • EncryptUsingTripleDesCbc: Returns «bytes» encrypted using the 3DES-CBC algorithm and password set to «password».
  • EncryptUsingTwofishCbc: Returns «bytes» encrypted using the Twofish-CBC algorithm and password set to «password».
  • 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».
  • 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».
  • HtmlDecode: Returns the HTML encoded «character_expression» as a decoded string.
  • HtmlEncode: Returns «character_expression» as a HTML encoded string.
  • UrlDecode: Returns the URL encoded «character_expression» as a decoded string.
  • UrlEncode: Returns «character_expression» as a URL encoded string.
  • 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.
  • 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.
  • 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.
  • - (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)
  • IfElse: Returns the «result» whose «condition» evaluates to true. If none of them evaluate to true then «default_result» is returned.
  • RowIndex: Returns the current row index.
  • RowIndex(): Returns the current row index.
  • 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».
  • 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».
  • 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».
  • Capitalize: Returns «character_expression» with the first character in every word converted to upper case and all other characters converted to lower case.
  • 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.
  • 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.
  • 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.
  • 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».
  • 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.
  • UnescapeXml: Returns the «string_expression» with XML characters escaped.
  • Upper: Returns «character_expression» as all upper case characters.
  • (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_NUMERIC): 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_TEXT): Attempts to cast the expression immediately to the right of the cast to a DT_TEXT.
  • (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.
  • 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 seperated 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 seperated 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».
  • ReplaceInvalidXmlChars: Returns «character_expression» with all invalid XML characters replaced with «replace_string».
  • XslTransform: Returns the result of performing an XSL tranformation on «source_xml» using the provided «xsl».
  • XslTransform: Returns the result of performing an XSL tranformation on «source_xml» using the provided «xsl».

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

Download the SSIS Productivity Pack with the Premium Derived Column now for free