SSIS issue -Urgent

[venu_23]

Are Excel files handled differently by a File System task? 

I have a dtsx package that  uses an Excel(.xls) file. I've found many tutorials and posts about moving files with a data flow task, but nothing seems to address my specific problem.

The package is supposed to loop through the tabs of an xls file, extract data, and then move that file to an archive folder and rename with date format. 

A Foreach Loop container gets a collection from a network drive and opens each file with an EXCEL Connection Manager. The Connection manager is then used by a series of Data Flow tasks that get data from the tabs of the spreadsheet and insert them into a SQL Server db through another Connection Manager object.

Next, it is supposed to move the file to an \Archive directory when it is finished. I'm trying to use a File System Task to accomplish this, but it fails. 

The EXCEL Connection Manager does not appear in the Source Connection drop-down list as the flat file connections do in my other packages. So I have a variable that uses an expression to build the path\filename to use as the source. If I disable all of the Data Flow tasks in the Foreach Loop container, it will move/rename the file as it should. So it's not a permissions issue or a bad filename/path in the variable. 

But when the Data Flow tasks are enabled, it goes through them and then fails on the Dft with an error message that says it can't move the file because it is in use. There are constraints on the Data Flow tasks so each runs in series and then the File System task is after they are all done.

Is there something I need to do force it to close the connection and release the file after the last Data Flow?

Is there some other work around?

Thanks,

[krishna0125]

I think I faced similar kind of issue sometime in back even with flat files in file system task. I am not able to recollect it now. make sure you check issourcepathvariable as false and create new connection(sourecconnection) from file system task and it properties of that connection object use expression to get the path and file name from foreach loop. it may work.

[loveindia]

did you try containers? by having all DFT in one container and after that use the task to move data. ? weekend roju no work mood man... connection string asalu variable ga avutunda ani question?

[sqladdict]

Script task raskora ......sambaaa