Delete connection file

I've inherited a procedure that performs a few transforms and writes to flat files in a working directory using a connection in connection manager.

After the manipulation, the system cleans up by deleting the working files. Works fine in development, but from the command line gives:

The process cannot access the file <filename> because it is being used by another process.".

I suspect this is because connection manager still has the file open.

Any ideas?

Thanks

Guy

Yes, you are right.try closing the connection.or try a switch from command line to do it.|||Any ideas on how to close a connection? As far as I can see a connection is opened by reference to it, but I can't see any way to close a connection.|||

//release here

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

managedOleInstance.ReleaseConnections();

|||

So this would be in a script componenent after the last valid use of the connection and before the deletion I take it?

I would also probably need to force RetainSameConnection to use only one connection the whole way through.

|||

Can u show the code you have written?

Then i can favor you better.

Also, can u solve my problem?

I have a flat file with name "Employee.txt " (Full url: C:\Employee.txt ) .The File content is like this

Anil,Engineering,1997
Sunil,Sales,1981
Kumar,Inventory,1991
Rajesh,Engineering,1992

(Note: Items are Comma Seperated)

Now, i have a SQL Server database called "EmployeeDB" which has 2 tables "TblEmp1", "TblEmp2".
The Table is like this.

TblEmp1 : Columns
EmpName EmpDept EmpjoinDate

TblEmp2 : Columns
EName EDate Edept

using integration services (SSIS) i need code to Create a dtsx package so that i can push the flat file content to these 2 tables.
And the condition is :

After Executing the package Data loaded in TblEmp1 should be like this

EmpName EmpDept EmpjoinDate
Anil Engineering 1997
Sunil Sales 1981
Kumar Inventory 1991
Rajesh Engineering 1992

(No change in order compared to source)
And Data loaded inTBLEmp2 should be like this

EName EDate Edept
Anil 1997 Engineering
Sunil 1981 Sales
Kumar 1991 Inventory
Rajesh 1992 Engineering

Now, i know that we need to do like this in wizard
1) Create a flat file source component.
2) Create flat file connection and set the properties of flat file (delimeters and other things)
3) Create a Multicast Component.
4) Create a Path between Flat file source and Multicast.
5) Create 2 destination component(each for a table).
6) Create path from multicast to 2 destination components
7) Create a OledbConnection and set table names for 2 destination components..
7) Now,i have to do mapping for destination1.
8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1.

I have done it in wizard.I need to do it through code and i know that its not complicated.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!!

Please Get back ASAP today.
Thanks in Advance,
Anil Kumar MS

|||

Sorry mate, our days in Sydney obviously end earlier than wherever you are...

Firstly, I don't have code, just components. I've already outlined the relevant bits of what happened.

Secondly, with respect of your problem. Is there a constraint linking the two tables? If not, what you're doing looks just dandy. If there is, you may need to change your order or temporalily relax the constraint.

If its just a question of mapping why not just use the mapping tab on the destination?

Regards