BI xPress Snippet Wizard

Introduction

IconDescription
BI xPress Snippet IconSnippet Wizard allows BI developers to insert prebuilt and tested functionality into their SSIS packages. You can insert a task or multiple tasks that are included with BI xPress, or you can build your own tasks to 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

Note:  You must use the SentryOne Workbench (32-bit) to apply Snippets to SSIS packages on a machine that only contains SSDT for Visual Studio 2015.

Open the Snippet Wizard by doing one of the following:

  • Open the desired SSIS Project in Visual Studio, right click in the control flow and then select Add Snippet from the context menu.
  • Open the SentryOne Workbench and then select the Snippet feature.

Using the Snippet Wizard

Open the Snippet Wizard to continue to the Select Location and Packages page. 

1. Select a location from the drop-down list, and then select the path for your package. Select Next to continue to the Select Template page.BI xPress Snippet Wizard Select Location and Packages

Note:  If you are using SSDT or BIDS in Visual Studio, your package will already be selected and you can skip to the next step.

2. Select your snippet. You can optionally filter the available snippets 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 (for example, <Program Files>\Pragmatic Works\BI xPress\SSIS Snippets). Select Next to continue to the Specify Parameters and Preview page.BI xPress Snippet Wizard Select Template

3. Select or deselect the snippet parameters that you do not need. Select Next to open the Add Variable window.

Note:  If there is no parameter for your snippet, you can continue to the next step by selecting Next.

4. Add your Parameter value. Select a Parameter type, and enter a parameter variable. Select Ok to save your changes. 

Parameters can be bound to any of the following types:

  • Variable
  • Constant
  • Connection
  • Connection Property
BI xPress Snippet Wizard Variable types

Note:  If you want to read/write a parameter value from a variable, select the variable type from the Parameter Type drop down menu. If the variable is not found in the package, you can create the variable by selecting Create Variable. You can also create all missing variables at once by selecting Next to open the create variable screen.

BI xPress Snippet Wizard Create Variable
BI xPress Snippet Wizard Add Variable window

Snippet File Editor

BI xPress Snippet Editor

You can edit existing snippets by double clicking snippet on the Snippet Browser screen, or selecting the Edit File option from the context menu or toolbar. You can use the Snippet Editor Dialog box to define the following items:

  • Snippet general properties
  • Snippet Parameters
  • Source code (VB.net/C#)

Snippet File Specification

<Snippet>

AttributeDescription
NameName of the snippet (Optional). If this is blank then file name will be used.
AuthorAuthor of snippet.
TypeSnippet type (Allowed values: ControlFlow)
ModifiedOnWhen snippet was modified.
CreatedOnSnippet creation date.
DescriptionSnippet description (For multi-line description use the LongDescription property. See below)

<Snippet> Parent Element

ElementAttributesDescription
< Property Name = "LongDesc" >N/A

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>

< Property Name = "VersionHistory" >N/AStore version history of the snippet (see example below) <Property Name="VersionHistory"><![CDATA[version-1 : Initial release]]></Property>
< Task >N/ADefault 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 ...)

<Task Type ="ScriptTask"> Parent Element

< Assembly >

AttributesDescription
N/AExample: <AssemblyReference>Microsoft ®.SQLServer.ManagedDTS</AssemblyReference>  -- Or -- <AssemblyReference>Microsoft ®.SQLServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL</AssemblyReference>

<Parameter>

AttributesDescription
NameProperty Name
Value
CategoryNot implemented
DirectionDirection of parameter (Allowed values: Input, Output)
IsOptionalIf 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.
EnabledThis attribute will indicate whether any code section marked with parameter name should be included or excluded.
DescriptionDescription of parameter (will be displayed in the parameter selection grid)
DataTypeAny valid SSIS Datatype for variable (e.g. System.Int32, System.String ...)
BindingTypeParameter type (Allowed values: Constant, Variable, Connection, ConnectionProperty)
AllowBlankIf this attribute is false then you cannot leave parameter value blank when it is enabled.
AllowedBindingTypesAllowed type which you can select as BindingType. Use vertical bar {|} to separate multiple values (Allowed values: Constant, Variable, Connection, ConnectionProperty)
BoundObjectNameThis 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)

LookupValuesIf parameter need to be a drop-down menu then specify valid list of values separated by vertical bar {|}.
LookupValueSapThis is optional but if you want LookupValues separated by other than vertical bar then specify new separator here.
ValidationTypeYou 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. See the ValidationType table below for more information.
PasswordCharIf 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 "*"
ValidationErrorMessageCustom error message which is displayed when parameter validation fails
ValidationValue1ValidationType attribute refers this value when parameter value is compared against single value (e.g. Equal, NotEqual, Like, NotLike)
ValidationValue2ValidationType attribute refers this value when parameter value is compared against multiple values (e.g. Between, NotBetween)
EnableMultiLineSupportWhen 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())

ValidationType

Validation TypeDescription
None (Default)No validation.
EqualParameter value must be equal to value defined by ValidationValue1 attribute.
NotEqualParameter value must not be equal to value defined by ValidationValue1 attribute.
BetweenParameter value must be between values defined by ValidationValue1 and ValidationValue2 attributes.
NotBetweenParameter value must not be between values defined by ValidationValue1 and ValidationValue2 attributes.
GreaterThanParameter value must be greater than value defined by ValidationValue1 attribute.
LessThanParameter value must be less than value defined by ValidationValue1 attribute.
StartsWithParameter value must start with value defined by ValidationValue1 attribute.
EndsWithParameter value must end with value defined by ValidationValue1 attribute.
ContainsParameter value must contain substring defined by value of ValidationValue1 attribute.
NotContainsParameter 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.

NotLikeParameter value must not match with string pattern defined by value of ValidationValue1 attribute.
RegXFor more complex string pattern validation you can use regular expression validation. Define regular expression in ValidationValue1 attribute.