Commit in SSIS after each file process

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi All,

I am processing multiple files using forecah loop in SSIS. Could some body will say how the commit will happen? If I wanted to commit after each file process completes successfully, what changes should be done.

Thanks,
Srinivas

Posted 19-Dec-13 1:04am

cns1710

Updated 19-Dec-13 1:05am

v2

Add a Solution

Comments

coded007 19-Dec-13 9:42am

Please explain us clearly what you want to commit ?

cns1710 19-Dec-13 22:13pm

Assuming there are 3 files to process. 1st file processes and loads the data into table successfully, 2nd file fails data validation and moved to error directory and 3rd file also validates data successfully and loads the data into table. In OLE DB Destination, Maximum Insert Commit Size is 2147483647.
My question is - once the job gets over, will the data loaded by 1st and 3rd file remains in table or will it rollback?
Actually, I ran multiple times with multiple files, most of the times I was seeing valid files data in table. Few times I was not able to see any data in table.

coded007 23-Dec-13 6:49am

It will not roll back if you are following same process as you explained? check whole package once where it is going to back

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-12-19T09:32:00

Solution 1

Truly? I have no idea, because you did not specify what have you done till now.

Have a look here: Foreach Loop Container[^]

Posted 19-Dec-13 9:32am

Maciej Los

Comments

cns1710 19-Dec-13 22:15pm

Sorry, I am new to SSIS. I was doing some enhancement in d existing pkg.
Assuming there are 3 files to process. 1st file processes and loads the data into table successfully, 2nd file fails data validation and moved to error directory and 3rd file also validates data successfully and loads the data into table. In OLE DB Destination, Maximum Insert Commit Size is 2147483647.
My question is - once the job gets over, will the data loaded by 1st and 3rd file remains in table or will it rollback?
Actually, I ran multiple times with multiple files, most of the times I was seeing valid files data in table. Few times I was not able to see any data in table.

Maciej Los 20-Dec-13 1:46am

I have no idea why it happens. There are known issues with files loading. I would suggest to search via Google[^].