johnftamburo
asked on
SSIS: Trying to fill variables with Execute SQL
this is the query in my SQL Task
SET NOCOUNT ON;
SELECT @pDeployments = CONVERT(VARCHAR(255),ISNUL L(paramete r_valueset ,''))
FROM dbo.o_rpt_email_list_reque st_paramet ers
WHERE request_id = @request_id
AND parameter_name = 'Deployments';
SELECT @pSplit = CONVERT(VARCHAR(255),ISNUL L(paramete r_valueset ,''))
FROM dbo.o_rpt_email_list_reque st_paramet ers
WHERE request_id = @request_id
AND parameter_name = 'Split';
SELECT @pTopDomain = CONVERT(VARCHAR(255),ISNUL L(paramete r_valueset ,''))
FROM dbo.o_rpt_email_list_reque st_paramet ers
WHERE request_id = @request_id
AND parameter_name = 'TopDomain';
SELECT @pLinkURL = CONVERT(VARCHAR(255),ISNUL L(paramete r_valueset ,''))
FROM dbo.o_rpt_email_list_reque st_paramet ers
WHERE request_id = @request_id
AND parameter_name = 'LinkURL';
SELECT @pLinkID = CONVERT(INT,ISNULL(paramet er_valuese t,'-1'))
FROM dbo.o_rpt_email_list_reque st_paramet ers
WHERE request_id = @request_id
AND parameter_name = 'LinkID';
SET NOCOUNT OFF;
@request ID is defined as an input parameter; the "@p..." parameters are all defined as output parameters. The data types are precisely correct and double checked.
When the step is tested, I receive:
"Error: 0xC001F009 at Obtain Parameter names and values: The type of the value being assigned to variable "User::pDeployments" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Obtain Parameter names and values, Execute SQL Task: Executing the query "SET NOCOUNT ON; SELECT @pDeployments = CONVERT(VAR..." failed with the following error: "The type of the value being assigned to variable "User::pDeployments" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
There is no result set and the step has result set set to None. I triple checked to ensure that the parameters are properly set up for output. I simply need to fill these five user variables and for the life of me do not see the error.
Thanks for your help.
John
SET NOCOUNT ON;
SELECT @pDeployments = CONVERT(VARCHAR(255),ISNUL
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'Deployments';
SELECT @pSplit = CONVERT(VARCHAR(255),ISNUL
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'Split';
SELECT @pTopDomain = CONVERT(VARCHAR(255),ISNUL
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'TopDomain';
SELECT @pLinkURL = CONVERT(VARCHAR(255),ISNUL
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'LinkURL';
SELECT @pLinkID = CONVERT(INT,ISNULL(paramet
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'LinkID';
SET NOCOUNT OFF;
@request ID is defined as an input parameter; the "@p..." parameters are all defined as output parameters. The data types are precisely correct and double checked.
When the step is tested, I receive:
"Error: 0xC001F009 at Obtain Parameter names and values: The type of the value being assigned to variable "User::pDeployments" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Obtain Parameter names and values, Execute SQL Task: Executing the query "SET NOCOUNT ON; SELECT @pDeployments = CONVERT(VAR..." failed with the following error: "The type of the value being assigned to variable "User::pDeployments" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
There is no result set and the step has result set set to None. I triple checked to ensure that the parameters are properly set up for output. I simply need to fill these five user variables and for the life of me do not see the error.
Thanks for your help.
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help,
Regards,
Regards,
ASKER
SELECT
ISNULL((SELECT parameter_valueset
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'Deployments'),'') AS pDeployments
,ISNULL((SELECT parameter_valueset
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'Split'),'') AS pSplit
,ISNULL((SELECT parameter_valueset
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'TopDomain'),'') AS pTopDomain
,ISNULL((SELECT parameter_valueset
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'LinkURL'),'') AS pLinkURL
,CONVERT(INT,ISNULL((SELEC
FROM dbo.o_rpt_email_list_reque
WHERE request_id = @request_id
AND parameter_name = 'LinkID'),-1)) AS pLinkID
;
and then mapped the columns of the single row to variables!
Awarding points now!