pipeline fails for stored Procedure called in Copy Activity - Azure data factory V2
We've a SQL server stored procedure which returns the incremental records. If there are no changes to the table, then nothing is returned. Stored procedure does what is expected.
We're invoking the above said stored procedure via Copy activity in Azure data factory. It works fine for all the cases except when nothing (empty) is returned.
We are looking for an option, where Nothing(Empty) is returned from stored procedure, pipeline should skip and proceed further and also mark the whole pipeline successful rather failed.
Thanks
azure azure-data-factory asked Nov 21 '18 at 11:59
ShankarShankar
7829
add a comment |
We've a SQL server stored procedure which returns the incremental records. If there are no changes to the table, then nothing is returned. Stored procedure does what is expected.
We're invoking the above said stored procedure via Copy activity in Azure data factory. It works fine for all the cases except when nothing (empty) is returned.
We are looking for an option, where Nothing(Empty) is returned from stored procedure, pipeline should skip and proceed further and also mark the whole pipeline successful rather failed.
Thanks
azure azure-data-factory asked Nov 21 '18 at 11:59
ShankarShankar
7829
I would suggest first to use Lookup activity to return an output of SP, then with IF activity check if that output is empty if its not empty then perform Copy Activity.
– DraganB
Nov 21 '18 at 13:41
I can't use IFCondition activity, since I already used it once. I believe, IFCondition activity can be used only once in a pipeline:)
– Shankar
Nov 21 '18 at 15:16
You can't do another IF as an inner activity, but you can check in your flow output of first IF, and check if the output of first IF is true or false (what you need) and there you can go in inner activity and to SP. :)
– DraganB
Nov 21 '18 at 15:35
add a comment |
We've a SQL server stored procedure which returns the incremental records. If there are no changes to the table, then nothing is returned. Stored procedure does what is expected.
We're invoking the above said stored procedure via Copy activity in Azure data factory. It works fine for all the cases except when nothing (empty) is returned.
We are looking for an option, where Nothing(Empty) is returned from stored procedure, pipeline should skip and proceed further and also mark the whole pipeline successful rather failed.
Thanks
azure azure-data-factory asked Nov 21 '18 at 11:59
ShankarShankar
7829
We've a SQL server stored procedure which returns the incremental records. If there are no changes to the table, then nothing is returned. Stored procedure does what is expected.
We're invoking the above said stored procedure via Copy activity in Azure data factory. It works fine for all the cases except when nothing (empty) is returned.
We are looking for an option, where Nothing(Empty) is returned from stored procedure, pipeline should skip and proceed further and also mark the whole pipeline successful rather failed.
Thanks
azure azure-data-factory azure azure-data-factory asked Nov 21 '18 at 11:59
ShankarShankar
7829
asked Nov 21 '18 at 11:59
ShankarShankar
7829
asked Nov 21 '18 at 11:59
ShankarShankar
7829
asked Nov 21 '18 at 11:59
ShankarShankar
7829
asked Nov 21 '18 at 11:59
ShankarShankar
7829
7829
I would suggest first to use Lookup activity to return an output of SP, then with IF activity check if that output is empty if its not empty then perform Copy Activity.
– DraganB
Nov 21 '18 at 13:41
I can't use IFCondition activity, since I already used it once. I believe, IFCondition activity can be used only once in a pipeline:)
– Shankar
Nov 21 '18 at 15:16
You can't do another IF as an inner activity, but you can check in your flow output of first IF, and check if the output of first IF is true or false (what you need) and there you can go in inner activity and to SP. :)
– DraganB
Nov 21 '18 at 15:35
add a comment |
I would suggest first to use Lookup activity to return an output of SP, then with IF activity check if that output is empty if its not empty then perform Copy Activity.
– DraganB
Nov 21 '18 at 13:41
I can't use IFCondition activity, since I already used it once. I believe, IFCondition activity can be used only once in a pipeline:)
– Shankar
Nov 21 '18 at 15:16
You can't do another IF as an inner activity, but you can check in your flow output of first IF, and check if the output of first IF is true or false (what you need) and there you can go in inner activity and to SP. :)
– DraganB
Nov 21 '18 at 15:35
I would suggest first to use Lookup activity to return an output of SP, then with IF activity check if that output is empty if its not empty then perform Copy Activity.
– DraganB
Nov 21 '18 at 13:41
I would suggest first to use Lookup activity to return an output of SP, then with IF activity check if that output is empty if its not empty then perform Copy Activity.
– DraganB
Nov 21 '18 at 13:41
I can't use IFCondition activity, since I already used it once. I believe, IFCondition activity can be used only once in a pipeline:)
– Shankar
Nov 21 '18 at 15:16
I can't use IFCondition activity, since I already used it once. I believe, IFCondition activity can be used only once in a pipeline:)
– Shankar
Nov 21 '18 at 15:16
You can't do another IF as an inner activity, but you can check in your flow output of first IF, and check if the output of first IF is true or false (what you need) and there you can go in inner activity and to SP. :)
– DraganB
Nov 21 '18 at 15:35
You can't do another IF as an inner activity, but you can check in your flow output of first IF, and check if the output of first IF is true or false (what you need) and there you can go in inner activity and to SP. :)
– DraganB
Nov 21 '18 at 15:35
add a comment |
3 Answers
3
active
oldest
votes
Your stored procedure needs to end by a SELECT, so it returns something - including an empty set if there is no rows to return.
However, to skip the pipeline if there is no row, DraganB's last answer is pretty relevant, I had to do that a couple of time on my current project.
answered Nov 21 '18 at 18:27
juliejulie
12
I agree. But in my case, I need to create an empty file when there's no output returned from Stored Procedure. Empty file just with header record in it(first row as column names). I was looking for something like - link
– Shankar
Nov 23 '18 at 17:45
add a comment |
As @DraganB said in the comment, activities could run in the flow,so you could do stored procedure activity --> if activity --> copy activity.If the output of sp activity is empty,then don't run the copy activity and end the pipeline.
Another idea, maybe you could learn about azure function external table trigger. You could add a status column in your table such as needToBeCopied, every insert or update operation will change the column. Then filter the data which need to be copied while running the copy activity.
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
add a comment |
It got resolved. The real issue was Copy activity wasn't returning the correct error message. There was an issue with access control.
Grant VIEW CHANGE TRACKING permission on a table to a user:
[sql]
GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to username
[/sql]
Incremental loading approach is through enabling Change Tracking on SQL Server Database and on required tables .
Azure data factory should have logged error as 'Insufficient permissions on so and so table'. Instead it failed the whole pipeline with error message as 'Stored procedure might be invalid or stored procedure doesn't return any output'.
Anyway, we assigned the right permissions and issue got resolved. Now, it creates an Empty file just with header record in it when there's no output returned from Stored Procedure likewise in - Data Factory Avoiding creation of empty files
answered Nov 27 '18 at 15:31
ShankarShankar
7829
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%2f53411576%2fpipeline-fails-for-stored-procedure-called-in-copy-activity-azure-data-factory%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your stored procedure needs to end by a SELECT, so it returns something - including an empty set if there is no rows to return.
However, to skip the pipeline if there is no row, DraganB's last answer is pretty relevant, I had to do that a couple of time on my current project.
answered Nov 21 '18 at 18:27
juliejulie
12
I agree. But in my case, I need to create an empty file when there's no output returned from Stored Procedure. Empty file just with header record in it(first row as column names). I was looking for something like - link
– Shankar
Nov 23 '18 at 17:45
add a comment |
Your stored procedure needs to end by a SELECT, so it returns something - including an empty set if there is no rows to return.
However, to skip the pipeline if there is no row, DraganB's last answer is pretty relevant, I had to do that a couple of time on my current project.
answered Nov 21 '18 at 18:27
juliejulie
12
I agree. But in my case, I need to create an empty file when there's no output returned from Stored Procedure. Empty file just with header record in it(first row as column names). I was looking for something like - link
– Shankar
Nov 23 '18 at 17:45
add a comment |
Your stored procedure needs to end by a SELECT, so it returns something - including an empty set if there is no rows to return.
However, to skip the pipeline if there is no row, DraganB's last answer is pretty relevant, I had to do that a couple of time on my current project.
answered Nov 21 '18 at 18:27
juliejulie
12
Your stored procedure needs to end by a SELECT, so it returns something - including an empty set if there is no rows to return.
However, to skip the pipeline if there is no row, DraganB's last answer is pretty relevant, I had to do that a couple of time on my current project.
answered Nov 21 '18 at 18:27
juliejulie
12
answered Nov 21 '18 at 18:27
juliejulie
12
answered Nov 21 '18 at 18:27
juliejulie
12
answered Nov 21 '18 at 18:27
juliejulie
12
12
I agree. But in my case, I need to create an empty file when there's no output returned from Stored Procedure. Empty file just with header record in it(first row as column names). I was looking for something like - link
– Shankar
Nov 23 '18 at 17:45
add a comment |
I agree. But in my case, I need to create an empty file when there's no output returned from Stored Procedure. Empty file just with header record in it(first row as column names). I was looking for something like - link
– Shankar
Nov 23 '18 at 17:45
I agree. But in my case, I need to create an empty file when there's no output returned from Stored Procedure. Empty file just with header record in it(first row as column names). I was looking for something like - link
– Shankar
Nov 23 '18 at 17:45
I agree. But in my case, I need to create an empty file when there's no output returned from Stored Procedure. Empty file just with header record in it(first row as column names). I was looking for something like - link
– Shankar
Nov 23 '18 at 17:45
add a comment |
As @DraganB said in the comment, activities could run in the flow,so you could do stored procedure activity --> if activity --> copy activity.If the output of sp activity is empty,then don't run the copy activity and end the pipeline.
Another idea, maybe you could learn about azure function external table trigger. You could add a status column in your table such as needToBeCopied, every insert or update operation will change the column. Then filter the data which need to be copied while running the copy activity.
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
add a comment |
As @DraganB said in the comment, activities could run in the flow,so you could do stored procedure activity --> if activity --> copy activity.If the output of sp activity is empty,then don't run the copy activity and end the pipeline.
Another idea, maybe you could learn about azure function external table trigger. You could add a status column in your table such as needToBeCopied, every insert or update operation will change the column. Then filter the data which need to be copied while running the copy activity.
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
add a comment |
As @DraganB said in the comment, activities could run in the flow,so you could do stored procedure activity --> if activity --> copy activity.If the output of sp activity is empty,then don't run the copy activity and end the pipeline.
Another idea, maybe you could learn about azure function external table trigger. You could add a status column in your table such as needToBeCopied, every insert or update operation will change the column. Then filter the data which need to be copied while running the copy activity.
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
As @DraganB said in the comment, activities could run in the flow,so you could do stored procedure activity --> if activity --> copy activity.If the output of sp activity is empty,then don't run the copy activity and end the pipeline.
Another idea, maybe you could learn about azure function external table trigger. You could add a status column in your table such as needToBeCopied, every insert or update operation will change the column. Then filter the data which need to be copied while running the copy activity.
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
answered Nov 22 '18 at 2:51
Jay GongJay Gong
9,4731614
9,4731614
add a comment |
add a comment |
It got resolved. The real issue was Copy activity wasn't returning the correct error message. There was an issue with access control.
Grant VIEW CHANGE TRACKING permission on a table to a user:
[sql]
GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to username
[/sql]
Incremental loading approach is through enabling Change Tracking on SQL Server Database and on required tables .
Azure data factory should have logged error as 'Insufficient permissions on so and so table'. Instead it failed the whole pipeline with error message as 'Stored procedure might be invalid or stored procedure doesn't return any output'.
Anyway, we assigned the right permissions and issue got resolved. Now, it creates an Empty file just with header record in it when there's no output returned from Stored Procedure likewise in - Data Factory Avoiding creation of empty files
answered Nov 27 '18 at 15:31
ShankarShankar
7829
add a comment |
It got resolved. The real issue was Copy activity wasn't returning the correct error message. There was an issue with access control.
Grant VIEW CHANGE TRACKING permission on a table to a user:
[sql]
GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to username
[/sql]
Incremental loading approach is through enabling Change Tracking on SQL Server Database and on required tables .
Azure data factory should have logged error as 'Insufficient permissions on so and so table'. Instead it failed the whole pipeline with error message as 'Stored procedure might be invalid or stored procedure doesn't return any output'.
Anyway, we assigned the right permissions and issue got resolved. Now, it creates an Empty file just with header record in it when there's no output returned from Stored Procedure likewise in - Data Factory Avoiding creation of empty files
answered Nov 27 '18 at 15:31
ShankarShankar
7829
add a comment |
It got resolved. The real issue was Copy activity wasn't returning the correct error message. There was an issue with access control.
Grant VIEW CHANGE TRACKING permission on a table to a user:
[sql]
GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to username
[/sql]
Incremental loading approach is through enabling Change Tracking on SQL Server Database and on required tables .
Azure data factory should have logged error as 'Insufficient permissions on so and so table'. Instead it failed the whole pipeline with error message as 'Stored procedure might be invalid or stored procedure doesn't return any output'.
Anyway, we assigned the right permissions and issue got resolved. Now, it creates an Empty file just with header record in it when there's no output returned from Stored Procedure likewise in - Data Factory Avoiding creation of empty files
answered Nov 27 '18 at 15:31
ShankarShankar
7829
It got resolved. The real issue was Copy activity wasn't returning the correct error message. There was an issue with access control.
Grant VIEW CHANGE TRACKING permission on a table to a user:
[sql]
GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to username
[/sql]
Incremental loading approach is through enabling Change Tracking on SQL Server Database and on required tables .
Azure data factory should have logged error as 'Insufficient permissions on so and so table'. Instead it failed the whole pipeline with error message as 'Stored procedure might be invalid or stored procedure doesn't return any output'.
Anyway, we assigned the right permissions and issue got resolved. Now, it creates an Empty file just with header record in it when there's no output returned from Stored Procedure likewise in - Data Factory Avoiding creation of empty files
answered Nov 27 '18 at 15:31
ShankarShankar
7829
edited Dec 8 '18 at 18:52
answered Nov 27 '18 at 15:31
ShankarShankar
7829
answered Nov 27 '18 at 15:31
ShankarShankar
7829
answered Nov 27 '18 at 15:31
ShankarShankar
7829
7829
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%2f53411576%2fpipeline-fails-for-stored-procedure-called-in-copy-activity-azure-data-factory%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
I would suggest first to use Lookup activity to return an output of SP, then with IF activity check if that output is empty if its not empty then perform Copy Activity.
– DraganB
Nov 21 '18 at 13:41
I can't use IFCondition activity, since I already used it once. I believe, IFCondition activity can be used only once in a pipeline:)
– Shankar
Nov 21 '18 at 15:16
You can't do another IF as an inner activity, but you can check in your flow output of first IF, and check if the output of first IF is true or false (what you need) and there you can go in inner activity and to SP. :)
– DraganB
Nov 21 '18 at 15:35