DTSTaskExecResult_Failure = not working

"CSharpcoder" wrote in message
news:eEXoFhbtIHA.5096@TK2MSFTNGP02.phx.gbl...
>I migrating a DTS package to an SSIS package. The DTS package had an Active
>X script that is looking for a value of 1 or 0, if its 1 then the package
>continues and if its 0 it errors out and sends an email. In the SSIS
>package the Active X script is working but partially, meaning, if the
>return value is 0 its not failing and sending out an email, the Active X
>task block is showing 'green' (when I run it in SMS). If anything other
>then 1 is returned in the return value, the job should send out an email.
> The job however does stop processing, but it never fires off my Send Email
> Task.
>
> How can I fire off my Send Email task if anything other then 1 is
> returned?
>
>
Hi

Have you got the FailPackageOnFailure or FailParentOnFailure set to true at
task or package level? What is the MaximumErrorCount?

John

I've tried both and nothing, the task is still 'Green' (successful) when I
run it through SMS. If the value is [ 0 ] it needs to fail and kick off an
email, which isn't happening
"John Bell" wrote in message
news:6870C951-72A1-4EEB-8CC0-6DCA1BD2DE60@microsoft.com...
>
> "CSharpcoder" wrote in message
> news:eEXoFhbtIHA.5096@TK2MSFTNGP02.phx.gbl...
>>I migrating a DTS package to an SSIS package. The DTS package had an
>>Active X script that is looking for a value of 1 or 0, if its 1 then the
>>package continues and if its 0 it errors out and sends an email. In the
>>SSIS package the Active X script is working but partially, meaning, if the
>>return value is 0 its not failing and sending out an email, the Active X
>>task block is showing 'green' (when I run it in SMS). If anything other
>>then 1 is returned in the return value, the job should send out an email.
>> The job however does stop processing, but it never fires off my Send
>> Email Task.
>>
>> How can I fire off my Send Email task if anything other then 1 is
>> returned?
>>
>>
> Hi
>
> Have you got the FailPackageOnFailure or FailParentOnFailure set to true
> at task or package level? What is the MaximumErrorCount?
>
> John

"CSharpcoder" wrote in message
news:Ozfs%23cdtIHA.2208@TK2MSFTNGP04.phx.gbl...
> I've tried both and nothing, the task is still 'Green' (successful) when I
> run it through SMS. If the value is [ 0 ] it needs to fail and kick off an
> email, which isn't happening
> "John Bell" wrote in message
> news:6870C951-72A1-4EEB-8CC0-6DCA1BD2DE60@microsoft.com...
>>
>> "CSharpcoder" wrote in message
>> news:eEXoFhbtIHA.5096@TK2MSFTNGP02.phx.gbl...
>>>I migrating a DTS package to an SSIS package. The DTS package had an
>>>Active X script that is looking for a value of 1 or 0, if its 1 then the
>>>package continues and if its 0 it errors out and sends an email. In the
>>>SSIS package the Active X script is working but partially, meaning, if
>>>the return value is 0 its not failing and sending out an email, the
>>>Active X task block is showing 'green' (when I run it in SMS). If
>>>anything other then 1 is returned in the return value, the job should
>>>send out an email.
>>> The job however does stop processing, but it never fires off my Send
>>> Email Task.
>>>
>>> How can I fire off my Send Email task if anything other then 1 is
>>> returned?
>>>
>>>
>> Hi
>>
>> Have you got the FailPackageOnFailure or FailParentOnFailure set to true
>> at task or package level? What is the MaximumErrorCount?
>>
>> John
>
Hi

You are setting

Dts.TaskResult = Dts.Results.Failure

John

here is my script that I'm using

Function Main()
If DTSGlobalVariables("retValue").Value = "1" Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function

if 0 is returned I need the task to fail and kick off my emaol

"John Bell" wrote in message
news:B0F48FC5-348A-4BB6-915F-2ABD68045721@microsoft.com...
>
> "CSharpcoder" wrote in message
> news:Ozfs%23cdtIHA.2208@TK2MSFTNGP04.phx.gbl...
>> I've tried both and nothing, the task is still 'Green' (successful) when
>> I run it through SMS. If the value is [ 0 ] it needs to fail and kick off
>> an email, which isn't happening
>> "John Bell" wrote in message
>> news:6870C951-72A1-4EEB-8CC0-6DCA1BD2DE60@microsoft.com...
>>>
>>> "CSharpcoder" wrote in message
>>> news:eEXoFhbtIHA.5096@TK2MSFTNGP02.phx.gbl...
>>>>I migrating a DTS package to an SSIS package. The DTS package had an
>>>>Active X script that is looking for a value of 1 or 0, if its 1 then
>>>>the package continues and if its 0 it errors out and sends an email. In
>>>>the SSIS package the Active X script is working but partially, meaning,
>>>>if the return value is 0 its not failing and sending out an email, the
>>>>Active X task block is showing 'green' (when I run it in SMS). If
>>>>anything other then 1 is returned in the return value, the job should
>>>>send out an email.
>>>> The job however does stop processing, but it never fires off my Send
>>>> Email Task.
>>>>
>>>> How can I fire off my Send Email task if anything other then 1 is
>>>> returned?
>>>>
>>>>
>>> Hi
>>>
>>> Have you got the FailPackageOnFailure or FailParentOnFailure set to true
>>> at task or package level? What is the MaximumErrorCount?
>>>
>>> John
>>
> Hi
>
> You are setting
>
> Dts.TaskResult = Dts.Results.Failure
>
> John
>

"CSharpcoder" wrote in message
news:em5sWEetIHA.4376@TK2MSFTNGP06.phx.gbl...
> here is my script that I'm using
>
> Function Main()
> If DTSGlobalVariables("retValue").Value = "1" Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> End Function
>
> if 0 is returned I need the task to fail and kick off my emaol
>
> "John Bell" wrote in message
> news:B0F48FC5-348A-4BB6-915F-2ABD68045721@microsoft.com...
>>
>> "CSharpcoder" wrote in message
>> news:Ozfs%23cdtIHA.2208@TK2MSFTNGP04.phx.gbl...
>>> I've tried both and nothing, the task is still 'Green' (successful) when
>>> I run it through SMS. If the value is [ 0 ] it needs to fail and kick
>>> off an email, which isn't happening
>>> "John Bell" wrote in message
>>> news:6870C951-72A1-4EEB-8CC0-6DCA1BD2DE60@microsoft.com...
>>>>
>>>> "CSharpcoder" wrote in message
>>>> news:eEXoFhbtIHA.5096@TK2MSFTNGP02.phx.gbl...
>>>>>I migrating a DTS package to an SSIS package. The DTS package had an
>>>>>Active X script that is looking for a value of 1 or 0, if its 1 then
>>>>>the package continues and if its 0 it errors out and sends an email. In
>>>>>the SSIS package the Active X script is working but partially, meaning,
>>>>>if the return value is 0 its not failing and sending out an email, the
>>>>>Active X task block is showing 'green' (when I run it in SMS). If
>>>>>anything other then 1 is returned in the return value, the job should
>>>>>send out an email.
>>>>> The job however does stop processing, but it never fires off my Send
>>>>> Email Task.
>>>>>
>>>>> How can I fire off my Send Email task if anything other then 1 is
>>>>> returned?
>>>>>
>>>>>
>>>> Hi
>>>>
>>>> Have you got the FailPackageOnFailure or FailParentOnFailure set to
>>>> true at task or package level? What is the MaximumErrorCount?
>>>>
>>>> John
>>>
>> Hi
>>
>> You are setting
>>
>> Dts.TaskResult = Dts.Results.Failure
>>
>> John
>>
>
>

Hi

Use a Script Task rather than an Active X script

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables,
events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to
indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
'
If CType(Dts.Variables("retValue").Value, Integer) = 1 Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

End Sub

End Class

List retValue in the read only variables and declare it as a package
variable. If you build the package without any errors it should work.

John

Thanks, I'll give that a shot, and post back
"John Bell" wrote in message
news:1BEAC54E-28ED-49A9-B5B6-B389914B2E67@microsoft.com...
>
> "CSharpcoder" wrote in message
> news:em5sWEetIHA.4376@TK2MSFTNGP06.phx.gbl...
>> here is my script that I'm using
>>
>> Function Main()
>> If DTSGlobalVariables("retValue").Value = "1" Then
>> Main = DTSTaskExecResult_Success
>> Else
>> Main = DTSTaskExecResult_Failure
>> End If
>> End Function
>>
>> if 0 is returned I need the task to fail and kick off my emaol
>>
>> "John Bell" wrote in message
>> news:B0F48FC5-348A-4BB6-915F-2ABD68045721@microsoft.com...
>>>
>>> "CSharpcoder" wrote in message
>>> news:Ozfs%23cdtIHA.2208@TK2MSFTNGP04.phx.gbl...
>>>> I've tried both and nothing, the task is still 'Green' (successful)
>>>> when I run it through SMS. If the value is [ 0 ] it needs to fail and
>>>> kick off an email, which isn't happening
>>>> "John Bell" wrote in message
>>>> news:6870C951-72A1-4EEB-8CC0-6DCA1BD2DE60@microsoft.com...
>>>>>
>>>>> "CSharpcoder" wrote in message
>>>>> news:eEXoFhbtIHA.5096@TK2MSFTNGP02.phx.gbl...
>>>>>>I migrating a DTS package to an SSIS package. The DTS package had an
>>>>>>Active X script that is looking for a value of 1 or 0, if its 1 then
>>>>>>the package continues and if its 0 it errors out and sends an email.
>>>>>>In the SSIS package the Active X script is working but partially,
>>>>>>meaning, if the return value is 0 its not failing and sending out an
>>>>>>email, the Active X task block is showing 'green' (when I run it in
>>>>>>SMS). If anything other then 1 is returned in the return value, the
>>>>>>job should send out an email.
>>>>>> The job however does stop processing, but it never fires off my Send
>>>>>> Email Task.
>>>>>>
>>>>>> How can I fire off my Send Email task if anything other then 1 is
>>>>>> returned?
>>>>>>
>>>>>>
>>>>> Hi
>>>>>
>>>>> Have you got the FailPackageOnFailure or FailParentOnFailure set to
>>>>> true at task or package level? What is the MaximumErrorCount?
>>>>>
>>>>> John
>>>>
>>> Hi
>>>
>>> You are setting
>>>
>>> Dts.TaskResult = Dts.Results.Failure
>>>
>>> John
>>>
>>
>>
>
> Hi
>
> Use a Script Task rather than an Active X script
>
> ' Microsoft SQL Server Integration Services Script Task
> ' Write scripts using Microsoft Visual Basic
> ' The ScriptMain class is the entry point of the Script Task.
>
> Imports System
> Imports System.Data
> Imports System.Math
> Imports Microsoft.SqlServer.Dts.Runtime
>
> Public Class ScriptMain
>
> ' The execution engine calls this method when the task executes.
> ' To access the object model, use the Dts object. Connections, variables,
> events,
> ' and logging features are available as static members of the Dts class.
> ' Before returning from this method, set the value of Dts.TaskResult to
> indicate success or failure.
> '
> ' To open Code and Text Editor Help, press F1.
> ' To open Object Browser, press Ctrl+Alt+J.
>
> Public Sub Main()
> '
> ' Add your code here
> '
> If CType(Dts.Variables("retValue").Value, Integer) = 1 Then
> Dts.TaskResult = Dts.Results.Success
> Else
> Dts.TaskResult = Dts.Results.Failure
> End If
>
> End Sub
>
> End Class
>
> List retValue in the read only variables and declare it as a package
> variable. If you build the package without any errors it should work.
>
> John

I'm now getting this error:

DTS Script task has encountered an exception in user code:

Project name ScriptTask_

The element cannot be found in a collection. This error happens when you try
to retrieve an element from a collection on a
container during execution of the package and the element is not there.

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

at ScriptTask_7d9df8666bdd49f5b5da696fdb7db73d.ScriptMain.Main()



what does that all mean?


"John Bell" wrote in message
news:1BEAC54E-28ED-49A9-B5B6-B389914B2E67@microsoft.com...
>
> "CSharpcoder" wrote in message
> news:em5sWEetIHA.4376@TK2MSFTNGP06.phx.gbl...
>> here is my script that I'm using
>>
>> Function Main()
>> If DTSGlobalVariables("retValue").Value = "1" Then
>> Main = DTSTaskExecResult_Success
>> Else
>> Main = DTSTaskExecResult_Failure
>> End If
>> End Function
>>
>> if 0 is returned I need the task to fail and kick off my emaol
>>
>> "John Bell" wrote in message
>> news:B0F48FC5-348A-4BB6-915F-2ABD68045721@microsoft.com...
>>>
>>> "CSharpcoder" wrote in message
>>> news:Ozfs%23cdtIHA.2208@TK2MSFTNGP04.phx.gbl...
>>>> I've tried both and nothing, the task is still 'Green' (successful)
>>>> when I run it through SMS. If the value is [ 0 ] it needs to fail and
>>>> kick off an email, which isn't happening
>>>> "John Bell" wrote in message
>>>> news:6870C951-72A1-4EEB-8CC0-6DCA1BD2DE60@microsoft.com...
>>>>>
>>>>> "CSharpcoder" wrote in message
>>>>> news:eEXoFhbtIHA.5096@TK2MSFTNGP02.phx.gbl...
>>>>>>I migrating a DTS package to an SSIS package. The DTS package had an
>>>>>>Active X script that is looking for a value of 1 or 0, if its 1 then
>>>>>>the package continues and if its 0 it errors out and sends an email.
>>>>>>In the SSIS package the Active X script is working but partially,
>>>>>>meaning, if the return value is 0 its not failing and sending out an
>>>>>>email, the Active X task block is showing 'green' (when I run it in
>>>>>>SMS). If anything other then 1 is returned in the return value, the
>>>>>>job should send out an email.
>>>>>> The job however does stop processing, but it never fires off my Send
>>>>>> Email Task.
>>>>>>
>>>>>> How can I fire off my Send Email task if anything other then 1 is
>>>>>> returned?
>>>>>>
>>>>>>
>>>>> Hi
>>>>>
>>>>> Have you got the FailPackageOnFailure or FailParentOnFailure set to
>>>>> true at task or package level? What is the MaximumErrorCount?
>>>>>
>>>>> John
>>>>
>>> Hi
>>>
>>> You are setting
>>>
>>> Dts.TaskResult = Dts.Results.Failure
>>>
>>> John
>>>
>>
>>
>
> Hi
>
> Use a Script Task rather than an Active X script
>
> ' Microsoft SQL Server Integration Services Script Task
> ' Write scripts using Microsoft Visual Basic
> ' The ScriptMain class is the entry point of the Script Task.
>
> Imports System
> Imports System.Data
> Imports System.Math
> Imports Microsoft.SqlServer.Dts.Runtime
>
> Public Class ScriptMain
>
> ' The execution engine calls this method when the task executes.
> ' To access the object model, use the Dts object. Connections, variables,
> events,
> ' and logging features are available as static members of the Dts class.
> ' Before returning from this method, set the value of Dts.TaskResult to
> indicate success or failure.
> '
> ' To open Code and Text Editor Help, press F1.
> ' To open Object Browser, press Ctrl+Alt+J.
>
> Public Sub Main()
> '
> ' Add your code here
> '
> If CType(Dts.Variables("retValue").Value, Integer) = 1 Then
> Dts.TaskResult = Dts.Results.Success
> Else
> Dts.TaskResult = Dts.Results.Failure
> End If
>
> End Sub
>
> End Class
>
> List retValue in the read only variables and declare it as a package
> variable. If you build the package without any errors it should work.
>
> John

"CSharpcoder" wrote in message
news:u3KuhVftIHA.1316@TK2MSFTNGP06.phx.gbl...
> I'm now getting this error:
>
> DTS Script task has encountered an exception in user code:
>
> Project name ScriptTask_
>
> The element cannot be found in a collection. This error happens when you
> try to retrieve an element from a collection on a
> container during execution of the package and the element is not there.
>
> at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
>
> at ScriptTask_7d9df8666bdd49f5b5da696fdb7db73d.ScriptMain.Main()
>
>
>
> what does that all mean?
>
Hi

You hae not listed retValue as a readonly variable. In the script task
editor dialog on the script branch you should see readonlyvariables and
readwritevariables as the last two items. Add retValue to readonlyvariables.

John

That got it to fail, but now its not calling my email task. I've created a
failure constraint to my email task, but its never being called. What am I
missing on that?


"John Bell" wrote in message
news:C4892B2B-2C17-4918-AFC8-1486F0FD0F95@microsoft.com...
>
> "CSharpcoder" wrote in message
> news:u3KuhVftIHA.1316@TK2MSFTNGP06.phx.gbl...
>> I'm now getting this error:
>>
>> DTS Script task has encountered an exception in user code:
>>
>> Project name ScriptTask_
>>
>> The element cannot be found in a collection. This error happens when you
>> try to retrieve an element from a collection on a
>> container during execution of the package and the element is not there.
>>
>> at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
>>
>> at ScriptTask_7d9df8666bdd49f5b5da696fdb7db73d.ScriptMain.Main()
>>
>>
>>
>> what does that all mean?
>>
> Hi
>
> You hae not listed retValue as a readonly variable. In the script task
> editor dialog on the script branch you should see readonlyvariables and
> readwritevariables as the last two items. Add retValue to
> readonlyvariables.
>
> John