June 18, 2008 at 3:10 am
Hi,
I've got a huge trouble using SSIS.
I need to make several outer join on differents data sources, and as the merge join doesn't seem to work fine with Nvarchar key columns, I need to convert them in varchar and to sort them after in a sort component (the convert(varchar, ...) doesn't seem to work either in a query).
So, I've got 8 OLEDB data sources, and 3 sort components (the sort orders are different between some data sources). The SSIS job works fine with less than 100.000 rows, but when it ran with more than 180.000 rows, the jobs hangs on a sort component. Apparently, the cpu doesn't seem to be overloaded, and I've got to kill the process after long time.
If anybody has already encounter the same issue, or if anybody has an idea to solve this, I would appreciate.
Thanks a lot by advance
Regards,
Sébastien
June 18, 2008 at 12:30 pm
Sebastian,
Enable logging on the package to see where your problem might be. Also, consider running Profiler while running the package.
Lastly, have you tuned your initial queries? It might speed things up.
Link to better SSIS Data Flow performance: http://technet.microsoft.com/en-us/library/ms141031(SQL.100).aspx This may or may not help.
SQL might not be hanging so much as it's taking forever to process. There's a difference. And check the Progress tab while you're debugging to see where in the SORT process the package is "hanging".
Lastly, Sort has a MaximumThreads property. Play with that a little and see if it helps or hinders.
June 18, 2008 at 12:53 pm
NSort for SSIS might be of interest too. I haven't used it but the posts and articles I've read have been positive.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply