Commit in for each loop

[NetNewbie]

Hi,

Can anyone tell if the following is possible is SSIS?

I have a set of files each one 20 to 25 GB. I am loading these through SSIS
into 1 table using a dataflow task.

The problem is after the files are loaded successfully, the commit failes
due to lask of space, and I ended up loading 2-3 files at a time.

Other than the option of increasing space or temp GB size, I want to know
how can I force SSIS to commit the data to the table after each file.

So if I put the dataflow task inside a foreach loop, it is not helpful,
because

The for each file enumerator is \\path\*.out* - so it loops for each file.
The dataflow inside the loop has a flatfile source with a multiflatfile
connection manager which also has the path set to \\path\*.out*.

It is obvious that the above is not proper, how do I pass the filename from
the for each file loop, to the flatfile source connection manager, so that
the dataflow happens once for each file ?

Inside the for each loop I want something like

Execute begin transaction - SQL task

dataflow -- flatfile source to OLE DB destination

Execute commit tran SQL task

I can't figure out how to do this.

Thanks,
Mahi

[Charles Hyman]

Hi

This is pretty easily accomplished.

You can pass the filename using a variable in SSIS.

You can also use checkpointing so that the package can continue from
where it left off after the space problem is solved.

Let me know if you need more info.

Charles Hyman

Post by NetNewbie
Hi,
Can anyone tell if the following is possible is SSIS?
I have a set of files each one 20 to 25 GB. I am loading these through SSIS
into 1 table using a dataflow task.
The problem is after the files are loaded successfully, the commit failes
due to lask of space, and I ended up loading 2-3 files at a time.
Other than the option of increasing space or temp GB size, I want to know
how can I force SSIS to commit the data to the table after each file.
So if I put the dataflow task inside a foreach loop, it is not helpful,
because
The for each file enumerator is \\path\*.out* - so it loops for each file.
The dataflow inside the loop has a flatfile source with a multiflatfile
connection manager which also has the path set to \\path\*.out*.
It is obvious that the above is not proper, how do I pass the filename from
the for each file loop, to the flatfile source connection manager, so that
the dataflow happens once for each file ?
Inside the for each loop I want something like
Execute begin transaction - SQL task
dataflow -- flatfile source to OLE DB destination
Execute commit tran SQL task
I can't figure out how to do this.
Thanks,
Mahi