How to ensure value assignment to variables with package scope stick even in error situation?

Question

How to ensure value assignment to variables with package scope stick even in error situation?

utekjunk

SSC Rookie

Points: 45
More actions
January 27, 2016 at 8:26 am

#323891

I am new to SSIS and may have a wrong concept on how variables behave in SSIS...
I am trying to pick up the package variable values set by a SQL task within the onError event handler
I set up two string variables, resultCustomText1 and resultCustomText2, with package scope and set their default value to "default".
Then, in a SQL task, I map them to two output parameters, presultCustomText1 and presultCustomText2.
The SQL statement is something like this...
USE [SQLCONVERSION]
SET @presultCustomText1= 'this is my 1st custom text'
declare @testvar int = 0
-- this will cause an error
select (1/@testvar) as value
SET @presultCustomText2= 'this is my 2nd custom text'
And then in the OnError event of the task, I write these two package variables out to a log table using another SQL task
I expect it to give me "This is my 1st custom text" and "default" but I get "default" and "default"
Is there a way to get what I want "This is my 1st custom text" and "default" using variables?
I could think of...
- I notice that if I add "GO" after the @presultCustomText1 assignment, I will get the it but using "GO" will have a lot of complication in
a more complex situation.
- I can also write the values to a place holder table somewhere but it is not as convenient...
Any suggestion is greatly appreciated. Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Have you verified (use breakpoints) that the variables' values are correctly being set by the ExecuteSQL task?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

utekjunk SSC Rookie Points: 45 More actions · Answer 2

Yes, I put break points in every event in the main SQL task. It shows that presultCustomText1 is still "default" at the onError() call.

I notice that, the event sequence is

OnPreExecute() -> OnQueryCancel() -> OnError();

However, if I put a "GO" after the assignment, it goes thru a different event sequence and the presultCustomText1 is "this is my 1st custom text" at the onError() call. It is like the assignment is not committed if error. Wonder if there is a way to get around that...

OnPreExecute() -> OnQueryCancel() -> OnProgress() -> OnQueryCancel() ->OnError()

USE [SQLCONVERSION]
SET @presultCustomText1= 'this is my 1st custom text'
GO
declare @testvar int = 0
-- this will cause an error
select (1/@testvar) as value
SET @presultCustomText2= 'this is my 2nd custom text'

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

utekjunk (1/27/2016)
Yes, I put break points in every event in the main SQL task. It shows that presultCustomText1 is still "default" at the onError() call.
I notice that, the event sequence is
OnPreExecute() -> OnQueryCancel() -> OnError();
However, if I put a "GO" after the assignment, it goes thru a different event sequence and the presultCustomText1 is "this is my 1st custom text" at the onError() call. It is like the assignment is not committed if error. Wonder if there is a way to get around that...
OnPreExecute() -> OnQueryCancel() -> OnProgress() -> OnQueryCancel() ->OnError()
USE [SQLCONVERSION]
SET @presultCustomText1= 'this is my 1st custom text'
GO
declare @testvar int = 0
-- this will cause an error
select (1/@testvar) as value
SET @presultCustomText2= 'this is my 2nd custom text'

OK, I think I misunderstood slightly what you were trying to do.

Are you putting a chunk of hard-coded SQL into an ExecuteSQL task? I do not recommend this, as it leads to code dispersion (my term for all the T-SQL hiding away in packages, reports etc, which tends to get forgotten during new any development or refactoring exercises). Better (IMO) to create a stored proc and to call that from your ExecuteSQL task, using OUTPUT params.

Handle any errors gracefully in your proc using TRY CATCH blocks appropriately.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

utekjunk SSC Rookie Points: 45 More actions · Answer 4

Thanks Phil for the suggestion. I will go back and review my approach.

I am new to SSIS and don't know what the best practice is...

that's something I have been wrestling in the last couple days whether to put something in SP or putting a big chunk of SQL in one task so that it is easier to manage or breaking them down into bit and piece...etc

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

utekjunk (1/27/2016)
Thanks Phil for the suggestion. I will go back and review my approach.
I am new to SSIS and don't know what the best practice is...
that's something I have been wrestling in the last couple days whether to put something in SP or putting a big chunk of SQL in one task so that it is easier to manage or breaking them down into bit and piece...etc

No good SQL developer will ever complain about you doing these things in a proc, in my experience 🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.