Post reply

Fail Package based on variable

  • 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/

  • December 28, 2016 at 8:39 am

    #1919879

    Does this work for you?

    If RecordCount > 0 Then

    DTS.TaskResult = Dts.Results.Failure

  • 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/

  • 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!

  • 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/

  • 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/

  • 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!

  • 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/

Viewing 8 posts - 1 through 7 (of 7 total)

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