May 25, 2012 at 5:37 am
Hi All, hope you are having a good day thus far. 🙂
I'm a relative newb to SSIS (working with it just under a year) but I've currently started hitting a strange problem with a data flow task not loading all my rows.
Basically what I have is a Data Reader source that connects to a MySQL database (using ODBC connection), and populates a table on SQL Server Destination (2005). Recently I've noticed that rows are not being replicated.
When I run the SSIS package, everything looks fine, there are no errors, the data flow task says 407k rows loaded, but when I select a count there are actually only 350k populated in the table.
This has only started happening recently, I have no idea why. I've been searching around the net to find an answer, but have had no luck. It's basically a reporting server, so as you can imagine, missing rows doesn't do anything to help people's confidence in the data. :ermm:
Does anyone here have any ideas?
Thanks,
Adrian
May 25, 2012 at 9:19 am
I've exported MySQL data nightly to SQL Server table with no problems using SSIS and ODBC source.
Is it repeatably not loading all MySQL source rows? Is there a unique index (or something similar) on the destination table causing a problem? You're not ignoring errors on your OLE Destination (or whatever destination you're using)?
Rob
May 25, 2012 at 9:45 am
DO you have any non-default settings setup on the OLE DB Destination, e.g. "Ignore Failure" in the "Error Output" page?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 25, 2012 at 10:15 am
Thanks for your replies.
Em, Opc, no I don't have any ignores in there. They're all set to fail task on error.
I haven't yet checked your suggestions Rob, so hopefully they will shed some light on the problem over the weekend. Actually, Now I think of it, I noticed in some of my predecessor's data flows that she used the UNLIMIT keyword at the end of her selection from MySQL ODBCs. I always thought this was unnecessary. Maybe it makes a difference?
May 28, 2012 at 4:56 am
Hi All,
I'm just coming back on this topic to say I managed to get this loading all rows. I was originally using SQL Server Destination, but I read a post on MSDN suggesting that this was an unreliable data connector, and that to use the OLE DB one instead can frequently solve problems. As soon as I switched to the OLE DB one it worked as I expected it to. More of a workaround than a solution, I think, but at least it serves its purpose now. I was having the same problem with a different package also, but switching the destination to OLE DB worked here also, so it does seem that it was what was causing the problem.
Thanks for the suggestions,
Adrian.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply