SSIS Set Variable Transform
 PreviousNext

SSIS Set Variable Transform (FREE) can be used to set SSIS Variable inside data flow. You can choose any upstream column and set column value into the SSIS Variable. It also supports various advanced modes (Append, Replace, Increment....)

Download SSIS PowerPack 
NOTE: This transform must be connected to downstream (e.g. Trash Destination). If its is not connected to downstream then you must set Data Flow property RunInOptimizedMode=false. If you dont do this then SSIS Engine will ignore unattached transforms for optimization and it may never get executed. If you execute using Visual Studio then Optimization is turned off so it may just work fine without any additional workaround but if you execute using command line or SQL Agent then you may notice this behaviour of SSIS Engine.

Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will learn how to set value in variable using Set variable Transform.
  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 SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the DataFlow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the ZS Dummy Data Source and ZS Set Variable Transform in the design panel and join the components with the blue arrow.
    SSIS Set Variable Transform - Drag and Drop
  6. Here, you can use OLE DB Data Source, MongoDB Source, and CSV File Source.
  7. We need to create Variable.

How to create Variable.

  1. In Visual Studio Right Click on Design Panel, Select Variables.
    Create Variable
  2. Just Give Name of Variable with String DataType.
    Create Variable

How to set value in Variable using Set Variable Transform.

  1. Lets, Click Here for How to Configure ZS Dummy Data Source.
  2. Now Double click on Set Variable Transform to configure it.
  3. In the Component Properties, you can select Various VariableSetMode Property, and select Variable in VariableToSet we have created before.
    SSIS Set Variable Transform - Configure
  4. Next move on Input Columns Tab, select columns.
    SSIS Set Variable Transform - Configure
  5. Click on OK button to Save Set Variable Transform UI Settings.
  6. Now, Just Drag and Drop Our Free ZS Trash Destination from SSIS Toolbox.
    SSIS Trash Destination - Drag and Drop
  7. Now single click on the Set Variable Transform, once you see blue arrow from source ... connect it to Trash Destination.
  8. Lets Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Drag and Drop
  9. Here, you can also connect ZS Logging Task to show extracted value.
  10. Thats all, Just Run or Execute your Package Project.
    SSIS Set Variable Transform - Execute

Properties

Property Name Description
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
VariableSetMode Action you like to perform. If you set any action with ReplacePlaceholder then you must use placeholder <%value%> where you like to insert upstream value. Use variable placeholder (e.g. {{User::MyVar}} ) if you wish to replace value from variable. Example for placeholder use: { tags : [<%value%>] , Package: "{{System::PackageName}}" }

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Set [0] Set
Increment [1] Increment
IncrementByBuffer [2] IncrementByBuffer
IncrementByColumnValue [3] IncrementByColumnValue
Append [4] Append
AppendAndReplacePlaceholders [5] AppendAndReplacePlaceholders
SetWithReplacePlaceholders [6] SetWithReplacePlaceholders
VariableToSet Name of variable you want to set (e.g. User::vMyData)
IncrementBy Number to add in previous variable value (This number can be -ve or +ve)
RowSeparator Separator to use for Append mode (not valid for other mode). You can use \r\n for new line, \t for tab, \n for unix style new line.
LastRowSeparator Separator to use for last row (Only valid of .
Header Header string to use for Append mode.
Footer Footer string to use for Append mode.
UseCustomValueForNull By default SSIS doesn't allow to assign NULL value to SSIS Variable. Turn on this setting to use custom value for NULL. Set CustomValueForNull as well to use custom value for NULL
CustomValueForNull If you set UseCustomValueForNull=true then this Custom Value will be used when NULL value is detected as input. If you leave CustomValueForNull blank then we will change NULL string to Blank, NULL Int32,Int64,Decimal,Double use 0

Setting UI

SSIS Set Variable Transform - Setting UI
SSIS Set Variable Transform - Setting UI
SSIS Set Variable Transform - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Set Variable Transform] category
How to convert Varbinary to Base64 in SSIS

How to convert Varbinary to Base64 in SSIS

Introduction In this post we will show you how to convert varbinary to Base64 in SSIS. We will use FREE Tasks provided by ZappySys. Preparing Sample Data First let’s create a sample table with some Varbinary datatype. Run following command in SSMS to create a sample table with one sample row.


Copyrights reserved. ZappySys LLC.