Link to home
Start Free TrialLog in
Avatar of johnftamburo
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),ISNULL(parameter_valueset,''))
FROM dbo.o_rpt_email_list_request_parameters
WHERE request_id = @request_id
AND parameter_name = 'Deployments';
SELECT @pSplit = CONVERT(VARCHAR(255),ISNULL(parameter_valueset,''))
FROM dbo.o_rpt_email_list_request_parameters
WHERE request_id = @request_id
AND parameter_name = 'Split';
SELECT @pTopDomain = CONVERT(VARCHAR(255),ISNULL(parameter_valueset,''))
FROM dbo.o_rpt_email_list_request_parameters
WHERE request_id = @request_id
AND parameter_name = 'TopDomain';
SELECT @pLinkURL = CONVERT(VARCHAR(255),ISNULL(parameter_valueset,''))
FROM dbo.o_rpt_email_list_request_parameters
WHERE request_id = @request_id
AND parameter_name = 'LinkURL';
SELECT @pLinkID = CONVERT(INT,ISNULL(parameter_valueset,'-1'))
FROM dbo.o_rpt_email_list_request_parameters
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
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of johnftamburo
johnftamburo

ASKER

OK Thanks!  You pointed me in the right direction.  Changed query to:

SELECT
      ISNULL((SELECT parameter_valueset
      FROM dbo.o_rpt_email_list_request_parameters
      WHERE request_id = @request_id
      AND parameter_name = 'Deployments'),'') AS pDeployments
      ,ISNULL((SELECT parameter_valueset
      FROM dbo.o_rpt_email_list_request_parameters
      WHERE request_id = @request_id
      AND parameter_name = 'Split'),'') AS pSplit
      ,ISNULL((SELECT parameter_valueset
      FROM dbo.o_rpt_email_list_request_parameters
      WHERE request_id = @request_id
      AND parameter_name = 'TopDomain'),'') AS pTopDomain
      ,ISNULL((SELECT parameter_valueset
      FROM dbo.o_rpt_email_list_request_parameters
      WHERE request_id = @request_id
      AND parameter_name = 'LinkURL'),'') AS pLinkURL
      ,CONVERT(INT,ISNULL((SELECT parameter_valueset
      FROM dbo.o_rpt_email_list_request_parameters
      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!
Glad to help,
Regards,