SSIS Script Task if Variable RecordCount >0 Fail Package

Question

Post reply

SSIS Script Task if Variable RecordCount >0 Fail Package

Welsh Corgi

SSC Guru

Points: 116520
More actions
December 21, 2016 at 1:05 pm

#332913

I need to create a VB Script Task if the Variable RecordCount >0 Fail Package
if not succeed.
I have done this a long time ago but I do not remember the syntax.
How do I accomplish this?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 11 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323547 More actions · Answer 1

Dts.Events.FireError is what you want.

is your account used by a team of users, and not an individual?

Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)

http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653

if(myVariable <> 0)

{

Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);

Dts.TaskResult = (int)ScriptResults.Failure;

}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}

Only myself runs it.

Yes I realize that you made the post but was a little unclear.

I was hoping that I could get the example in VB.

Thank you for you many post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}

I was looking for something simpler to start with.

If RecordCount > 0

Then fail the package

That is it.

I do not need Dts.TaskResult = (int)ScriptResults.Failure;

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323547 More actions · Answer 4

Welsh Corgi (12/23/2016)
Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
I was looking for something simpler to start with.
If RecordCount > 0
Then fail the package
That is it.
I do not need Dts.TaskResult = (int)ScriptResults.Failure;
Thank you.

actually you DO need Dts.TaskResult = (int)ScriptResults.Failure;

that is exactly how a script task reports a failure, which in turn marks the package as failed...when any task fails.

that is how it is done in code.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Welsh Corgi (12/23/2016)
Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
I was looking for something simpler to start with.
If RecordCount > 0
Then fail the package
That is it.
I do not need Dts.TaskResult = (int)ScriptResults.Failure;
Thank you.

The fact that Lowell managed to offer such a polite response amazes me.

Asking for help and then telling the person who offered (correct) assistance that their advice is wrong is just plain rude.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Sorry but I never said that anything was wrong.

I appreciate the advise and I never intended to be rude.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Lowell (12/23/2016)
Welsh Corgi (12/23/2016)
Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
I was looking for something simpler to start with.
If RecordCount > 0
Then fail the package
That is it.
I do not need Dts.TaskResult = (int)ScriptResults.Failure;
Thank you.
actually you DO need Dts.TaskResult = (int)ScriptResults.Failure;
that is exactly how a script task reports a failure, which in turn marks the package as failed...when any task fails.
that is how it is done in code.

I did not mean that you gave me bad advise, SORRY 🙁 I just do not recall the failure being that way.

Thank you for all of your help.

Regards.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Lowell (12/23/2016)
Welsh Corgi (12/23/2016)
Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
I was looking for something simpler to start with.
If RecordCount > 0
Then fail the package
That is it.
I do not need Dts.TaskResult = (int)ScriptResults.Failure;
Thank you.
actually you DO need Dts.TaskResult = (int)ScriptResults.Failure;
that is exactly how a script task reports a failure, which in turn marks the package as failed...when any task fails.
that is how it is done in code.

How does it recognize the variable as a SSIS User Variable?

Could we go through this from start to finish in VB.NET please?

Thank you for everything.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Phil Parkin (12/23/2016)
Welsh Corgi (12/23/2016)
Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
I was looking for something simpler to start with.
If RecordCount > 0
Then fail the package
That is it.
I do not need Dts.TaskResult = (int)ScriptResults.Failure;
Thank you.
The fact that Lowell managed to offer such a polite response amazes me.
Asking for help and then telling the person who offered (correct) assistance that their advice is wrong is just plain rude.

It is not that I am implying that the advise is wrong. I'm just trying to understand it.

I am grateful for the assistance that Lowell provided me. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323547 More actions · Answer 10

Welsh Corgi (12/23/2016)
Lowell (12/23/2016)
Welsh Corgi (12/23/2016)
Lowell (12/21/2016)
Dts.Events.FireError is what you want.
is your account used by a team of users, and not an individual?
Trivially adapted from part of an answer I posted for you yesterday @4:40pm(less than 24 hours ago)
http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653
if(myVariable <> 0)
{
Dts.Events.FireError(-1, "Main", "Your Error message", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
I was looking for something simpler to start with.
If RecordCount > 0
Then fail the package
That is it.
I do not need Dts.TaskResult = (int)ScriptResults.Failure;
Thank you.
actually you DO need Dts.TaskResult = (int)ScriptResults.Failure;
that is exactly how a script task reports a failure, which in turn marks the package as failed...when any task fails.
that is how it is done in code.
How does it recognize the variable as a SSIS User Variable?
Could we go through this from start to finish in VB.NET please?
Thank you for everything.

You have to assign the variable.

the example I had posted previously was very complete:

http://www.sqlservercentral.com/Forums/Topic1844513-364-1.aspx#bm1844653

it had variable assignments, try catch, proper raising of relevant error and package rollback.

specifically if you review this example again, you'll note where i'm assigning a c# variable whatever the value is form the current variable in DTS.

string FileName = (string)Dts.Variables["BHCS_InPatient_File"].Value;

you know what your variable and datatypes are.

you are probably at the point where you need to actually try to adapt some of the code examples provided; I'm not sure if the vb-converted version work, all I did was run them through a converter,but I KNOW the c# versions do.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!