• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • 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
--DIRECT INPUT, SINGLE ROW:
Select Col_A, Col_B, Col_C
from myTable where PackageID = 1

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

PARAMETER MAPPING:
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 SET:
Result Name: Col_A, Col_B, Col_C
Variable name: User::vCol_A, User::vCol_B, User::vCol_C


SEND MAIL EXPRESSION:
"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.

Thanks.

0
dschlee
Asked:
dschlee
  • 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.


Untitled.png
0
 
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...
http://agilebi.com/jwelch/2009/11/29/ssis-101-viewing-variable-values-at-runtime/
0
 
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jason Yousef, MSSr. BI DeveloperCommented:
Great....Lesson learned hard :)
0
 
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.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
I just replied to your E-mail.
Thanks
Jason
0
 
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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