Fail Package based on variable

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  December 28, 2016 at 8:27 am

  #324697

  I'm trying to fail a packages if a DTS Variable > 0.

  Public Sub Main ()

  Dim RecordCount As String

  DTS.Variables.contains ('Record Count") = True

  If RecordCount > 0 Then

  DTS.TaskResult = Failure

  What is wrong with this code?

  I just want to fail the package if the RecordCount is greater than 0.

  Thanks.

  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/

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738337

  More actions

  December 28, 2016 at 8:39 am

  #1919879

  Does this work for you?

  If RecordCount > 0 Then

  DTS.TaskResult = Dts.Results.Failure

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  December 28, 2016 at 8:47 am

  #1919880

  Steve Jones - SSC Editor (12/28/2016)

  ---

  Does this work for you?

  If RecordCount > 0 Then

  DTS.TaskResult = Dts.Results.Failure

  Thanks but I'm unclear what the entire syntax should be.:unsure:

  Thank you very much.

  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: 323527

  More actions

  December 28, 2016 at 8:54 am

  #1919881

  Public Sub Main()

  '

  ' Add your code here

  '

  Dim RecordCount As Integer

  RecordCount = CInt(Dts.Variables("RecordCount").Value)

  If RecordCount > 0 Then

  Dts.TaskResult = ScriptResults.Failure

  Else

  Dts.TaskResult = ScriptResults.Success

  End If

  End Sub

  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

  December 28, 2016 at 9:01 am

  #1919886

  Lowell (12/28/2016)

  ---

  Public Sub Main()

  '

  ' Add your code here

  '

  Dim RecordCount As Integer

  RecordCount = CInt(Dts.Variables("RecordCount").Value)

  If RecordCount > 0 Then

  Dts.TaskResult = ScriptResults.Failure

  Else

  Dts.TaskResult = ScriptResults.Success

  End If

  End Sub

  Thanks a lot Lowell.

  If the task does not fail I need it to execute a SQL EXEC Task to complete the package but this looks like eacactly what I need.

  Thanks again.

  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

  December 28, 2016 at 9:17 am

  #1919890

  I run the code and it succeeds.

  I tried adding a MessageBox to check the

  Public Sub Main()

  Dim RecordCount As Integer

  RecordCount = CInt(Dts.Variables("RecordCount").Value)

  'MessageBox.Show("Looks Good")

  MessageBox.Show(RecordCount) -- Implicit Conversion from integer to String

  If RecordCount > 0 Then

  Dts.TaskResult = ScriptResults.Failure

  End If

  End Sub

  In an attempt to display the value of the RecordCount Value as I hover over the line I get: The value of the record count = 1 so I need it to fail the package.

  What am I missing?

  Thanks.:-)

  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: 323527

  More actions

  December 28, 2016 at 9:22 am

  #1919892

  MessageBox can only display strings, and RecordCount is an integer,so simply convert the value you want to display.

  MessageBox.Show(RecordCount.ToString)

  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

  December 28, 2016 at 9:43 am

  #1919897

  Lowell (12/28/2016)

  ---

  MessageBox can only display strings, and RecordCount is an integer,so simply convert the value you want to display.

  MessageBox.Show(RecordCount.ToString)

  Sweet it works 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/