How to set package-scope variables with output from Execute SQL Task

Posted on 2011-10-21
Last Modified: 2013-11-10
Note: package does not fail, but does not put variable values in send email

query/SP is
Select Col_A, Col_B, Col_C
from myTable where PackageID = 1

OLEDB connection (Cannot find a way for ODBC to succeed)

Variable name: User::vCol_A, User::vCol_B, User::vCol_C
Direction: output, output, output
DataType: varchar, varchar, varchar
Parameter Name: 0,1,2
Parameter Size: 50, 50, 50

Result Name: Col_A, Col_B, Col_C
Variable name: User::vCol_A, User::vCol_B, User::vCol_C

"vCol_A = " + (DT_WSTR, 50)@[User::vCol_A]
+ "\n" + "vCol_B = " +  (DT_WSTR, 50)@[User::vCol_B]
+ "\n" + "vCol_C = " + (DT_WSTR, 50)@[User::vCol_C]

Other variables that have SET value in Properties DO pass value to email.


Question by:dschlee
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Just Select the Control for scope of variable and click add new variable. and see the second column the parent of that variable it is the scope of variable.

    LVL 21

    Expert Comment

    To change the variable scope without deleting it and re-creating it again, download the BIDS HELPER plugin.

    Your expression looks correct, so it might be the scope as you requested, but normally SSIS gives an error when it tries to assign the value and cannot find the variable!!!

    Try to change the variable's scope to the package and add a checkpoint and see if it gets a value or not at run time.

    here's how...

    Accepted Solution

    Thank you for both responses.  Turns out the issue was that i had the variables added to Parameter Mapping AND Result Set.  This combination apparently set the value of the variables to NULL or empty. Even when set the variable VALUE property to a hardcoded value, the result came back NULL/empty.

    When I removed variables from Parameter Mapping and kept the variables in Result Set, the variables were set with the values from the SP.  The scope was not the issue.  Thanks.
    LVL 21

    Expert Comment

    Great....Lesson learned hard :)

    Author Comment

    I'm considering hiring a SSIS Wizard for a one-hour daily sessions.  Tutor schedule would need to be within 6-8p Pacific, or 5-8a Pacific.  Specific goal: build a package that
    implements package-level variables,
    dynamically name Excel Source and Worksheet,
    convert Excel worksheet to tab-delimited file,
    create new SQL tables with column names matching the Excel worksheet columns,
    insert records (data from package variables) into SQL tables,
    create a .XML file containing static and dynamic content, then execute
    create a BAT file containing static and dynamic content, then execute
    execute EXE file,
    copy files from one directory to another.

    If you're an available and willing Wizard, please contact me at
    LVL 21

    Expert Comment

    I just replied to your E-mail.

    Author Closing Comment

    When I hardcoded the VALUE in the variable property, and the output was NULL/empty, the cause of the 'no value' had to be set at either Parameter Mapping or Results Set. Removing the variable from PM removed the problem.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now