[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

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
0
johnftamburo
Asked:
johnftamburo
  • 2
1 Solution
 
Reza RadCommented:
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
 
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 RadCommented:
Glad to help,
Regards,
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now