[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

In SSIS, is it possible to save a value within a Data Flow Task into a Variable?

I have a value which comes from a SQL query against an Excel file.  I want to save that value into a variable.  I have been able to get that value using Excel Source component.  What should I do next?  Thanks.
4 Solutions
thomaszhwangAuthor Commented:
I tried to use the Recordset Desitination.  However it requires an Object type Variable.

Actually the SQL only returns one row and one column, I just want to save the value in that cell into the Variable.  Thanks.
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Actually the SQL only returns one row and one column, I just want to save the value in that cell into the Variable.

If you are returning values from Excel to SQL Server using a Execute SQL Task, then you can directly pass that value to a variable.
If not, then insert this value into a temporary table and get that value into a variable
This might help

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

ValentinoVBI ConsultantCommented:
This can be done through the Script Component.  Add your variable to the ReadWriteVariables, and pass your input column into the script.  Then in the script create a private variable to keep your value, capture it in the "ProcessInputRow" method and pass it on to the variable in the PostExecute method.

The following snippet demonstrates the principle, assuming that the variable is called ValTest and the input column is Val and the data type is integer.
public class ScriptMain : UserComponent
    private int val;

    public override void PreExecute()

    public override void PostExecute()
        Variables.ValTest = val;

    public override void Input0_ProcessInputRow(Input0Buffer Row)
        val = Row.Val;


Open in new window

Alpesh PatelAssistant ConsultantCommented:
Data Source is Excel and Put REcordSet as Destination and then Get the value in Object. In Script task set value in Variable.
thomaszhwangAuthor Commented:
Thanks everyone.

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now