September 30, 2011 at 1:26 pm
Here's the process:
The issue comes in the 3rd step. The 3rd step consists of 3 data flow tasks with the following tasks:
I am processing the data 1000 rows at a time, so I expect to see 1000 rows be transferred to DB's C, D, & E. What happens is I will get an error for one row that says "An error has occurred while sending this row to destination data source." Really helpful error message. This error causes no more rows to be processed. So I might get 362 of 1000 rows transferred. If I get another error like a unique constraint error on 1 row, there rest of the rows get processed still, which is what I expect to happen on ANY error. I have run the process with a trace running and I only see this error in the trace:
The statement has been terminated.3621
I have looked in the SQL Error Log and the Windows Error Log and have not seen anything there. Any other ideas?
Oh, and I can immediately reprocess the rows without error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2011 at 2:07 pm
Jack Corbett (9/30/2011)
Here's the process:....
What happens is I will get an error for one row that says "An error has occurred while sending this row to destination data source."
I have a few ideas....
Oh, and I can immediately reprocess the rows without error.
:blink: Nevermind, no I don't.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2011 at 2:13 pm
Gee thanks. I'm glad I edited it and put that sentence back in. This one has me befuddled completely.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2011 at 4:13 pm
I'm kinda at a loss too. It would be better if the error was consistent.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2011 at 9:45 pm
Jack Corbett (9/30/2011)
"An error has occurred while sending this row to destination data source.".
Is it a network connectivity issue? It seems I've gotten that vague (non-helpful) error message when connected to a client via VPN and I have connectivity issues (usually hiccup in wireless internet connection). Though I don't know if/how this would be logged in the error logs.
Just a thought,
Rob
October 3, 2011 at 6:19 am
Is the ADO .NET destination a SQL Server DB? Do you get different results if you change it to an OLE DB destination? You can still redirect errored rows with OLEDB, you just have to change it from being a Fast Load to a normal load.
Edit: Removed another question after re-reading
October 3, 2011 at 6:38 am
rgtft (10/2/2011)
Jack Corbett (9/30/2011)
"An error has occurred while sending this row to destination data source.".Is it a network connectivity issue? It seems I've gotten that vague (non-helpful) error message when connected to a client via VPN and I have connectivity issues (usually hiccup in wireless internet connection). Though I don't know if/how this would be logged in the error logs.
Just a thought,
Rob
I suppose it could be a network issue. I haven't really looked into that because everything is GB so that little load shouldn't cause an issue on the network and I haven't heard of anyone else reporting issues on the servers in question. Good thought that warrants some looking.
Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2011 at 6:40 am
HowardW (10/3/2011)
Is the ADO .NET destination a SQL Server DB? Do you get different results if you change it to an OLE DB destination? You can still redirect errored rows with OLEDB, you just have to change it from being a Fast Load to a normal load.Edit: Removed another question after re-reading
Yes, the ADO.NET destination is SQL Server.
I have not tried using an OLE DB destination. I may have to try it to eliminate ADO.NET as the issue. I'm not sure why that would be the issue since it is a series of 1 row inserts, but...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2011 at 6:42 am
Jack Corbett (10/3/2011)
HowardW (10/3/2011)
Is the ADO .NET destination a SQL Server DB? Do you get different results if you change it to an OLE DB destination? You can still redirect errored rows with OLEDB, you just have to change it from being a Fast Load to a normal load.Edit: Removed another question after re-reading
Yes, the ADO.NET destination is SQL Server.
I have not tried using an OLE DB destination. I may have to try it to eliminate ADO.NET as the issue. I'm not sure why that would be the issue since it is a series of 1 row inserts, but...
Yeah, I agree - I was more suggesting it to work out what the real problem is - ADO .NET sometimes returns very vague error messages that may become more meaningful with OLE DB
October 3, 2011 at 6:46 am
HowardW (10/3/2011)
Jack Corbett (10/3/2011)
HowardW (10/3/2011)
Is the ADO .NET destination a SQL Server DB? Do you get different results if you change it to an OLE DB destination? You can still redirect errored rows with OLEDB, you just have to change it from being a Fast Load to a normal load.Edit: Removed another question after re-reading
Yes, the ADO.NET destination is SQL Server.
I have not tried using an OLE DB destination. I may have to try it to eliminate ADO.NET as the issue. I'm not sure why that would be the issue since it is a series of 1 row inserts, but...
Yeah, I agree - I was more suggesting it to work out what the real problem is - ADO .NET sometimes returns very vague error messages that may become more meaningful with OLE DB
I agree about the error message. What is vexing to me is that I'm running a trace at the same time and not seeing a better error message either.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2011 at 7:08 am
Jack Corbett (10/3/2011)
I agree about the error message. What is vexing to me is that I'm running a trace at the same time and not seeing a better error message either.
Could be a client side error like a TCP/IP message or something... I've had connections terminated by overly militant firewalls before when they encountered character data that triggered a rule 🙂
I think if it fails the whole lot, it's going to be something at the connection level that triggers the onError handler rather than just a problem with a single row...
October 4, 2011 at 10:17 am
HowardW (10/3/2011)
Jack Corbett (10/3/2011)
I agree about the error message. What is vexing to me is that I'm running a trace at the same time and not seeing a better error message either.
Could be a client side error like a TCP/IP message or something... I've had connections terminated by overly militant firewalls before when they encountered character data that triggered a rule 🙂
I think if it fails the whole lot, it's going to be something at the connection level that triggers the onError handler rather than just a problem with a single row...
It does appear to be a connection failure. Getting error number 2 which is a connection error. I'm getting this from a server side trace I'm running. Funny how it is intermittent though.
No events in any of the event logs on either the client or the server. May have to escalate to the operations team to look at.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 3, 2011 at 11:14 am
Jack Corbett (9/30/2011)
What happens is I will get an error for one row that says "An error has occurred while sending this row to destination data source."....
Oh, and I can immediately reprocess the rows without error.
This does not tell me it's a network issue. The first thing I would be checking is the datatypes in both source and destination, plus the records around the error. I've seen SSIS connection managers do funky things with the records, though this usually occurs on the Flat File connectors that misread what the delimiters or fixed width markers are supposed to be. (Fields get crammed together or offset to the wrong column).
In your case, I wouldn't use ADO.NET destinations. They get messy. I'd do a MultiCast for the first destination and send it to your three different dbs and the RowCount transformation. Do the redirect to an OLE DB destination (since you're sending it to SQL Server anyway). This way you will get a much more helpful error message when, and if, it actually errors out. Plus you can put data viewers on the connections to the D,C, & E dbs to verify what is actually moving to those databases.
Have you even looked at the data viewers to see what's going on?
November 3, 2011 at 11:59 am
Brandie Tarvin (11/3/2011)
Jack Corbett (9/30/2011)
What happens is I will get an error for one row that says "An error has occurred while sending this row to destination data source."....
Oh, and I can immediately reprocess the rows without error.
This does not tell me it's a network issue. The first thing I would be checking is the datatypes in both source and destination, plus the records around the error. I've seen SSIS connection managers do funky things with the records, though this usually occurs on the Flat File connectors that misread what the delimiters or fixed width markers are supposed to be. (Fields get crammed together or offset to the wrong column).
In your case, I wouldn't use ADO.NET destinations. They get messy. I'd do a MultiCast for the first destination and send it to your three different dbs and the RowCount transformation. Do the redirect to an OLE DB destination (since you're sending it to SQL Server anyway). This way you will get a much more helpful error message when, and if, it actually errors out. Plus you can put data viewers on the connections to the D,C, & E dbs to verify what is actually moving to those databases.
Have you even looked at the data viewers to see what's going on?
Thanks Brandie.
All the data types match and all the rows will process if I re-run them so it isn't a data type issue. Also, with any data issues I've always gotten the redirect to the error output. These rows are never getting to the destination to be redirected to the error output. If it were a data issue I'd see the attempt to insert on my trace and then an error. Also the if it were a data issue the row should redirect and then the next row should be processed.
It's been a while since I posted this so I don't recall using a data viewer, but I would bet I did because that's always my first step.
I've never used the multi-cast, I'll have to look at it as it might be a better way to handle this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 3, 2011 at 12:12 pm
Jack Corbett (11/3/2011)
I've never used the multi-cast, I'll have to look at it as it might be a better way to handle this.
Multicast is a funderful thing. Try it. You might like it. @=)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply