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

Posted on 2011-05-09
Last Modified: 2012-05-11
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.
Question by:thomaszhwang

    Author Comment

    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.
    LVL 57

    Accepted Solution

    >> 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
    LVL 10

    Assisted Solution

    This might help

    LVL 37

    Assisted Solution

    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

    LVL 21

    Assisted Solution

    by:Alpesh Patel
    Data Source is Excel and Put REcordSet as Destination and then Get the value in Object. In Script task set value in Variable.

    Author Closing Comment

    Thanks everyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now