SSIS - How to check if the value in a column matches (is the same) another column
I am doing a validation process, trying to use conditional split to see if current manager = past manager in a salesperson database (look at image link below). If they don't match, I will later use it to signal the shift.
I tried doing [manager_2017] == [manager_2018] but it doesn't run. What am I doing wrong? Shouldn't it run since it's a True or False case?
I am using SSIS with Visual Studio 2017.
visual-studio ssis visual-studio-2017
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
add a comment |
I am doing a validation process, trying to use conditional split to see if current manager = past manager in a salesperson database (look at image link below). If they don't match, I will later use it to signal the shift.
I tried doing [manager_2017] == [manager_2018] but it doesn't run. What am I doing wrong? Shouldn't it run since it's a True or False case?
I am using SSIS with Visual Studio 2017.
visual-studio ssis visual-studio-2017
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
1
Could you click the Edit button and expand on "it doesn't run"
– billinkc
Nov 26 '18 at 13:32
I found the error, the sample had null values which the SSIS needs some specification on how to handle them
– Alessandra Midori
Nov 26 '18 at 15:01
add a comment |
I am doing a validation process, trying to use conditional split to see if current manager = past manager in a salesperson database (look at image link below). If they don't match, I will later use it to signal the shift.
I tried doing [manager_2017] == [manager_2018] but it doesn't run. What am I doing wrong? Shouldn't it run since it's a True or False case?
I am using SSIS with Visual Studio 2017.
visual-studio ssis visual-studio-2017
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
I am doing a validation process, trying to use conditional split to see if current manager = past manager in a salesperson database (look at image link below). If they don't match, I will later use it to signal the shift.
I tried doing [manager_2017] == [manager_2018] but it doesn't run. What am I doing wrong? Shouldn't it run since it's a True or False case?
I am using SSIS with Visual Studio 2017.
visual-studio ssis visual-studio-2017
visual-studio ssis visual-studio-2017
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
edited Nov 26 '18 at 13:31
billinkc
46.5k982122
46.5k982122
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
asked Nov 26 '18 at 12:05
Alessandra MidoriAlessandra Midori
313
313
1
Could you click the Edit button and expand on "it doesn't run"
– billinkc
Nov 26 '18 at 13:32
I found the error, the sample had null values which the SSIS needs some specification on how to handle them
– Alessandra Midori
Nov 26 '18 at 15:01
add a comment |
1
Could you click the Edit button and expand on "it doesn't run"
– billinkc
Nov 26 '18 at 13:32
I found the error, the sample had null values which the SSIS needs some specification on how to handle them
– Alessandra Midori
Nov 26 '18 at 15:01
1
1
Could you click the Edit button and expand on "it doesn't run"
– billinkc
Nov 26 '18 at 13:32
Could you click the Edit button and expand on "it doesn't run"
– billinkc
Nov 26 '18 at 13:32
I found the error, the sample had null values which the SSIS needs some specification on how to handle them
– Alessandra Midori
Nov 26 '18 at 15:01
I found the error, the sample had null values which the SSIS needs some specification on how to handle them
– Alessandra Midori
Nov 26 '18 at 15:01
add a comment |
2 Answers
2
active
oldest
votes
While awaiting clarification on the precise error, the supplied syntax is correct. [manager_2017] == [manager_2018] is a valid SSIS expression which evaluates to a boolean type (DT_BOOL).
I find it's handy to add a Derived Column before things like a Conditional Split and create explicit columns to hold my logical flags. It makes it easier to add a data viewer/data tap into the mix to ensure I've covered the cases.
IsSameManager `[manager_2017] == [manager_2018]`
Then in my Conditional Split, I'd have two output streams. Default and a new one "Retained Manager" I can then split on a single column IsSameManager
There are two things I can think of that might be causing an error, based on your sample data. The first is that column names are case sensitive in SSIS. Your spreadsheet has an uppercase for the first column "Salesperson_id" and lowercase for the manager columns. If they are represented as Manager_2017 then the expression would have to change to [Manager_2017]
The other possibility is data typing. If one of those columns was interpreted as a large data type (DT_NTEXT/DT_TEXT) then comparisons between it at a non-large type (DT_WSTR, DT_STR) won't be allowed.
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
add a comment |
I found the error, but thanks for the tips.
Basically the sample had null values so I had to specify how to deal with those before SSIS would run.
So the formula ended up as:
[manager_2017] == [manager_2018] && !ISNULL([manager_2017]) && !ISNULL([manager_2018])
Obviously the sample I am using is much bigger, I have multiple hierarchy levels to compare and I simplified it for the purpose of asking, so I ended up overlooking the null values.
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
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%2f53480745%2fssis-how-to-check-if-the-value-in-a-column-matches-is-the-same-another-colum%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
While awaiting clarification on the precise error, the supplied syntax is correct. [manager_2017] == [manager_2018] is a valid SSIS expression which evaluates to a boolean type (DT_BOOL).
I find it's handy to add a Derived Column before things like a Conditional Split and create explicit columns to hold my logical flags. It makes it easier to add a data viewer/data tap into the mix to ensure I've covered the cases.
IsSameManager `[manager_2017] == [manager_2018]`
Then in my Conditional Split, I'd have two output streams. Default and a new one "Retained Manager" I can then split on a single column IsSameManager
There are two things I can think of that might be causing an error, based on your sample data. The first is that column names are case sensitive in SSIS. Your spreadsheet has an uppercase for the first column "Salesperson_id" and lowercase for the manager columns. If they are represented as Manager_2017 then the expression would have to change to [Manager_2017]
The other possibility is data typing. If one of those columns was interpreted as a large data type (DT_NTEXT/DT_TEXT) then comparisons between it at a non-large type (DT_WSTR, DT_STR) won't be allowed.
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
add a comment |
While awaiting clarification on the precise error, the supplied syntax is correct. [manager_2017] == [manager_2018] is a valid SSIS expression which evaluates to a boolean type (DT_BOOL).
I find it's handy to add a Derived Column before things like a Conditional Split and create explicit columns to hold my logical flags. It makes it easier to add a data viewer/data tap into the mix to ensure I've covered the cases.
IsSameManager `[manager_2017] == [manager_2018]`
Then in my Conditional Split, I'd have two output streams. Default and a new one "Retained Manager" I can then split on a single column IsSameManager
There are two things I can think of that might be causing an error, based on your sample data. The first is that column names are case sensitive in SSIS. Your spreadsheet has an uppercase for the first column "Salesperson_id" and lowercase for the manager columns. If they are represented as Manager_2017 then the expression would have to change to [Manager_2017]
The other possibility is data typing. If one of those columns was interpreted as a large data type (DT_NTEXT/DT_TEXT) then comparisons between it at a non-large type (DT_WSTR, DT_STR) won't be allowed.
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
add a comment |
While awaiting clarification on the precise error, the supplied syntax is correct. [manager_2017] == [manager_2018] is a valid SSIS expression which evaluates to a boolean type (DT_BOOL).
I find it's handy to add a Derived Column before things like a Conditional Split and create explicit columns to hold my logical flags. It makes it easier to add a data viewer/data tap into the mix to ensure I've covered the cases.
IsSameManager `[manager_2017] == [manager_2018]`
Then in my Conditional Split, I'd have two output streams. Default and a new one "Retained Manager" I can then split on a single column IsSameManager
There are two things I can think of that might be causing an error, based on your sample data. The first is that column names are case sensitive in SSIS. Your spreadsheet has an uppercase for the first column "Salesperson_id" and lowercase for the manager columns. If they are represented as Manager_2017 then the expression would have to change to [Manager_2017]
The other possibility is data typing. If one of those columns was interpreted as a large data type (DT_NTEXT/DT_TEXT) then comparisons between it at a non-large type (DT_WSTR, DT_STR) won't be allowed.
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
While awaiting clarification on the precise error, the supplied syntax is correct. [manager_2017] == [manager_2018] is a valid SSIS expression which evaluates to a boolean type (DT_BOOL).
I find it's handy to add a Derived Column before things like a Conditional Split and create explicit columns to hold my logical flags. It makes it easier to add a data viewer/data tap into the mix to ensure I've covered the cases.
IsSameManager `[manager_2017] == [manager_2018]`
Then in my Conditional Split, I'd have two output streams. Default and a new one "Retained Manager" I can then split on a single column IsSameManager
There are two things I can think of that might be causing an error, based on your sample data. The first is that column names are case sensitive in SSIS. Your spreadsheet has an uppercase for the first column "Salesperson_id" and lowercase for the manager columns. If they are represented as Manager_2017 then the expression would have to change to [Manager_2017]
The other possibility is data typing. If one of those columns was interpreted as a large data type (DT_NTEXT/DT_TEXT) then comparisons between it at a non-large type (DT_WSTR, DT_STR) won't be allowed.
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
answered Nov 26 '18 at 14:27
billinkcbillinkc
46.5k982122
46.5k982122
add a comment |
add a comment |
I found the error, but thanks for the tips.
Basically the sample had null values so I had to specify how to deal with those before SSIS would run.
So the formula ended up as:
[manager_2017] == [manager_2018] && !ISNULL([manager_2017]) && !ISNULL([manager_2018])
Obviously the sample I am using is much bigger, I have multiple hierarchy levels to compare and I simplified it for the purpose of asking, so I ended up overlooking the null values.
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
add a comment |
I found the error, but thanks for the tips.
Basically the sample had null values so I had to specify how to deal with those before SSIS would run.
So the formula ended up as:
[manager_2017] == [manager_2018] && !ISNULL([manager_2017]) && !ISNULL([manager_2018])
Obviously the sample I am using is much bigger, I have multiple hierarchy levels to compare and I simplified it for the purpose of asking, so I ended up overlooking the null values.
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
add a comment |
I found the error, but thanks for the tips.
Basically the sample had null values so I had to specify how to deal with those before SSIS would run.
So the formula ended up as:
[manager_2017] == [manager_2018] && !ISNULL([manager_2017]) && !ISNULL([manager_2018])
Obviously the sample I am using is much bigger, I have multiple hierarchy levels to compare and I simplified it for the purpose of asking, so I ended up overlooking the null values.
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
I found the error, but thanks for the tips.
Basically the sample had null values so I had to specify how to deal with those before SSIS would run.
So the formula ended up as:
[manager_2017] == [manager_2018] && !ISNULL([manager_2017]) && !ISNULL([manager_2018])
Obviously the sample I am using is much bigger, I have multiple hierarchy levels to compare and I simplified it for the purpose of asking, so I ended up overlooking the null values.
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
edited Nov 26 '18 at 15:10
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
answered Nov 26 '18 at 15:04
Alessandra MidoriAlessandra Midori
313
313
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.
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%2f53480745%2fssis-how-to-check-if-the-value-in-a-column-matches-is-the-same-another-colum%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
1
Could you click the Edit button and expand on "it doesn't run"
– billinkc
Nov 26 '18 at 13:32
I found the error, the sample had null values which the SSIS needs some specification on how to handle them
– Alessandra Midori
Nov 26 '18 at 15:01