February 24, 2010 at 1:13 pm
I just have a general question on the best way to proceed with something. I have something that I want to automate and Integration Services would do what I need to, however, I need to be able to check some things and either allow the flow to continue or stop and send a message. For instance, one of the first things I need to do is check for bad records. If there are any then I want to stop the process and send an email to a group of people to look into it. If the sp that runs doesn't find any bad records then I want it to continue on to the next item, which would be another sp to run.
Just looking for suggestions on what Control Flow Items or Transformation items to use in order to make this happen.
Ideally, I would also like to be able to capture any errors that may result as well so I know how far it got before failing.
Thanks.
February 24, 2010 at 1:41 pm
I'm a little fuzzy on your process. IT sounds like you have a sproc that does some processing and then may or may not kick out an error list. This will be kind of tricky.. You might have to break that process up a little bit in SSIS to capture what you want. You might give some additional detail to work from..
CEWII
February 24, 2010 at 1:52 pm
If this first SP returns any records then we know we need to do some checking to see if it is good/bad. This is and will have to be a manual process. So, what I want to do is run this SP and if it returns no records move onto the next step. If it does return records then I want it to send an email and stop the whole process.
February 24, 2010 at 1:57 pm
Where do these returned records go?
CEWII
February 24, 2010 at 2:00 pm
They don't need to go anywhere really. Probably just put them in the email notification. When I say put them in the notification it is as simple as making sure the primary key field is in there. There may be more than one record but typically it would only be one record.
February 24, 2010 at 2:15 pm
Ok, I think I have a thought..
1. Declare a variable as type object
2. Make sure the sproc ALWAYS returns a resultset, even if it is empty.. Set the result-set to full and map the result-set to the variable you just created.
3. Add a script task and add the variable used in step 1 and 2 as a read-only variable
This is where it gets tricky..
The email part is easy, here is the basic code for it:
Dim _oSMTPClient As New SmtpClient("smtpmailhost.yourcompany.com")
Dim _oMailMsg As New MailMessage()
_oMailMsg.From = New MailAddress("someemailaddress@yourcompany.com")
_oMailMsg.To.Add("someotheremailaddress@yourcompany.com")
_oMailMsg.Subject = "The Subject"
_oMailMsg.Body = "Message Body"
_oSMTPClient.Send(_oMailMsg)
Don't forget:
Imports System.Net.Mail
I'm looking for the code to look at the result-set variable and be able to iterate through it. This step gives us several things.
1. A rowcount, if it is zero then we can set the status to success, if it is not then then we can say it is a fail.
2. We can build the body of the message to send.
I'll have to get back to you on this code.
If this fails the SSIS is done, if not then it will continue to run. Thoughts?
CEWII
February 24, 2010 at 2:25 pm
I haven't done a lot of this before but sounds like it will work. I'm going to start trying to do it and I'll see where it goes.
February 24, 2010 at 2:26 pm
Here is some more code, this is the framework I'm thinking of..
Dim _bSuccessFlag as Boolean = false
Dim _oOledbAdapter As New OleDb.OleDbDataAdapter
Dim _oDataTable As New System.Data.DataTable
Dim _oDataRow As Data.DataRow
_oOledbAdapter.Fill(_oDataTable, Dts.Variables("ResultSetVariable").Value)
IF _oDatatable.Rows.Count = 0 THEN
_bSuccessFlag = true
ELSE
For Each _oDataRow In _oDataTable.Rows
'Build the message here from like _oDataRow.Item(1).ToString
Next
' Mail the message
END if
IF _bSuccessFlag THEN
Dts.TaskResult = Dts.Results.Success
ELSE
Dts.TaskResult = Dts.Results.Failure
END IF
CEWII
February 24, 2010 at 2:29 pm
I can help you out more tomorrow. Most of this code is snippets from my personal stuff where I have done this.
CEWII
February 24, 2010 at 3:48 pm
Thanks
February 25, 2010 at 3:51 pm
Elliot,
Thanks for your ideas. I ended up just using the SP in a data flow and then pushing the row count to a SSIS variable and then used the precedence constraints to perform the remaining tasks in the package.
February 25, 2010 at 5:12 pm
Sounds good.
CEWII
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply