Literal SQL - SSIS

Literals are hard coded information that you must provide when building expressions. SSIS expressions have three types of literals: numeric, string, and Boolean.

Numeric Literals

A numeric literal is simply a fixed number. Typically, a number is assigned to a variable or used in an expression. Numeric literals in SSIS have the same behavior that they have in C# or Java — you can’t just implicitly define numeric literals. Well, that’s not completely true; SSIS does interpret numeric values with a few default rules, but the point is that the rules are probably not what you might expect. A value of 12 would be interpreted as the default data type of DT_UI4, or the 4-byte unsigned integer. This might be what you want, but if the value were changed to 3000000000 during the evaluation process, an error similar to this will be generated:

The literal “3000000000” is too large to fit into type DT_UI4. The
magnitude of the
literal overflows the type.

SSIS operates on literals using logic similar to the underlying .NET CLR. Numeric literals are checked to see if they contain a decimal point. If they do not, the literal is cast using the unsigned integer DT_UI4 data type. If there is a decimal point, the literal is cast as a DT_NUMERIC. To override these rules, you must append a suffix to the numeric literal. The suffix enables a declarative way to define the literal. The following are examples of suffixes that can be used on numeric literals:

Knowing these suffixes and rules, the previous example can be altered to 3000000000L, and the expression can be validated.

String Literals

When building strings, there are times when you need to supply special characters in them. For example, PostgreSQL database sources require the use of quoted column and table names. The key here is to understand the escape sequences that are understood by the expression syntax parser. The escape sequence for the double quote symbol is ”. A sample expressiongenerated SQL statement might look like this:

Check Out SSIS Tutorials

The preceding expression would generate the following string:

Select “myData” from “owner”.”myTable”

Other common literals that you may need are listed in this table:

A few other string escape sequences are supported, but the elements in this table list those most frequently used. The backslash escape sequences come in handy when building file and path strings. The double quote escape sequence is more often used to interact with data sources that require quoted identifiers. This escape sequence is also used in combination with the remaining new line and tab characters to format strings for logging or other reporting purposes.

Boolean Literals

The Boolean literals of True and False don’t have to be capitalized, nor are they case sensitive. Boolean expressions are shortcut versions of the logical operators. To drive certain package functionality conditionally based on whether the package is running in an offline mode, you could write an expression in a variable using an artificial on or off type switch mechanism, as shown here:

@[System::OfflineMode]==True ? 1 : 0 (Not Recommended) 

Frequently Asked SSIS Interview Questions & Answers

The idea is to use the results of this operation to determine whether a precedence constraint should operate. The precedence operator would retest the expression to determine whether the value was 1 or 0. However, this is an awfully long way to do something. It’s much easier to just create an expression that looks like this:

@[System::OfflineMode]==False

Then all you have to do is plug the expression into the Precedence Editor, as shown in below Screen Shot

Note that using the literal is recommended over using any numeric values for evaluation. Programming any expression to evaluate numeric versions of Boolean values is dangerous and should not be a part of your SSIS techniques.

List of Related Microsoft Certification Courses: