February 3, 2012 at 9:03 am
Hello all,
I have a couple of questions related to a project I'm on:
1. I've got a working SSIS package that opens a CSV file, uploads it into a temp table, then runs a s/p to update various tables from the uploaded data. It's working fine, but I'd like to build in some fail-safes.
Is there a way to get the success or failure state of a s/p run during an Execute SQL task in SSIS? I see the SendEmail component. Can I use that to send an email failure notice?
2. I also have another s/p that creates a CSV from my table data, first by extracting that data in a s/p, then handing the output of that s/p to bcp, which creates the CSV. Can anyone suggest a way for the first s/p to notify the second s/p if the first one selected no records? (Hence no need to write a 0 kb CSV.)
Thanks,
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
February 3, 2012 at 1:34 pm
Capt. Sigerson (2/3/2012)
Hello all,I have a couple of questions related to a project I'm on:
1. I've got a working SSIS package that opens a CSV file, uploads it into a temp table, then runs a s/p to update various tables from the uploaded data. It's working fine, but I'd like to build in some fail-safes.
Is there a way to get the success or failure state of a s/p run during an Execute SQL task in SSIS? I see the SendEmail component. Can I use that to send an email failure notice?
2. I also have another s/p that creates a CSV from my table data, first by extracting that data in a s/p, then handing the output of that s/p to bcp, which creates the CSV. Can anyone suggest a way for the first s/p to notify the second s/p if the first one selected no records? (Hence no need to write a 0 kb CSV.)
Thanks,
I send emails using "exec msdb.dbo.sp_send_dbmail" with the recipients, subject, etc., from an ExecuteSQL task vs using SendMail component. I have validation steps which we want to know results of regardless of pass/fail so this keeps us notified.
I also have output parameters returned from stored procedures so I can check on the value of my variables at any time. In fact, I use them in the emails to determine which email version to send -pass vs fail. I do nothing with the return parameter, don't even map to it, but it could be of use to you.
So step 1 would be creating the variables, mapping them as output within the ExecuteSQL task that runs the stored procedure, declared as output parameters in the stored procedure with appropriate values stored in them during sp run.
then, finally, another ExecSQL task after the sp that can send the appropriate email based upon the value of those output parameters/local variables.
"Validation Enabled" and "Validation Failed" are variables I refer to a lot in the packages.
One thing to note, how you set up your parameters in the execsql task depends somewhat on what provider you are using. If you happen to be using OLE DB like I am, your parameter names start at 0 then 1, and so on.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply