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
sql
sql-server tsql ssis casting asked Nov 14 '18 at 16:19
Michael
4461823
add a comment |
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
sql
sql-server tsql ssis casting asked Nov 14 '18 at 16:19
Michael
4461823
Are you assigning the string variable In the Result Set pane of the Execute SQL Task?
– userfl89
Nov 14 '18 at 16:36
Yes. Its strange, I paste the SQL into a view to test from there and its still telling me the column is nvarchar(max) - I think this is what is causing the problem. It doesn't like nvarchar(max)
– Michael
Nov 14 '18 at 16:45
add a comment |
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
sql
sql-server tsql ssis casting asked Nov 14 '18 at 16:19
Michael
4461823
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
sql
sql-server tsql ssis casting sql
sql-server tsql ssis casting asked Nov 14 '18 at 16:19
Michael
4461823
asked Nov 14 '18 at 16:19
Michael
4461823
edited Nov 14 '18 at 16:33
asked Nov 14 '18 at 16:19
Michael
4461823
asked Nov 14 '18 at 16:19
Michael
4461823
asked Nov 14 '18 at 16:19
Michael
4461823
4461823
Are you assigning the string variable In the Result Set pane of the Execute SQL Task?
– userfl89
Nov 14 '18 at 16:36
Yes. Its strange, I paste the SQL into a view to test from there and its still telling me the column is nvarchar(max) - I think this is what is causing the problem. It doesn't like nvarchar(max)
– Michael
Nov 14 '18 at 16:45
add a comment |
Are you assigning the string variable In the Result Set pane of the Execute SQL Task?
– userfl89
Nov 14 '18 at 16:36
Yes. Its strange, I paste the SQL into a view to test from there and its still telling me the column is nvarchar(max) - I think this is what is causing the problem. It doesn't like nvarchar(max)
– Michael
Nov 14 '18 at 16:45
Are you assigning the string variable In the Result Set pane of the Execute SQL Task?
– userfl89
Nov 14 '18 at 16:36
Are you assigning the string variable In the Result Set pane of the Execute SQL Task?
– userfl89
Nov 14 '18 at 16:36
Yes. Its strange, I paste the SQL into a view to test from there and its still telling me the column is nvarchar(max) - I think this is what is causing the problem. It doesn't like nvarchar(max)
– Michael
Nov 14 '18 at 16:45
Yes. Its strange, I paste the SQL into a view to test from there and its still telling me the column is nvarchar(max) - I think this is what is causing the problem. It doesn't like nvarchar(max)
– Michael
Nov 14 '18 at 16:45
add a comment |
2 Answers
2
active
oldest
votes
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
answered Nov 14 '18 at 16:57
userfl89
1,467159
add a comment |
I fixedcreated 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
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.
answered Nov 15 '18 at 11:37
Michael
4461823
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304602%2fssis-the-value-type-comobject-can-only-be-converted-to-variables-of-type-object%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 14 '18 at 16:57
userfl89
1,467159
add a comment |
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
answered Nov 14 '18 at 16:57
userfl89
1,467159
add a comment |
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
answered Nov 14 '18 at 16:57
userfl89
1,467159
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
answered Nov 14 '18 at 16:57
userfl89
1,467159
edited Nov 14 '18 at 17:06
answered Nov 14 '18 at 16:57
userfl89
1,467159
answered Nov 14 '18 at 16:57
userfl89
1,467159
answered Nov 14 '18 at 16:57
userfl89
1,467159
1,467159
add a comment |
add a comment |
I fixedcreated 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
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.
answered Nov 15 '18 at 11:37
Michael
4461823
add a comment |
I fixedcreated 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
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.
answered Nov 15 '18 at 11:37
Michael
4461823
add a comment |
I fixedcreated 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
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.
answered Nov 15 '18 at 11:37
Michael
4461823
I fixedcreated 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
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.
answered Nov 15 '18 at 11:37
Michael
4461823
answered Nov 15 '18 at 11:37
Michael
4461823
answered Nov 15 '18 at 11:37
Michael
4461823
answered Nov 15 '18 at 11:37
Michael
4461823
4461823
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304602%2fssis-the-value-type-comobject-can-only-be-converted-to-variables-of-type-object%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you assigning the string variable In the Result Set pane of the Execute SQL Task?
– userfl89
Nov 14 '18 at 16:36
Yes. Its strange, I paste the SQL into a view to test from there and its still telling me the column is nvarchar(max) - I think this is what is causing the problem. It doesn't like nvarchar(max)
– Michael
Nov 14 '18 at 16:45