October 2, 2009 at 2:10 pm
I am trying to run a stored procedure from a sql task that takes a package variable as an input parameter. Eventually want to pass the value to the package from a scheduled job via sql agent, but I can not get past first base. Any help would be appreciated!!!
The details are as follows...
I get the following execution results ...
Progress: Executing query "exec RunCampaignRequest ?". - 100 percent complete
Task Execute SQL Task failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Error: The type of the value being assigned to variable "Package::CampaignName" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Trying to pass variable Package::CampaignName to a sql task to run sql statement
exec runcampaignrequest ?
These are the SSIS Package details ...
-----Defined variable-----
Name - CampaignName
Scope - TestolaPackage
DataType - string
Value - test
NameSpace - Package
-----Sql task-----
[General tab]
Connection type - ole db
Connection - dbase.xyz
SqlSourceType - direct input
SqlStatement - exec runcampaignrequest ?
BypassPrepare - true
[paramater mapping tab]
variable name - Package::CampaignName
Direction - input
Data type - nvarchar
Parameter Name - 0
Parameter Size - 50
[Result set tab]
n/a
[Expressions tab]
n/a
[properties]
Delay validation - true
Forcedexecutationvalue - blank
ForcedExecutationValueType - string
ForceExecutionValue - true
BypassPrepare - true
ExeValueVariable - Package::CampaignName
ForceExecutionResult - none
IsStoredProcedure - true
ResultSetYype - ResultSetType_None
SqlStateentSource - exec runcampaignrequest ?
SqlStatementSourceType - DirectInput
Thanks
October 2, 2009 at 2:38 pm
reflorio (10/2/2009)
I am trying to run a stored procedure from a sql task that takes a package variable as an input parameter. Eventually want to pass the value to the package from a scheduled job via sql agent, but I can not get past first base. Any help would be appreciated!!!The details are as follows...
I get the following execution results ...
Progress: Executing query "exec RunCampaignRequest ?". - 100 percent complete
Task Execute SQL Task failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Error: The type of the value being assigned to variable "Package::CampaignName" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Trying to pass variable Package::CampaignName to a sql task to run sql statement
exec runcampaignrequest ?
These are the SSIS Package details ...
-----Defined variable-----
Name - CampaignName
Scope - TestolaPackage
DataType - string
Value - test
NameSpace - Package
-----Sql task-----
[General tab]
Connection type - ole db
Connection - dbase.xyz
SqlSourceType - direct input
SqlStatement - exec runcampaignrequest ?
BypassPrepare - true
[paramater mapping tab]
variable name - Package::CampaignName
Direction - input
Data type - nvarchar
Parameter Name - 0
Parameter Size - 50
[Result set tab]
n/a
[Expressions tab]
n/a
[properties]
Delay validation - true
Forcedexecutationvalue - blank
ForcedExecutationValueType - string
ForceExecutionValue - true
BypassPrepare - true
ExeValueVariable - Package::CampaignName
ForceExecutionResult - none
IsStoredProcedure - true
ResultSetYype - ResultSetType_None
SqlStateentSource - exec runcampaignrequest ?
SqlStatementSourceType - DirectInput
Thanks
Try :
exec runcampaignrequest @Param1
Under parameter mapping:
Parameter Name - @Param1
October 5, 2009 at 6:43 am
Thanks for the suggestion. I gave it a try and ended up with the following error message.
[Execute SQL Task] Error: Executing the query "exec RunCampaignRequest @Param1" failed with the following error:
"Must declare the scalar variable "@Param1".". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
rom the current variable type. Variables may not change type during execution.
Variable types are strict, except for variables of type Object.
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Error: The type of the value being assigned to variable "Package::CampaignName" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
October 6, 2009 at 6:17 am
The error message seems to suggest a datatype conversion problem...
What datatype does the stored procedure parameter expect and what is the datatype of the variable you're passing into it?
HTH
Kindest Regards,
Frank Bazan
October 6, 2009 at 11:44 am
the parm in the package is defined as a string, and a varchar() in the stored procedure... thanks
October 13, 2009 at 12:51 pm
Elliott's answer below appeared to be correct.
October 13, 2009 at 2:55 pm
I think your problem is here:
[paramater mapping tab]
variable name - Package::CampaignName
Direction - input
Data type - nvarchar
Parameter Name - 0
Parameter Size - 50
you are converting from a string into nvarchar and the sproc is expecting varchar, SSIS doesn't want to do this, change the input type to varchar..
And as a side note, Frank, you were the closest..
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply