Can multiple script components execute asynchronously?
-
I have a package that imports Excel files. The import is complicated and is done using a script component as a source within a ForEach container. The script opens the Excel file using the Excel interop library, and adds rows to the data flow as necessary. Each file generates about 1300 new records in the destination SQL Server table. There are about 640 files to import each time the package is run. Because of the number of files that must be imported, I have tried to create multiple "threads", each thread dealing with a different group of files (thread 1 does files beginning with A-F; thread 2 G-K, etc.). In all I have 5 nearly identical threads that should be executing simultaneously.
When I run it, the threads appear to "take turns" rather than running at the same time. I'm not sure I'm saving any time over having a single thread deal with all the files. Is this an illusion, or is there some characteristic of script tasks and/or the Excel library that does not like to execute asynchronously?
-
If you look at the properties of the Execute Package task you'll seee there's a property for ExecuteOutOfProcess (Excute in a different thread). I do not see that property for the Script task which make me believe that you cannot force it to run in a separate thread.
You may be able to improve the situation by putting the scripts in separate packages and setting ExecuteOutOfProcess to TRUE.
Looks like your options are limited.
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
While not the answer I was hoping for, it's a good idea. I'll give it a shot. Thanks.
-
Hi David,
Why not try to export the Excel file content into CSV file and then use script to process this text file? You will still need Excel to do the "Save As CSV" piece, but you will load the Excel application only once and do the "Save As" without unloading Excel every time. This should give you huge boost in performance.
My point is that when you use Excel interop you are in fact loading a whole Excel application in memory. Every time you load/ unload the Excel application the server memory will fragment . I know Microsoft did improve the memory fragmentation issue in the latest server OSes , but still the Excel is so complex and such a resource hog that loading Excel 600 times in memory will have an impact on the environment for sure. If you can minimize this, that would be beneficial.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
I agree that using the Excel library is not desirable. I have had numerous issues with it. It is odd that an app as mature as Excel could interact so poorly with other products from the same company. Unfortunately, I'm not sure that processing the files as csv would be possible. The format of the files does not have a simple "rows and columns" data area. Data is plucked from various arbitrary cells based on the value of other cells. Also, the processing involves reading certain Excel-specific characteristics of the file (such as row.hidden) that would be lost after converting the file to csv. But if I could find workarounds for those, it might be worth a try. Not using the Excel model would be worth a lot.
This whole thing also highlights limitations in SSIS. The import process was originally written in Access 2003, and there it works like a champ, albeit slowly. The present exercise to "enterprise" it has produced so many gotchas that I'm almost ready to leave it in Access. Things that should be simple (like dividing a directory of files into A-F, G-K, etc.) require jumping through hoops that should be unnecessary. Any why can't script components in the same package execute simultaneously!? Granted, I'm not an SSIS professional, but I've spent the better part of the last year working mainly in SSIS, and it should be easier than this. This product still has a long way to go, in my view.
Sorry for the rant, but I needed to get that off my chest. I feel better now.
Anyway, thanks for the suggestion. I'll certainly give it a look.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply