September 1, 2009 at 9:33 am
Hi all,
I'm trying to build a package that will import a flat file in a table, but will "match" to another table to know if the "Department" is valid.
I've attached a JPG to make it clearer, but here's what I'm waiting to do:
If 1 of the departments I'm trying to load does not exist in the MasterDepartment Table, send an e-mail and fail the package. I've been able to send an e-mail from the control flow, but not from the data flow, and that's the problem.
There must be a way to make the data flow fail with some kind of parameter, that I would know I need to send the e-mail from the Control Flow.
I'm stumped there, I've got pretty basic experience with SSIS, so go easy on me, 😉
Thanks in advance,
Cheers,
J-F
September 1, 2009 at 10:51 am
Maybe you could do it this way - though it does require (in the case of no missing departments) two reads of the source data:
1) Data flow task 1 retrieves a DISTINCT list of departments contained in the flat file (might need an aggregate transformation) and LEFT JOINS to the master table (m). A subsequent conditional split gets rid of matches against m and then a count transform assigns the count to a package-scoped variable. This count is the number of departments which do not have matches on m.
2) On your control flow, test the value of the count variable and send e-mails as necessary using appropriate precedence conditions. If required, you can fail the package at this point.
3) If the value of your count variable is 0, proceed to your existing data flow (data flow task 2) and go ahead with the import.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 1, 2009 at 11:04 am
That looks like a nice work around Phil, thanks for the tip.
So, my interpretation is that there is no way to actually "Fail" the data flow, so the control flow gets a red line and I can send an e-mail.
I will try the count technique, and post again with feedback.
Thanks again,
Cheers,
J-F
September 1, 2009 at 11:21 am
I deliberately avoided that question, because I don't know the answer!
The necessary 'machinery' for dataflow failure is obviously in place (FailPackage..., FailParent, ForceExecution...) and maybe you can get at these settings in script ...
Or you could always use a script component, or maybe even a derived column (simpler), in the dataflow to write an invalid record and fail it that way - rather inelegant though.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 1, 2009 at 1:21 pm
Well, we know that you can use the Send Mail task in both the Control Flow and in the OnError event handler. You could route the missing lookups from your Department Lookup transformation into an OLE DB Command task. Inside your OLE DB Command task, execute a RAISERROR to error out your data flow. Raise a customer error so that in your OnError event handler, you can verify that it is your customer error and send an e-mail accordingly.
September 1, 2009 at 1:51 pm
Hi John,
First of all, thanks for your input, it has helped me discover a new facet of SSIS, the event handlers.
I might a few questions regarding the usage of this new feature though:
1. There was 1400 departments that did not exist in the table.. and the solution works nicely, it sent me 1400 e-mails, saying there was an error.. Geez.. Is there a way to summarize the data somehow?
2. Also, I would like to know, if it's possible to get a parameter in the send mail task, maybe I could provide the department name that is faulty, that could be helpful. Maybe not for this scenario, but I can certainly see it useful for future uses.
Thanks for your time,
Cheers,
J-F
September 1, 2009 at 3:00 pm
1. 1400 emails - what's wrong with that? Just kidding! I've used this before and only the first error triggered the OnError event handler. You may check your RAISERROR statement to ensure that the error severity is high enough to stop the package execution. That way, the first error triggers the OnError event and package executing (or at least data flow execution) ends. You can control whether or not an error in a control flow task bubbles up to it's parent container by setting the Propogate variable inside the OnError event.
2. I'm not sure it you can do that in the e-mail task since the OnError event will not have access to the data flow pipeline. You could set the RAISERROR statement to be parameterized and customize the error description that is raised. Once you get the OLE DB Command task to throw the error with the description that you want (using the department name, id, etc), use the ErrorCode and ErrorDescription variables inside the OnError event to configure your Send Mail task. In a sense, you'd be using RAISERROR to pass error information into your event handler via the standard ErrorCode/ErrorDescription system variables. By the way, if you're new to event handlers. The ErrorCode/ErrorDescription system variables are scoped to the OnError event so you would not see them elsewhere when checking the system variables.
September 2, 2009 at 6:23 am
Thanks a lot, you're right, the severity was not high enough (I tested with 2 departments this time, :-P)
I will look into the parametrization of the error messaging, and how I can use the Raiserror/OnError to achieve this.
Thanks a lot for your help John, and thanks a lot to you phil also, for putting me on the right track!
I might have more questions in the next days for this new SSIS adventure, so I better start making a few SSIS friends!
Thanks again guys, you've helped me much,
Cheers,
J-F
September 2, 2009 at 6:44 am
I just had a thought about this - why not set up an Excel spreadsheet (or CSV file) and redirect any missing departments to that?
Then include the spreadsheet as an attachment for your 'error' e-mail ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 2, 2009 at 8:42 am
Phil's suggestion would be a good option as well. You'd just need to put in some logic to count up your error rows in the data flow as they are routed to the file and then in your control flow, after the data flow, have a Send Mail task conditionally fire based on the counter variable.
As far as parameterizing your error message, you could create a stored procedure that builds out your RAISERROR message as you'd like to see it and raises the error. Include the call to the SP in the OLE DB Command task and use the column mappings tab to get your data flow column values into the variables for the SP. I just tested that out and it works just fine.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply