April 2, 2009 at 2:06 am
How can I store data from a SQL Table as Package Variable(s)?
i.e.
Table: tblSendMail
column: Account SMTPServer
April 2, 2009 at 3:34 am
Using Execute SQL Task and mapping the resultset to Package Variable(s)
--Ramesh
April 2, 2009 at 4:14 am
Sorry I am new to SSIS so please can you elaborate a bit more….?
April 2, 2009 at 4:42 am
My SQL Table contains:
Parameters
SMTPConnection
From
Value
internalsmtp.company.COM
I want to call these values for my SendMail Task
April 2, 2009 at 4:45 am
Sure...
1. Create 1 OLEDB SQL Connection
2. Drag 1 Execute SQL Task to Control Flow
3. In Execute SQL Task General Properties, Set Resultset = "Single row", Select the Connection, Set SQLSourceType = "Direct Input" and SQLStatement = "SELECT AccountSMTPServer FROM tblSendMail"
4. In Execute SQL Task Resultset Properties, Set Result Name = "0" and select the package variable to which you want the column data.
--Ramesh
April 2, 2009 at 5:01 am
Thanks, Point number 4
4. In Execute SQL Task Resultset Properties, Set Result Name = "0" and select the package variable to which you want the column data.
I have select "Result Set" in SQL Task Editor and set the following vaules:
ResultName: 0
VariableName: User::Parameter, ValueType: string
Getting the error:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the
query "select Parameter from dbo.SMTPSettings" failed with the following
error: "Parameter name is unrecognized.". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at Package: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded,
but the number of errors raised (1) reached the maximum allowed (1);
resulting in failure. This occurs when the number of errors reaches the number
specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
April 2, 2009 at 5:09 am
Ok Sorry Ramesh, I fixed it, I had selected an incorrect setting.
Ok so when I run this now, it completes successfully.
Where does the variable get stored? Is it only stored during execution?
April 2, 2009 at 5:15 am
How can I use this variable within my SendMail task?
i.e. the Variable I have captured is the SMTP Server value
April 2, 2009 at 5:43 am
Go to "Send Mail Task Editor" > Expressions > Select Property "SmtpConnection" & in Expresssions type
"SmtpServer=" + @[User::Parameter]
--Ramesh
April 2, 2009 at 5:55 am
Thanks for the setpby set, im getting the following error:
Expression cannot be evaluated.
TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
The variable "User::Parameter" was not found in the Variables collection. The variable might not exist in the correct scope.
Attempt to parse the expression ""SmtpServer=" + @[User::Parameter] " failed and returned error code 0xC00470A6.
The expression cannot be parsed. It might contain invalid elements or it might not be well-formed.
There may also be an out-of-memory error.
April 2, 2009 at 6:09 am
First make sure that the variable used in the expression is what you have declared in the package variables and also the scope in which it was declared.
When you are in Send Mail Task Expression Editor, click on the button next to the Expression. From that, click on Evaluate Expression. What did you get in the Evaluated Value area?
--Ramesh
April 2, 2009 at 6:17 am
Ok thanks,
Wehn I click ont he "Evaluate Exp" button I get:
SmtpServer=
Is this correct?
As when I connect the SQLTask to SendMail there is an error saying you are missing '@'....
April 2, 2009 at 7:19 am
Have you created the package variables you need? They need to be created/declared before they can be used.
Right-click the control flow and select Variables and follow it through from there. Ensure that the scope of the variables which you create is the package (and not just a single task).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 2, 2009 at 7:43 am
Well I think I have created the varriable correclty.
The following are the settings I have:
SQL Task Editor:
General
SQLSorceType: DirectInput
SQLStatement: Select vaule from SMTPSettings where id=1
(this will bring back the SMTP Server)
ResultSet
ResultName: 0
VariableName-
Container: Package
Name: SMTPConnection
NameSpace: User
ValueType: String
When I execute the SQL Task aloan it works fine.
Now when I link it to a SendMail task with the following settings, it fails:
Exspression: SMTPConnection
"SmtpServer=" +@[User::SMTPConnection]
(Evaluate Exp comes back as: SmtpServer=)
SSIS Package Fails with error
Nonfatal errors occured while saving the package:
The connection "SmtpServer=" not found
April 2, 2009 at 8:07 am
Do I need to add anything into Parameter Mapping within SQL Task Editor?
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply