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

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

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.


  • 3
  • 3
1 Solution
Alpesh PatelAssistant ConsultantCommented:
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.

Jason Yousef, MSSr. BI  DeveloperCommented:
To change the variable scope without deleting it and re-creating it again, download the BIDS HELPER plugin.  http://bidshelper.codeplex.com/

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...
dschleeAuthor Commented:
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.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Jason Yousef, MSSr. BI  DeveloperCommented:
Great....Lesson learned hard :)
dschleeAuthor Commented:
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 danschlee@gmail.com.
Jason Yousef, MSSr. BI  DeveloperCommented:
I just replied to your E-mail.
dschleeAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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