January 25, 2018 at 5:15 am
Hi,
I have a question that I would appreciate your help on please.
The set-up is....
I have a Sequence Container (SC1) that contains an Execute SQL Task (EST1) to rename a table. If this renaming of a table fails I would the error path to be followed which uses a different Execute SQL Task (EST2) to rename the table to a different name. Then I would like the Parent failed which will trigger an email.
I see two options (FailPackageOnFailure or ForcedExecutionValue) that I could implement but cannot decide on which to use (or would work as intended). The following is my understanding of their implementation.
FailPackageOnFailure
If I set the EST1 'FailPackageOnFailure' property to True will the error path still be followed so EST2 will be executed, after which the parent SC1 would have a failed status and thus the email being triggered, or is it a case of when EST1 fails SC1 fails triggering an email, but no error path is followed thus EST2 not executing??
ForcedExecutionValue
If EST1 'FailPackageOnFailure' property to False but EST1 fails then the error path is followed so EST2 will be executed. Whether EST2 succeeds or fails, if I set EST2 'ForcedExecutionValue' to 1 then can I make it so the SC1 is failed and thus SC1 email is trggered??
I hope this makes sense. Please advise. Thanks.
January 25, 2018 at 6:05 am
If you set FailPackageOnFailure to True on EST1, I don't think you will get to EST2, as package execution will cease at the point of failure, which is EST1.
If you want processing to stop after EST2 then it would be better to do your "error handling" there.
You need to consider two outcomes here:
1) what do I want to do if EST2 succeeds
2) what do I want to do if EST2 fails
That being said, it sounds like the two may go hand-in-hand
If you want EST2 to return a "failure" after successful execution then set ForceExecutionResult to 'Failure' on EST2.
By the sounds of it, you want to 'fail the parent', which is where you will need to set FailParentOnFailure in EST2. This will fail the sequence container. But, if you want to fail the package completely, then you will need to set FailPackageOnFailure to true. Depending on which 'fail' option you choose (parent or package) will determine where you need to add your Send Mail Task
January 25, 2018 at 6:14 am
chocthree - Thursday, January 25, 2018 5:15 AMHi,
I have a question that I would appreciate your help on please.
The set-up is....
I have a Sequence Container (SC1) that contains an Execute SQL Task (EST1) to rename a table. If this renaming of a table fails I would the error path to be followed which uses a different Execute SQL Task (EST2) to rename the table to a different name. Then I would like the Parent failed which will trigger an email.I see two options (FailPackageOnFailure or ForcedExecutionValue) that I could implement but cannot decide on which to use (or would work as intended). The following is my understanding of their implementation.
FailPackageOnFailure
If I set the EST1 'FailPackageOnFailure' property to True will the error path still be followed so EST2 will be executed, after which the parent SC1 would have a failed status and thus the email being triggered, or is it a case of when EST1 fails SC1 fails triggering an email, but no error path is followed thus EST2 not executing??ForcedExecutionValue
If EST1 'FailPackageOnFailure' property to False but EST1 fails then the error path is followed so EST2 will be executed. Whether EST2 succeeds or fails, if I set EST2 'ForcedExecutionValue' to 1 then can I make it so the SC1 is failed and thus SC1 email is trggered??I hope this makes sense. Please advise. Thanks.
Is there any reason why you can't combine EST1 and EST2? Then you should be able to handle all cases with judicious use of TRY/CATCH and THROW.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 25, 2018 at 7:05 am
Thanks DimPerson and Phil Parkin for your responses.
Firstly, Phil - I'm not great with C# so do not know how to do that approach at present (I will look it up).
DimPerson - okay, I understand what you mean. So I will do the following:
Configure EST1 'FailParentOnResult' property to False. This will ensure the error path to EST2 will be followed. I want EST2 to complete but then indicate the step had failed which will trigger the email being sent. Therefore I must configure the EST2 'ForceExecutionResult' to failure (which will pretend the step had failed, even the step actually succeeded) and configure the EST2 'FailParentOnResult' to True (which will fail the parent Sequence Container ensuring the email is triggered).
If I am misunderstand then please do let me know.
Thanks.
EDIT: There isn't a 'Failure' value for ForcedExecutionValue or ForceExecutionValue. Am I missing something?
January 25, 2018 at 7:20 am
chocthree - Thursday, January 25, 2018 7:05 AMThanks DimPerson and Phil Parkin for your responses.Firstly, Phil - I'm not great with C# so do not know how to do that approach at present (I will look it up).
Thanks.
The solution I suggested requires T-SQL only, not C#. And no non-default 'Fail' or 'Force' settings on the SSIS objects.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 25, 2018 at 8:04 am
Hi Phil,
Does the below look correct if placed within an Execute SQL Task component? Is the THROW used to set the 'FailParentOnFailure' to True, if so how do I do this? Thanks.
BEGIN TRY
BEGIN TRANSACTION
IF OBJECT_ID('dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData]', 'U') IS NOT NULL
DROP TABLE dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData];
GO
EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2]', 'ce.rfi_yle_dm_v2_PreviousExecutionData'
EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2_CurrentExecutionData]', 'ce.rfi_yle_dm_v2'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
***how do you set 'FailParentOnParent' to True here to the Sequence Container email is triggered?***
END CATCH
I see in the Execute SQL Task there is the 'FailParentOnFailure' in the Expressions pane. So I'm thinking if I need to use the THROW command to give a variable a value (not sure if the variable needs to be a boolean of a string to contain 'FALSE') and have that variable assigned to the 'FailParentOnFailure' expression. In turn this will cause the Sequence Container to fail, should the EST transaction not be successful, and thus triggering the email. Am I correct in my thinking? Thanks.
January 25, 2018 at 9:55 am
which version of ssis/ssdt are you using?
In 2016 I have ForceExecutionResult for the sequence container; options are 'none', 'success', 'failure', 'completion'.
I'm unsure if this is present in older versions.
January 25, 2018 at 10:06 am
Ah I see. The environment I've been handed to use, which I do not have permissions to install software, is using VS2010 🙁 I have requested 2017 to be installed. Unless it gets installed then I will have to go with the Try, Catch, Throw method that I'm trying to understand how to do.
Was my approach for the ForceExecutionResult and FailParentOnFailure correct? Thanks.
January 25, 2018 at 10:14 am
chocthree - Thursday, January 25, 2018 8:04 AMHi Phil,Does the below look correct if placed within an Execute SQL Task component? Is the THROW used to set the 'FailParentOnFailure' to True, if so how do I do this? Thanks.
BEGIN TRY
BEGIN TRANSACTION
IF OBJECT_ID('dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData]', 'U') IS NOT NULL
DROP TABLE dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData];
GO
EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2]', 'ce.rfi_yle_dm_v2_PreviousExecutionData'
EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2_CurrentExecutionData]', 'ce.rfi_yle_dm_v2'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
***how do you set 'FailParentOnParent' to True here to the Sequence Container email is triggered?***
END CATCHI see in the Execute SQL Task there is the 'FailParentOnFailure' in the Expressions pane. So I'm thinking if I need to use the THROW command to give a variable a value (not sure if the variable needs to be a boolean of a string to contain 'FALSE') and have that variable assigned to the 'FailParentOnFailure' expression. In turn this will cause the Sequence Container to fail, should the EST transaction not be successful, and thus triggering the email. Am I correct in my thinking? Thanks.
I'm not going to write out all of the code for you, but I will give you an idea.
BEGIN TRY
--Perform rename 1;
END TRY
BEGIN CATCH
--Perform rename 2;
THROW;
END CATCH
Description
1) If rename 1 is successful, the code completes with success.
2) If rename 1 fails, control passes to the CATCH block and rename 2 is attempted. After rename 2 completes, the rename 1 error failure is thrown. This error will, by default, cause the Exec SQL task and all of its parents to fail. Connect the failure or error output of the exec SQL task to your e-mail task.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2018 at 7:12 am
Ideally when you have this parent child package configuration, each child package encapsulates a complete business process. This would include what to do if it fails. This could entail failing the package. But if there is some recovery or alternate method, it is probably better to have it in the same package rather than call an alternate package. The one exception I could see is if the called package handles failures from multiple packages.
January 26, 2018 at 7:18 am
RonKyle - Friday, January 26, 2018 7:12 AMIdeally when you have this parent child package configuration, each child package encapsulates a complete business process. This would include what to do if it fails. This could entail failing the package. But if there is some recovery or alternate method, it is probably better to have it in the same package rather than call an alternate package. The one exception I could see is if the called package handles failures from multiple packages.
There is no mention of parent/child packages here ... just parent objects within a single package, unless I'm misreading things.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2018 at 8:08 am
Thanks Phil for the starter.
I was playing around with it this morning, and I produced this:
"BEGIN TRY
BEGIN TRAN
IF OBJECT_ID('dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData]', 'U') IS NOT NULL
DROP TABLE dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData];
EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2]', 'ce.rfi_yle_dm_v2_PreviousExecutionData'
EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2_CurrentExecutionData]', 'ce.rfi_yle_dm_v2'
COMMIT TRAN
END TRY
BEGIN CATCH
THROW
ROLLBACK TRAN
END CATCH"
I was executing directly in SSMS, to test, before placing it in the SSIS EST component. When running it in SSMS it throw the default error message in message window but seemed to lock the tables. When I closed the query window it asked me do I want to commit via a dialog box, I had to click 'Yes' in which it removed some tables and no new (renaming of tables occurred). Clearly I am doing something wrong and need to understand it better. Needless to say it didn't make it to my SSIS package - shame as I liked this approach.
DimPerson - I managed to use the 'ForceExecutionResult' option through openng the packages in VS2017. However, to avoid 'double hopping' I had to deploy the SSIS packages from the server's VS2010 install (knowing the packages have a VS2016 'ForceExecutionResult' configuration). Hope it works.
Thank you for your help. If you have any feedback to my comments please respond.
January 26, 2018 at 10:18 am
chocthree - Friday, January 26, 2018 8:08 AMThanks Phil for the starter.I was playing around with it this morning, and I produced this:
"BEGIN TRY
BEGIN TRAN
IF OBJECT_ID('dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData]', 'U') IS NOT NULL
DROP TABLE dbo.[ce.rfi_yle_dm_v2_PreviousExecutionData];EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2]', 'ce.rfi_yle_dm_v2_PreviousExecutionData'
EXEC sp_rename 'dbo.[ce.rfi_yle_dm_v2_CurrentExecutionData]', 'ce.rfi_yle_dm_v2'
COMMIT TRAN
END TRY
BEGIN CATCH
THROW
ROLLBACK TRAN
END CATCH"I was executing directly in SSMS, to test, before placing it in the SSIS EST component. When running it in SSMS it throw the default error message in message window but seemed to lock the tables. When I closed the query window it asked me do I want to commit via a dialog box, I had to click 'Yes' in which it removed some tables and no new (renaming of tables occurred). Clearly I am doing something wrong and need to understand it better. Needless to say it didn't make it to my SSIS package - shame as I liked this approach.
DimPerson - I managed to use the 'ForceExecutionResult' option through openng the packages in VS2017. However, to avoid 'double hopping' I had to deploy the SSIS packages from the server's VS2010 install (knowing the packages have a VS2016 'ForceExecutionResult' configuration). Hope it works.
Thank you for your help. If you have any feedback to my comments please respond.
Notice how my example included two BEGIN TRY rows ... you need this.
Why are you using transactions here? Either the rename succeeds or fails, and you handle that accordingly. I suggest you remove the BEGIN/END TRAN.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply