June 9, 2020 at 1:33 pm
I have a data flow task that should be moving around 16k records from a SQL 2012 instance over to a SQL 2005 instance. However, the task never completes. The source is a query... and if I limit the query to top 25 the task completes.. however, if I allow the source to return more than 25 or so rows the task never errors, and never completes. I have tried several drivers, and several parameters (batch size, insert commit) but the result is the same. It never errors, and never completes... and no rows are moved to the destination. If I change the destination of the package to another instance, it runs fine.. so it is something either on the destination server, or on the network segment between... However, troubleshooting efforts up until now have produced no issue.
June 9, 2020 at 1:40 pm
Are you running this in Debug mode in Visual Studio?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 9, 2020 at 1:42 pm
I have run it via debug, as well as via a sql agent job. Same results.. Never errors, never completes.
June 9, 2020 at 2:28 pm
"If I change the destination of the package to another instance..."
Is that another 2005 instance?
Is there anything going on in the data flow, or is this a straight source --> destination job?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 9, 2020 at 2:41 pm
It's just a straight source -> destination task. If I add " top 25" to the query, it succeeds. It only hangs when the row count increases.
June 9, 2020 at 4:32 pm
It's just a straight source -> destination task. If I add " top 25" to the query, it succeeds. It only hangs when the row count increases.
OK. You answered one of my questions, what about the other one?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 9, 2020 at 5:31 pm
The source is SQL 2012, and the destination that is having trouble is SQL 2005.
As a test, I have re-pointed the package to another SQL 2012 server, and it succeeds in less than 20 seconds.
June 9, 2020 at 6:11 pm
Experimenting with different options... if I change the Data access mode in the OLE DB Destination to "Table or view" instead of "Table or view- fast load" it does load the data.. albeit in batches of 20 rows (when this access mode is chose: the batch size is no longer configurable)... Also FYI: I am using SQL Server Native Client 11.0 as the driver.
June 9, 2020 at 6:18 pm
It's been so long since I've had to deal with 2005 that I cannot remember any of its idiosyncrasies, so I don't think I can assist further with this one.
You might also try using an ADO connection, as you are experimenting.
At your next planning meeting, I'd suggest putting 'SQL Server Upgrades' on the agenda. Even 2012 is starting to look long in the tooth now.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 9, 2020 at 6:34 pm
It sounds like a problem on the destination - I would suspect something is blocking the insert into that 2005 instance once you exceed a certain number of rows.
Are you inserting into a staging table - or to a final table? If it is not to a staging table - have you tried using a blank table with no current rows as the destination?
In debug mode - do you see all rows as being selected from the source, and the hang occurs only on the destination? Or - does it select ~25 rows and then hang?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply