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
johnftamburoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
try to select each sql statement in one execute sql task,
but note that you can not set parameters directly in ssis, you must set result set for each of them, for example this query ,needs to set result set to pDeployments .

SELECT CONVERT(VARCHAR(255),ISNULL(parameter_valueset,''))
FROM dbo.o_rpt_email_list_request_parameters
WHERE request_id = @request_id


let me know if you need more details

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnftamburoAuthor Commented:
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!
0
Reza RadConsultant, TrainerCommented:
Glad to help,
Regards,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.