Snippet

BI xPress ›› Features ››
Parent Previous Next

Snippet Wizard


Snippet Wizard is a tool that allows BI developers to insert pre-built and tested functionality into their SSIS packages. Users can insert a task or many tasks that are included with BI xPress or they can build their own for reuse in other SSIS packages. Some of the included tasks (snippets) that come with BI xPress are email, HTTP download, Windows registry access, XML, file compression, and much more.

Feature Highlights

  Use templates to quickly add script tasks to a package control flow

  Standardize scripting among teams of developers by sharing scripts


Important Note:

Pragmatic Workbench (32-bit) must be used when applying Snippets to SSIS packages on a machine that only contains SSDT for Visual Studio 2015.


Tutorial Video


How to launch SSIS Snippet Wizard
You can launch snippet wizard 2 ways:

  Right click in the control flow and click "Add Snippet" menu item when SSIS package is open in Visual studio

  Launch from the Pragmatic Workbench


Walkthrough:

1.  On the first screen of the Snippet Wizard you must select the location and package.  If using SSDT or BIDS, the package is already selected and can skip to the next step.  After selecting the package, click next.


Important Note

The Snippet Wizard can only operate on one SSIS package at a time and a warning will be issued if more than one package is selected.




2.  On the next screen of the Snippet Wizard, select the snippet optionally filtering by scripting language (VB.NET or C#). You can also change the snippet library location if you prefer to store snippets anywhere other than the default location (e.g. <Program Files>\Pragmatic Works\BI xPress\SSIS Snippets).



Parameter Selection Screen

3.  After clicking next, you will see parameters for the snippet. If there is no parameter then you can skip this screen by clicking next again. On the Snippet Parameter screen you will see input/output parameters. You can check or uncheck parameters which you do not need.



Parameter values can be bound to any of the following type:

  Variable

  Constant

  Connection

  Connection Property


If you want to read/write parameter value from a variable then choose variable type from the "Parameter Type" drop down menu. If variable is not found in the package then you can create it by clicking "Create Variable". You can also create all missing variables at once by clicking "Next" which prompts the create variable screen.




Snippet File Editor

You can edit existing snippets by double clicking snippet on the Snippet Browser screen or click on "Edit File" option in the right click menu or toolbar.

On the Snippet Editor Dialog box you can define the following items on the snippet editor form:

  Snippet general properties

  Snippet Parameters

  Source code (VB.net/C#)
 


Snippet File Specification

Parent Element

Element

Attributes

Description

 

< Snippet >

Name

Name of the snippet (Optional). If this is blank then file name will be used.

Author

Author of snippet

Type

Snippet type (Allowed values: ControlFlow)

ModifiedOn

When snippet was modified

CreatedOn

Snippet creation date

Description

Snippet description (For multi-line description use the LongDescription property. See below)

< Snippet >

< Property Name = "LongDesc" >

 

If you need long description with HTML tags use this property (see example below)

<Property Name="LongDesc"><![CDATA[Read various file properties (e.g. file size, directory name, attributes)]]></Property>

< Snippet >

< Property Name = "VersionHistory" >

 

Store version history of the snippet (see example below)

<Property Name="VersionHistory"><![CDATA[version-1 : Initial release]]></Property>

< Snippet >

< Task >

Name

Default name of task. If another task with same name exists then unique name will be generated by adding counter at the end (e.g. MyTask1, Mytask2 ...)

 

 

Type

Type of task (Allowed values: ScriptTask)

< Task Type = "ScriptTask" >

< Assembly >

 

Example:

<AssemblyReference>Microsoft ®.SQLServer.ManagedDTS</AssemblyReference>

 -- Or --

<AssemblyReference>Microsoft ®.SQLServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL</AssemblyReference>

< Task Type = "ScriptTask" >

< Parameter >

Name

Property Name

Value

 

Category

Not implemented

Direction

Direction of parameter (Allowed values: Input, Output)

IsOptional

If parameter is not option then you cannot set Enabled to False.  When IsOptional is True then any CodeSection with matching parameter name will skipped in code generation when parameter is not Enabled.

Enabled

This attribute will indicate whether any code section marked with parameter name should be included or excluded.

Description

Description of parameter (will be displayed in the parameter selection grid)

DataType

Any valid SSIS Datatype for variable (e.g. System.Int32, System.String ...)

BindingType

Parameter type (Allowed values: Constant, Variable, Connection, ConnectionProperty)

AllowBlank

If this attributes is false then you cannot leave parameter value blank when it is enabled.

AllowedBindingTypes

Allowed type which you can select as BindingType. Use vertical bar {|} to separate multiple values (Allowed values: Constant, Variable, Connection, ConnectionProperty)

BoundObjectName

This attribute represents what object name should be used for selected BindingType. (e.g. When BindingType=Variable then BoundObjectName represents VariableName, When BindingType=Connection then BoundedObjectname represents ConnectionManager Name). This attribute is ignored when BindingType=Constant

AllowedConnectionTypes

Allowed connection manager types when BidningType=Connection or ConnectionProperty. Use vertical bar {|} to separate multiple values (Allowed values:  any valid connection manager (see creationname property of connection manager)... e.g. FLATFILE, FILE, OLEDB)

LookupValues

If parameter need to be a drop-down menu then specify valid list of values separated by vertical bar {|}.

LookupValueSap

This is optional but if you want LookupValues separated by other than vertical bar then specify new separator here.

ValidationType

You can specify any of the following validation type for parameter. When you enable validation parameter value will be validated automatically based on specified validation rule.
 

Validation Type

Description

None (Default)

No validation

Equal

Parameter value must be equal to value defined by ValidationValue1 attribute

NotEqual

Parameter value must not be equal to value defined by ValidationValue1 attribute

Between

Parameter value must be between values defined by ValidationValue1 and ValidationValue2 attributes

NotBetween

Parameter value must not be between values defined by ValidationValue1 and ValidationValue2 attributes

GreaterThan

Parameter value must be greater than value defined by ValidationValue1 attribute

LessThan

Parameter value must be less than value defined by ValidationValue1 attribute

StartsWith

Parameter value must start with value defined by ValidationValue1 attribute

EndsWith

Parameter value must end with value defined by ValidationValue1 attribute

Contains

Parameter value must contain substring defined by value of ValidationValue1 attribute

NotContains

Parameter value must not contain substring defined by value of ValidationValue1 attribute

Like

Parameter value must match with string pattern defined by value of ValidationValue1 attribute.

http://msdn.Microsoft ®.com/en-us/library/swf8kaxw(VS.80).aspx

Examples:

Office*: Value must start with Office word
[A-C][0-5]*: Value must start with first character between A to C and Second character 0 to 5.

NotLike

Parameter value must not match with string pattern defined by value of ValidationValue1 attribute.

RegX

For more complex string pattern validation you can use regular expression validation. Define regular expression in ValidationValue1 attribute.


PasswordChar

If your parameter represents sensitive information and you don’t want to show in clear text in the user interface then use this attribute. Default password character is "*"

ValidationErrorMessage

Custom error message which is displayed when parameter validation fails

ValidationValue1

ValidationType attribute refers this value when parameter value is compared against single value (e.g. Equal, NotEqual, Like, NotLike)

ValidationValue2

ValidationType attribute refers this value when parameter value is compared against multiple values (e.g. Between, NotBetween)

EnableMultiLineSupport

When you need to define parameter as a constant which may have several lines of input string (e.g. EmailBody parameter) then you can create string constant with StringBuilder for better performance and readability by setting EnableMultiLineSupport=True. The following example shows how your place holder will be replaced with Proper string Builder Code.

Snippet Code

Dim SB_Body As New System.Text.StringBuilder

<@Body>


Code After Parameter Replacement

Dim SB_Body As New System.Text.StringBuilder

SB_Body.AppendLine("Hello World Line1")
SB_Body.AppendLine("Hello World Line2")
SB_Body.AppendLine("Hello World Line3")

SendMail(SB_Body.ToString())

Important Note

String Builder Variable name must be named as:

SB_{{ParameterName}}