SSIS The value type ComObject can only be converted to variables of type Object

user_2470603 0
Viewed 4736

I have the below SQL statement that is being called via a SSIS Execute:

  SELECT DISTINCT [ICDM_Log_Paths] = STUFF((SELECT ' ' + [ICDM_Log_Paths]+ '|' 
    FROM dbo.[tblTmpICDM_Log_Paths] 
    FOR XML PATH('')), 1, 1, '')
FROM dbo.[tblTmpICDM_Log_Paths] AS t

However, when I assign to a Result set and a String variable, I receive the error:

[Execute SQL Task] Error: The value type (__ComObject) can only be converted to variables of type Object. [Execute SQL Task] Error: An error occurred while assigning a value to variable "FileName":

"The type of the value (DBNull) being assigned to variable "User::FileName" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

How do I CAST the select as NVARCHAR when using the STUFF?

I have tried the below but it is not working:

  SELECT DISTINCT [ICDM_Log_Paths] = STUFF((SELECT ' ' + CAST([ICDM_Log_Paths] AS nvarchar(500))+ '|' 
    FROM dbo.[tblTmpICDM_Log_Paths] 
    FOR XML PATH('')), 1, 1, '')
FROM dbo.[tblTmpICDM_Log_Paths] AS t
ssis casting tsql sql-server sql
2 Answers

I fixed\created a workaround if it helps anyone.

1 . Created a view based on the below:

SELECT DISTINCT [ICDM_Log_Paths] = STUFF((SELECT ' ' + [ICDM_Log_Paths]+ '|' 
    FROM dbo.[tblTmpICDM_Log_Paths] 
    FOR XML PATH('')), 1, 1, '')
FROM dbo.[tblTmpICDM_Log_Paths] AS t

  1. In SSIS Execute SQL Task I then performed:

    SELECT CAST(ICDM_Log_Paths AS nvarchar(500)) AS Attachments FROM dbo.vw_ICDM_Filepaths

It works for me.

user_24706030

The result set is expecting a variable with an object data type if you're returning multiple rows. If multiple rows are returned, set the Result Type to "Full result set" and map an object variable in the Result Set pane and assign the variable as the 0 Result Name. If you will only return 1 row, you can use the "Single row" result set and assign the variable as the 0 Result Name. Another option is to set the Result Set to None and assign the variable in the Parameter Mapping Pane, with a direction of Output, 0 as the Parameter Name (for the first parameter), and the appropriate date type. For the second option, your syntax would be as follows with a ? as the parameter placeholder (assuming this is an OLE DB connection).

 SELECT DISTINCT ? = STUFF((SELECT ' ' + CAST([ICDM_Log_Paths] AS nvarchar(500))+ '|' 
    FROM dbo.[tblTmpICDM_Log_Paths] 
    FOR XML PATH('')), 1, 1, '')
FROM dbo.[tblTmpICDM_Log_Paths] AS t
user_41228720
Related