July 5, 2008 at 4:18 pm
Hi all,
I am looking for insights and opinions on an issue I am having.
I have about 30 tables in MySQL v5.0.5x that I am migrating data from, into a DW on SS2K5. I am using SSIS to do the ETL, and on my 32-bit machine this was working just fine. Now that I have built out the 64-bit production server, I am running into an error that appears to be emanating from MySQL (via their ODBC connector) to the effect of, "Error in my_thread_global_end(): 1 threads didn't exit."
Not sure why this wasn't happening in the 32-bit version, but I discovered it in 64-bit because, and this is where I am looking for input... I have 30 SSIS packages, one for each table, to do the ETL. I run all of these as an Agent job, in series. On the 32-bit box, they run flawlessly. Under 64, I get to the 10th package and it hangs... no error, just runs for 10+ hours and never comes back. If I run the package that hangs by itself, it completes successfully in less than 30 seconds.
Then I tried running the job starting on step #11 (skipping the offending step 10), and it ran through 4 steps, and hung again. That was when I noticed the threading error, which makes me think that the system is stuck awaiting free threads, or something along those lines.
First off, does this sound reasonable as far as why this is happening?
Second off, my two solutions for this problem are, either break up the big job into a series of smaller chunks of 2-4 jobs each and run them in succession. The thinking being that the completion of a job would automatically free up the threads (or reset the thread counter, if that is truly the problem). OR... I was thinking of making one monolithic SSIS package that incorporates all 30 ETLs for each table and running that one package as a job. Thinking that perhaps the threading issue is being compounded by the number of packages I am trying to run.
The monolithic idea seems like it could be a good way to go, but it also seems that I will lose some traceability and auditing info.
Does anyone have an opinion on how to proceed, or, even better, on why I am having this problem in the first place?
Thanks!
Chris
July 6, 2008 at 8:15 am
Hey Chris,
Looks like the threads not being closed has been a problem for other people though best as I can tell, may have been fixed (see this mySQL bug report). Am hoping you may see a fix for your particular version in there.
In the meantime, if it definitely *wasn't* failing on 32-bit, as a stop-gap measure you could try executing the packages in 32-bit mode, there should be a dtexec.exe in both the 32 and 64 bit program files directories.
Steve.
July 6, 2008 at 8:39 am
Hi Steve,
Thanks for the link. I actually saw that in my research but it seems that they integrated it into early 5.x versions, yet others have had it crop up in 5.0.37.
However, I have actually isolated the problem to two packages. Everything else runs fine. So now I am convinced I was wrong in assuming it to be a thread issue.
But now that leaves me with another dilemma. All 30 of my packages are simple SELECT * FROM table in MySQL, transform the unicode strings, and then drop the data into an identical table in SQL Server. I have re-created the two offending packages a few times now, but the continue to fail. Why would those two do that? One pulls about 8000 rows, and the other 50000. Several other working packages are pulling several hundred K to several M rows with no problem.
Any ideas?
I will try to run them in 32-bit mode to see if that helps, but it is just weird that these two would do it when others wouldn't.
Lastly, what is your opinion on the monolithic SSIS package? Is that a good or bad idea?
Thanks!
Chris
July 6, 2008 at 8:59 am
Do you write a ErrorLog or something from the SSIS Packages and JOB Likewise, this will help in squaring into the RECORDS, Tasks failing strategies.
Maninder
www.dbanation.com
July 6, 2008 at 9:16 am
Hi there,
You mean on the Advanced page of each step? I have not tried that... wasn't sure if it would be helpful.
Will give it a go.
Thanks,
Chris
July 6, 2008 at 9:26 am
Nothing gets written to that file. I am executing only the offending step, and I have to manually stop the job.
Any other logs I can check?
July 6, 2008 at 10:03 am
Try to select all the Option in Logging, if it doesnt write anything..
If it appears Deseected, try going to Control Flow Windows and RightClick in the window in empty Space and Click Logging and If it appears greayed out, check the DataFlow and Check again, until it is enabled.. on the right side check the DataFlow again, (Creates a Configuration File) then select every task Option to LOG.. and check.
Maninder
www.dbanation.com
July 6, 2008 at 10:22 am
Thanks, Mani. Will give it a go, and get back with results.
Cheers,
Chris
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply