CODEWITHSUNDEEP
sqlsql-servert-sqlssiscasting
Michael
Michael

Reputation: 2657

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

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

Upvotes: 3

Views: 7365

Answers (3)

HydTechie
HydTechie

Reputation: 817

I strongly feel SSIS should be revamped to new age SQL writing, I was getting same error for using Varchar(MAX) :( The error resolved when I changed to Varchar(2000)

DECLARE @Comma varchar(10) = '';

DECLARE @Result varchar(MAX) = ''; <-------

SELECT @Result = @Result + @Comma + cast(ID as varchar), @Comma = ',' FROM TableName;

SELECT @Result as outputCsv

Upvotes: 0

Michael
Michael

Reputation: 2657

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.

Upvotes: 3

userfl89
userfl89

Reputation: 4810

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

Upvotes: 0

Related Questions