Help > Appendix >
Format Specifiers
PreviousNext

Content

Introduction

SSIS supports C# style expression language to change properties at runtime dynamically. However SSIS Expression language has its own drawbacks. SSIS PowerPack introduces new concept called Variable Placeholders to make things much cleaner and easier. You can simply type Variable name with double opening and closing curly braces anywhere in your input text (e.g. {{User::MyVariable}} ). It also support some most common formatting functions too (e.g. {{User::MyDate,yyyyMMdd}} ). To know which properties support placeholers, you can simply look for variable icon or some times edit icon somewhere near to property editor on UI.
You can pass input values for placeholders 3 ways. Some functions have optional input values (e.g. FUN_GETDATE).
  1. Input value from SSIS Variables.
  2. Direct value.
  3. Mixed mode Examples:
    {{User::varOrderDate, yyyy-MM-dd}}	
    	
    <<my lowercase string, FUN_TRIM>>
    {{User::varInputStr, FUN_TRIM}}
    
    <<a2V5MTIz||my input string, FUN_HMAC_MD5>>
    {{User::varInputStr, FUN_HMAC_MD5}}
    
    <<FUN_GETDATE>>
    <<yyyy-MM-dd, FUN_GETDATE>>
    
    <<{{User::varUserId}}:{{User::varPassword}}, FUN_BASE64ENC>>
    {{User::varUserIdAndPassword, FUN_BASE64ENC}}
    
    	

Step-By-Step

In this tutorial we will learn how to use Variable Placeholders to make things much cleaner and easier. You can pass input values for placeholders 3 ways(Input value from SSIS Variables, Direct value and Mixed mode Examples).
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop ZS Logging Task from SSIS Toolbox.
    SSIS ZS Logging Task - Drag and Drop
  4. Now, lets create a Variable and store value.
    SSIS ZS Logging Task - Create Variable
  5. Double click on Logging Task to configure it.
  6. Here Input Value from SSIS Variables. Set Log Mode to MEssageBox, Click on Insert Variables and select Function Examples and You can see MD5 Hash function.
    SSIS ZS Logging Task - Configure

    ----- OR -----

    You can use Hardcoded direct value.
    <<hello,FUN_HASH_MD5>>
    <<hello,FUN_HASH_MD5_HEX>>
    
    SSIS ZS Logging Task - Configure

    ----- OR -----

    You can use variable with Hardcoded for value.
    <<varMyData,FUN_HASH_MD5>>
    <<varMyData,FUN_HASH_MD5_HEX>>
    
    SSIS ZS Logging Task - Configure
  7. Click on OK button to save Logging Task Configure Setting UI.
  8. Now, Execute or run project package.
    SSIS ZS Logging Task - Execute

Function List

Along with format specifiers you can also use functions. See below list.

Function name Description
FUN_READFILE Reads file content (input must be file path) and replace placeholder
Syntax: input_file_path,FUN_READFILE
FUN_TRIM Trim white spaces from start and end of input string
Syntax: input_string,FUN_TRIM
FUN_BASE64ENC Encodes input string to BASE64 format
Syntax: input_string,FUN_BASE64ENC
FUN_FILE_BASE64ENC Generates BASE64 encoded string of file data (Binary or Text)
Syntax: input_file_path,FUN_BASE64ENC
FUN_BASE64DEC Decodes input string from BASE64 format
Syntax: input_encoded_string,FUN_BASE64DEC
FUN_URLENC Encodes input string to percentage encoding format for URL (e.g. 1*&*2 is encoded to 1*%26*2)
Syntax: input_string,FUN_URLENC
FUN_URLDEC Decodes url encoded value to plain text
Syntax: input_string,FUN_URLDEC
FUN_GETDATE Returns current local date/time in a specified format
Syntax: [date_format,]FUN_GETDATE
FUN_GETDATE_UTC Returns current UTC date/time in a specified format
Syntax: [date_format,]FUN_GETDATE_UTC
FUN_TO_UTC_DATE Converts local date to UTC date (Time portion is removed)
Syntax: input_date_time,FUN_TO_UTC_DATE
FUN_TO_UTC_DATETIME Converts local date/time to UTC date/time
Syntax: input_date_time,FUN_TO_UTC_DATETIME
FUN_TO_LOCAL_DATE Converts UTC date to local date (Time portion is removed)
Syntax: input_date_time,FUN_TO_LOCAL_DATE
FUN_TO_LOCAL_DATETIME Converts UTC date/time to local date/time
Syntax: input_date,FUN_TO_LOCAL_DATETIME
FUN_MD5 Returns MD5 hash in Base64 format for specified input
Syntax: input_string,FUN_MD5
FUN_FILE_MD5 Returns MD5 hash in Base64 format for specified file content (input is file path)
Syntax: input_file_path,FUN_FILE_MD5
FUN_MD5HEX Returns MD5 hash in Hex format for specified input
Syntax: input_string,FUN_MD5HEX
FUN_HMAC_MD5 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the MD5 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_MD5
FUN_HMAC_SHA1 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA1 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_SHA1
FUN_HMAC_SHA256 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA256 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_SHA256
FUN_HMAC_SHA384 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA384 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA512 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA512 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_SHA512
FUN_HMAC_MD5_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the MD5 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_MD5_HEX
FUN_HMAC_SHA1_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA1 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_SHA1_HEX
FUN_HMAC_SHA256_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA256 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_SHA256_HEX
FUN_HMAC_SHA384_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA384 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_SHA384_HEX
FUN_HMAC_SHA512_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA512 hash function (Hash input data using user defined secret key)
Append --non-base64 option after secret if your key is not base64 encoded

Syntax: secret_key[--non-base64]||input_string,FUN_HMAC_SHA512_HEX
FUN_JSONENC Encodes string for JSON value (e.g. New line is replaced by \r\n, double quote is replaced by \")
Syntax: input_string,FUN_JSONENC
FUN_JSONDEC Decodes JSON string (e.g. \r\n is replaced by New line, \" is replaced by double quote)
Syntax: input_string,FUN_JSONDEC
FUN_XMLENC Encodes string for XML value (e.g. > is replaced by &gt; , < is replaced by &lt;)
Syntax: input_string,FUN_XMLENC
FUN_XMLDEC Decodes XML value (e.g. &gt; is replaced by > , &lt; is replaced by <)
Syntax: input_string,FUN_XMLDEC
FUN_SEQUENCE Returns a unique number in a sequence (Previous number + 1)
Syntax: FUN_SEQUENCE
FUN_NEW_GUID Returns a unique GUID. Format can be blank or any valid format listed here (i.e. N, D, B, P, X)
Syntax: [format,]FUN_NEW_GUID
FUN_RANDOM_INT Returns a random integer number
Syntax: [max_int_value,]FUN_RANDOM_INT
FUN_RANDOM_STR Returns a random string of a specified length (If length is not specified, it returns max 10 chars long string)
Syntax: [max_string_length,]FUN_RANDOM_STR

Date Time Formatting

You can use DateTime format specifiers like below as long as variable DataType is DateTime. Click here to see full list of datetime format specifiers. Click Here to see list of predefined standard date time formats.

Example Sample Output
{{System::StartTime,yyyy}} 2016
{{System::StartTime,yyyyMMdd}} 20161231
{{System::StartTime,MMM dd, yyyy}} Dec 31, 2016
{{System::StartTime,MMMM dd, yyyy}} December 31, 2016
{{System::StartTime,yyyy-MM-ddTHH:mm:ss.fffZ}} 2016-12-31T23:59:59.999Z
{{System::StartTime,yyy-MM-dd hh:mm:ss tt}} 2016-12-31 11:59:59 PM

Numeric Formats

You can use numeric format specifiers like below as long as variable DataType is Numeric (e.g Int32, Decimal etc). Click here to learn how to use custom numeric format specifiers.

Example Sample Output
{{User::SomeInt,N}} where SomeInt=1222333 1,222,333.00
{{User::SomeInt,N0}} where SomeInt=1222333 1,222,333
{{User::SomeInt,N3}} where SomeInt=1222333 1,222,333.000
{{System::PhoneNumber,###-###-####}} where PhoneNumber=1112223333 111-222-3333
{{User::Amount,C2}} where Amount=123.4522222 $123.45
{{User::Amount,C4}} $123.4567
{{User::Amount,######0}} 0000123

RecordSet Format Specifier

Few tasks support really easy way to dump ResultSet variable (e.g. ADO.net DataTable or DataSet) as HTML code which you can view in browser. This feature can be very handy with tasks such as ZS Logging Task, Send HTML Email Task. You can save recordset as HTML File or use inside email body.

Example Sample Output
{{User::varObjResult,table}} Renders HTML Table of RecordSet object. You can use ExecuteSQL Task or RecordSet destination to populate variable with Dataset. Variable must be object datatype

URL Encode/decode functions

Many times you have to pass parameters to your REST API Calls which needs to be in URL encoded format (Also known as percentage format e.g. %20). If you have this need then you can use FUN_URLENC or FUN_URLDEC specifiers.
Usecase: When you pass data in URL or POST body then you can use FUN_URLENC and FUN_URLDEC like below
UserID={{User::varUid,FUN_URLENC}}&Pass={{User::varPass,FUN_URLENC}}

Example Sample Output
{{User::Formula,FUN_URLENC}} where value of Formula is 1*&*2 1*%26*2
{{User::Data,FUN_URLENC}} where value of Data is 1 2 3 1+2+3
{{User::Data,FUN_URLDEC}} where value of Formula is 1+2+3 1 2 3
{{User::Data,FUN_BASE64ENC}} where data is ABCDEF123## QUJDREVGMTIzIyM=
{{User::Data,FUN_BASE64DEC}} where data is QUJDREVGMTIzIyM= ABCDEF123##

JSON Encode/decode functions

Many times you have to create simple JSON by inserting variables into JSON template. SSIS Powerpack has functions to make sure your value is properly encoded as per JSON rules.
Usecase: When you pass JSON data in API POST body which needs value from variables then you can use FUN_JSONENC like below
{ ErrorMessage : {{User::varMessage,FUN_JSONENC}} }

Example Sample Output
{{User::varData,FUN_JSONENC}} where value of varData is ... 1[newline]2[newline]3 1\r\n2\r\n3\r\n
{{User::varData,FUN_JSONDEC}} where value of varData is ... 1\r\n2\r\n3\r\n 1[newline]2[newline]3

XML Encode/decode functions

Many times you have to create simple XML by inserting variables into XML template. SSIS Powerpack has functions to make sure your value is properly encoded as per XML rules.
Usecase: When you pass XML data in API POST body which needs value from variables then you can use FUN_XMLENC like below
<ErrorMessage>{{User::varMessage,FUN_XMLENC}}</ErrorMessage>

Example Sample Output
{{User::varData,FUN_XMLENC}} where value of varData is ... "1<2<3" &quot;1&lt;2&lt;3&quot;
{{User::varData,FUN_XMLDEC}} where value of varData is ... &quot;1&lt;2&lt;3&quot; "1<2<3"

MD5 Hashing functions

Many times you have to send value HASH to your API server. You can use following Hashing function to hash your input value.
Example Sample Output
{{User::varData,FUN_MD5}} where value of varData is ... ABC kC+90rHfDE9wtKXSNSXpMg==
{{User::varData,FUN_MD5}} where value of varData is ... ABC kC+90rHfDE9wtKXSNSXpMg==
{{User::varFile,FUN_FILE_MD5}} where value of varFile is C:\data\MyFile.txt and content of that file is ... ABC kC+90rHfDE9wtKXSNSXpMg==
{{User::varData,FUN_MD5HEX}} where value of varData is ... ABC 902FBDD2B1DF0C4F70B4A5D23525E932

HMAC functions

Many times you have to send secure data by using Hash-based Message Authentication Code (HMAC). Below section summarizes various functions you can use to generate secure data. Basically you have to supply your Secret Key (Must be Base64 format) and Value along with FUNCTION name.
Example Sample Output
<<a2V5MTIz||Hi my name is A,FUN_HMAC_MD5>>

where a2V5MTIz is secret key used to generate HMAC
EICjBYkqIW6HpU0VOkIABQ==
<<a2V5MTIz||Hi my name is A,FUN_HMAC_MD5_HEX>>

where a2V5MTIz is secret key used to generate HMAC
1080a305892a216e87a54d153a420005
<<a2V5MTIz||POST\nx-csod-api-key:ABCD12345\nx-csod-date:2012-09-08T11:27:32.000\n/services/api/sts/session,FUN_HMAC_MD5_HEX>>

where a2V5MTIz is secret key used to generate HMAC. Notice that we have used \n in the input string. You can use \r or \n in the input string
RMZtvDgBFOubtNOKzQcbB5K6G93nVeZEu1uVnXIvkTYcaM81s1gexu+psi++H6XxQ3imJwaIhv+mOE0G3UKWdg==

File Operation functions

Many times you have to read file content and supply it part of some property. Use below function to perform indirect read.
Example Sample Output
<<c:\url.txt,FUN_READFILE>>

where data inside c:\url.txt contains some URL
http://myhost

Using direct string rather than variable

Version 2.4 introduced new feature to apply function on direct string rather than SSIS Variable placeholders.
	To apply placeholder function on direct string you must enclose data in double angle brackets (not curly brackets) and function name must start with FUN_ (e.g. <<myuser1:mysecretpass123,FUN_BASE64ENC>>).
	

Usecase: You want to encode userid and password to base64 format without extra steps to specifying SSIS variable like below
Authorization: <<mysecretpass123,FUN_BASE64ENC>>

Above string will produce following result.
Authorization: bXlzZWNyZXRwYXNzMTIz

Using SSIS Variables inside Direct String Placeholders

So in previous section we talked about using static input values inside placeholders but what about using some static string and some dynamic from SSIS Variable. In version 2.7 introduced a new feature to apply Mixed placeholders (i.e. Using SSIS Variables inside static placeholders).

Here is an example of how you can mix static string and dynamic string inside placeholder.

	<<Basic {{User::vUserId}}:{{User::vPassword}},FUN_BASE64ENC>>
	


Copyrights reserved. ZappySys LLC.