question

rahulmehta08 avatar image
rahulmehta08 asked

SSIS Package Optimization for thousands of excel files and archival on the basis of business logic

I have developed a scenario where ssis package will retrieve the excel file name using foreach loop and search whether that file name is part of business logic. If it isn't I need to archive that, I have done that using File system task.Now the problem here is, for each loop is taking approximately 5 seconds for each file.Now there are thousands of fies in the folder how can i optimize the code to run faster? I have developed SSIS package containing a procedure will run and then inside foreachloop there is execute sql task to identify whether that filename is part of business logic if no then i am passing flag as 0 and if yes flag as 1.And then I used file system task to move the file to the archive folder.
sqlssisc#
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Is this a one-off thing that's not urgent? If so, just let it take a while. Who cares? Otherwise I think you need to identify which part is taking the time so you can improve that part. You could manually move the biggest file to see how long that takes. You could test calling the stored procedure and time that. You could run the For Each file loop with the processing part commented out or disabled to see if iterating over a lot of files is the problem. Once you know where the time is being used up, you will have a better idea of what you need to do.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
Another option would be to introduce parallel processing into the package. SSIS is great for that. In other words, if the files are able to be broken up into chunks in some fashion (iE: name, created date, modified date, etc), then you could have multiple foreach loops running against their respective collection of files. Don't know if the files could be segmented like that but it's an option.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.