My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?
Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1
Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.
Regards,
MS Guy
MS Guy wrote:
Can we have a folder as input path for SSIS package, and process all files within it? My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?
Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.
Regards,
MS Guy
There's a few things that make the answer "It depends" but there's a few things you should look at:
1) MULTIFLATFILE connection manager. This connection manager points to a colelction of files (usually all files in a folder) and you can then apply a single operation (such as loading the contents of all those files into a table) in one step. Usually the metadata of the files needs to be the same - it depends on what operation you're applying.
2) Use a Foreach loop container to loop over the files and process them individually. There's a bit more guidance on this here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx plus Darren and Allan have some good stuff on this at www.sqlis.com.
-Jamie
