SSIS - Stop parent based on child data

Question

SSIS - Stop parent based on child data

kdbarrett 88655

Right there with Babe

Points: 770
More actions
January 26, 2017 at 9:29 am

#320958

It's been a few years since I've done any real SSIS; back when 2008 was new, so really 2005 was my last true exposure to SSIS. So, something that seems simple is giving me trouble in BIDS 2010.
I have to process four files: start with source files, add contents to a database, extract destination files. I have no problem there.
The problem arises when I have a parent package run the four as child packages. The first two files take ClientIDs from external systems and I have a process that matches those to internal ClientIDs. Some records don't match and I want to stop the whole process, notify users and have them fix the problem. So it's not an "error"; the file processes, it just doesn't get a match. I have a Conditional Split in the child package does what I need it to do, but I can't figure out how to tell the parent package that the child found exceptions.
If in ChildPackage1, FileA produces say, 2 exceptions, I want ParentPackage to stop and notify before moving on to ChildPackage2.
I tried setting a project variable, EpisodesExceptions, to the exception rowcount but when I add "$Project::EpisodesExceptions" to the list of ReadWriteVariables for the script task, it just hangs.
Any advice? Thanks.

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

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

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

kdbarrett 88655 - Thursday, January 26, 2017 9:29 AM
It's been a few years since I've done any real SSIS; back when 2008 was new, so really 2005 was my last true exposure to SSIS. So, something that seems simple is giving me trouble in BIDS 2010.
I have to process four files: start with source files, add contents to a database, extract destination files. I have no problem there.
The problem arises when I have a parent package run the four as child packages. The first two files take ClientIDs from external systems and I have a process that matches those to internal ClientIDs. Some records don't match and I want to stop the whole process, notify users and have them fix the problem. So it's not an "error"; the file processes, it just doesn't get a match. I have a Conditional Split in the child package does what I need it to do, but I can't figure out how to tell the parent package that the child found exceptions.
If in ChildPackage1, FileA produces say, 2 exceptions, I want ParentPackage to stop and notify before moving on to ChildPackage2.
I tried setting a project variable, EpisodesExceptions, to the exception rowcount but when I add "$Project::EpisodesExceptions" to the list of ReadWriteVariables for the script task, it just hangs.
Any advice? Thanks.

I suggest setting up a logging table in your database. Let the child packages write to that and then, once they've completed, the parent package can query the logging table looking for errors.

drew.allen SSC Guru Points: 77014 More actions · Answer 2

kdbarrett 88655 - Thursday, January 26, 2017 9:29 AM
So it's not an "error"; the file processes, it just doesn't get a match.

You need a match in order to continue and you failed to find a match. How is that not an error?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

kdbarrett 88655 Right there with Babe Points: 770 More actions · Answer 3

Phil Parkin - Thursday, January 26, 2017 10:48 AM
kdbarrett 88655 - Thursday, January 26, 2017 9:29 AM
It's been a few years since I've done any real SSIS; back when 2008 was new, so really 2005 was my last true exposure to SSIS. So, something that seems simple is giving me trouble in BIDS 2010.
I have to process four files: start with source files, add contents to a database, extract destination files. I have no problem there.
The problem arises when I have a parent package run the four as child packages. The first two files take ClientIDs from external systems and I have a process that matches those to internal ClientIDs. Some records don't match and I want to stop the whole process, notify users and have them fix the problem. So it's not an "error"; the file processes, it just doesn't get a match. I have a Conditional Split in the child package does what I need it to do, but I can't figure out how to tell the parent package that the child found exceptions.
If in ChildPackage1, FileA produces say, 2 exceptions, I want ParentPackage to stop and notify before moving on to ChildPackage2.
I tried setting a project variable, EpisodesExceptions, to the exception rowcount but when I add "$Project::EpisodesExceptions" to the list of ReadWriteVariables for the script task, it just hangs.
Any advice? Thanks.
I suggest setting up a logging table in your database. Let the child packages write to that and then, once they've completed, the parent package can query the logging table looking for errors.

I was thinking of something like that, but was hoping to avoid extra database calls. I may have to go this route, though.

kdbarrett 88655 Right there with Babe Points: 770 More actions · Answer 4

drew.allen - Thursday, January 26, 2017 11:06 AM
kdbarrett 88655 - Thursday, January 26, 2017 9:29 AM
So it's not an "error"; the file processes, it just doesn't get a match.
You need a match in order to continue and you failed to find a match. How is that not an error?
Drew

It's not a SQL or file processing failure. Query returns, say, 98 rows instead of 100. That doesn't generate an error. I've tried forcing them with TaskResults, but I find those to be unreliable.

drew.allen SSC Guru Points: 77014 More actions · Answer 5

kdbarrett 88655 - Thursday, January 26, 2017 11:22 AM
drew.allen - Thursday, January 26, 2017 11:06 AM
kdbarrett 88655 - Thursday, January 26, 2017 9:29 AM
So it's not an "error"; the file processes, it just doesn't get a match.
You need a match in order to continue and you failed to find a match. How is that not an error?
Drew
It's not a SQL or file processing failure. Query returns, say, 98 rows instead of 100. That doesn't generate an error. I've tried forcing them with TaskResults, but I find those to be unreliable.

What I'm saying is that it needs to generate an error if you fail to find a match. I'm not sure how you are finding your matches, so I can't tell you how to change it to produce an error when it doesn't find a match.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

kdbarrett 88655 Right there with Babe Points: 770 More actions · Answer 6

drew.allen - Thursday, January 26, 2017 11:51 AM
kdbarrett 88655 - Thursday, January 26, 2017 11:22 AM
drew.allen - Thursday, January 26, 2017 11:06 AM
kdbarrett 88655 - Thursday, January 26, 2017 9:29 AM
So it's not an "error"; the file processes, it just doesn't get a match.
You need a match in order to continue and you failed to find a match. How is that not an error?
Drew
It's not a SQL or file processing failure. Query returns, say, 98 rows instead of 100. That doesn't generate an error. I've tried forcing them with TaskResults, but I find those to be unreliable.
What I'm saying is that it needs to generate an error if you fail to find a match. I'm not sure how you are finding your matches, so I can't tell you how to change it to produce an error when it doesn't find a match.
Drew

I stuck an OLEDB Command Task with a simple RAISEERROR in the branch that finds exceptions. So if there are any records that don't match, it hits this task, throws and error and still sends an email. Exactly what I was looking for. Thanks for the inspiration!

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

kdbarrett 88655 - Thursday, January 26, 2017 1:17 PM
I stuck an OLEDB Command Task with a simple RAISEERROR in the branch that finds exceptions. So if there are any records that don't match, it hits this task, throws and error and still sends an email. Exactly what I was looking for. Thanks for the inspiration!

If you want a non-database way of throwing an error, create a Script Task and write your code to return ScriptResults.xxx

public void Main() { bool hasErrors = Convert.ToBoolean(Dts.Variables["User::HasValidationErrors"].Value);

if (hasErrors) { Dts.TaskResult = (int)ScriptResults.Failure; } else { Dts.TaskResult = (int)ScriptResults.Success; } }

kdbarrett 88655 Right there with Babe Points: 770 More actions · Answer 8

Phil Parkin - Thursday, January 26, 2017 1:26 PM
kdbarrett 88655 - Thursday, January 26, 2017 1:17 PM
I stuck an OLEDB Command Task with a simple RAISEERROR in the branch that finds exceptions. So if there are any records that don't match, it hits this task, throws and error and still sends an email. Exactly what I was looking for. Thanks for the inspiration!
If you want a non-database way of throwing an error, create a Script Task and write your code to return ScriptResults.xxx
public void Main() { bool hasErrors = Convert.ToBoolean(Dts.Variables["User::HasValidationErrors"].Value);
if (hasErrors) { Dts.TaskResult = (int)ScriptResults.Failure; } else { Dts.TaskResult = (int)ScriptResults.Success; } }

I tried that and the task still completed successfully. I've had that issue with using this method before. But the RAISEERROR in an OLEDB Command Task works great!

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

kdbarrett 88655 - Thursday, January 26, 2017 3:02 PM
I tried that and the task still completed successfully. I've had that issue with using this method before. But the RAISEERROR in an OLEDB Command Task works great!

Whatever issue you had, it can be fixed. I use this method a lot and it works every time.

kdbarrett 88655 Right there with Babe Points: 770 More actions · Answer 10

Phil Parkin - Thursday, January 26, 2017 3:47 PM
kdbarrett 88655 - Thursday, January 26, 2017 3:02 PM
I tried that and the task still completed successfully. I've had that issue with using this method before. But the RAISEERROR in an OLEDB Command Task works great!
Whatever issue you had, it can be fixed. I use this method a lot and it works every time.

Here's a sample that results in success:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail ' Added

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
Public Sub Main()
    'Stuff happens here

Dts.TaskResult = ScriptResults.Failure
End Sub

End Class

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

kdbarrett 88655 - Friday, January 27, 2017 9:10 AM
Phil Parkin - Thursday, January 26, 2017 3:47 PM
kdbarrett 88655 - Thursday, January 26, 2017 3:02 PM
I tried that and the task still completed successfully. I've had that issue with using this method before. But the RAISEERROR in an OLEDB Command Task works great!
Whatever issue you had, it can be fixed. I use this method a lot and it works every time.
Here's a sample that results in success:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail     ' Added
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
Public Sub Main()
    'Stuff happens here
   Dts.TaskResult = ScriptResults.Failure
End Sub
End Class

What version of SSIS is this?

kdbarrett 88655 Right there with Babe Points: 770 More actions · Answer 12

Phil Parkin - Friday, January 27, 2017 9:18 AM
kdbarrett 88655 - Friday, January 27, 2017 9:10 AM
Phil Parkin - Thursday, January 26, 2017 3:47 PM
kdbarrett 88655 - Thursday, January 26, 2017 3:02 PM
I tried that and the task still completed successfully. I've had that issue with using this method before. But the RAISEERROR in an OLEDB Command Task works great!
Whatever issue you had, it can be fixed. I use this method a lot and it works every time.
Here's a sample that results in success:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail     ' Added
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
Public Sub Main()
    'Stuff happens here
   Dts.TaskResult = ScriptResults.Failure
End Sub
End Class
What version of SSIS is this?

2010. And now it seems to be working fine...