January 13, 2015 at 2:16 pm
HI -- I have a package that runs fine in Sql Server 2008R2. Now, I'm recreating it in Sql Server 2005 to fit a different server. I'm hitting a problem that I cannot find a ready solution for.
The package has a For Each Loop to Bulk Insert many tables from a MySql server to SQL Server. (I can't go directly since our DBAs won't allow MySQL drivers in the SQL Server environment -- sucks, but I'm stuck with that)
So the Loop looks like this:
Execute Process: MySql command to extract table contents and dump to a flat file
SQL Command: Truncate the target table
Bulk Insert task: Load the Sql Server table
Note that this sequence works fine on Sql Server 2008R2 through 2014.
Here's where I am stuck;
Everything runs fine until the Bulk Insert task. It begins, turns yellow, but never finishes. I'm running profiler at the same time and I can see that it never sends the Bulk Insert command to the server. No error messages or warnings are issued. It just sits there.
I turned on auditing to try to get a better picture. It's curious. I only see this for the Bulk Insert Task:
OnPreExecute...1/13/2015 4:11:14 PM,1/13/2015 4:11:14 PM,0,0x,(null)
OnPreValidate...1/13/2015 4:11:15 PM,1/13/2015 4:11:15 PM,0,0x,(null)
OnPostValidate...1/13/2015 4:11:15 PM,1/13/2015 4:11:15 PM,0,0x,(null)
(I cut out the guids and other stuf).
So, the task does not make it past OnPostValidate. No "Executing" or "OnProgress" or "OnPostExecute". Nothing. It just stops and stays yellow until I cancel it.
Anyone seen this and found a solution?
Gerald Britton, Pluralsight courses
January 13, 2015 at 2:37 pm
Its possible its trying to run the whole kit and caboodle in one transaction. I don't remember 2k5 well enough to know when it commits, or where you can force it to do that.
Can you use bcp as a workaround in the 2k5 environment
January 14, 2015 at 8:40 am
Manic Star (1/13/2015)
Its possible its trying to run the whole kit and caboodle in one transaction. I don't remember 2k5 well enough to know when it commits, or where you can force it to do that.
Good thought. Note however, that the BULK INSERT command is never sent to the server in this case. So, even if the TRUNCATE TABLE were wrapped in a transaction, the server is not seeing the BULK INSERT command. I think this is an issue with BIDS
Can you use bcp as a workaround in the 2k5 environment
Perhaps, but I found a better (for me) way:
I simply used another Execute SQL task and built the BULK INSERT command myself. Works like a charm!
Still a mystery about the Bulk Insert Task, though. Wish I knew the secret there!
Gerald Britton, Pluralsight courses
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply