rsteckly (8/5/2009)

.......I'd like a tool that monitors the database server(s) and notifies me when issues arise. Ideally, this tool would allow me to export the data.

I'd like to be...

Access absent?? Do you mean access denied? The error you are seeing usually happens when you don't have permissions to connect to the SQL instance. Are you...

Since you are using stored procedues and the DB engine is going to be doing all of the work, the only real benefit I see to calling the SPs outside...

Sorry for the delay in my response.

You don't need to create an event handler for the OnError event. The article that I posted a link for shows you how...

You've already done it. The error handling is all of the stuff you've setup down stream from the OLE DB Destination. You set the error condition of your...

Well you insertion logic is already there. It is your OLE DB Destination. If you want your data validations to happen at the database layer and redirect your...

What you need to do is set up the insert, along with the post-insert error checking logic into a single transaction so if you reach your ceiling of 50 errors,...

You can do the validations in SSIS, but we need to know what conditions cause the rows to fail. In other words, what makes the rows 'bad'?

So, you want a failure to roll back the 5 rows?

Here's what I'd do.

Right now, you're using the error condition that the DB engine raises whenever you attempt to insert...

Package level scope is fine. You don't need to create a new variable. By case, I mean case sensitive. If your variable is 'errorrowcount', make sure it's...

Check the variable name in the script and make sure it matches (including case) the variable name that you created in your package.

I tried to duplicate this will no luck. I set up a table with an identity column and ran 2 rows through my data flow. One row already...

Sorry, my bad. In both scripts, above the Imports section at the top, add this entry: Option Strict Off.

OK, good news, bad news. Up until now, I had not tried to use the RowCount transformation in conjunction with the Conditional Split transformation.

I went ahead and...

Sorry for the delayed response. The RowCount transformation allows you to read in the # of rows in the data flow and place that value into a variable (of...