Hi! I am encountering an issue when inserting rows into 2 databases. I have to insert about 5000 rows and all that must be done in transaction. Here is my package description : In a Sequence Container (with transaction required)In a Foreach loop : Insert a row into database1 Get the ID of the inserted row Insert a row into database2, the row contains the id of the inserted row in database1 When it begins, it works well. But after a while, the package slows down. After 70% of the rows inserted, the package fails because the SQL Server has refused the connection. I guess this is because I am inserting too much stuff in transaction and the server cannot handle it. I even have made the server to crash once. Now I wonder how I can do that, because I cannot do this out of transaction. I must be sure that all the records are inserted in both databases and commit all that in the end. Can you help me ? Thank you. Best regards.Matteo, .NET Developer and System Engineer

Are both databases on the same server? If yes, you can define a trigger on the first destination table that inserts all the inserted rows into the second table, together with the IDs of the first table. If the source is also on the same server, you can all write it into one TSQL statement using the OUTPUT clause.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Maybe it slows down because of the transaction log (which you need). Try to shrink the log file ion an Execute SLQ task before the insert starts. e.g. DBCC shrinkfile (databaselogname, 123) Jan D'Hondt - Database and .NET development

Do not just advice people on the net to shrink their transaction log! It has very important disadvantages, such as losing the ability to do a point in time restore. http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/ The reason the whole package blows up because the rows are inserted row by row. Dealing with the problem in a set based manner will solve all issues, without ruining the transaction log chain.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, do you really need to do it using a Foreach loop? Can't you just insert rows in database1 (using Data Flow task) and then insert those rows in the other database? Both Data Flows should be inside the sequence container and you should find some way to get only the newly inserted records from database1 (e.g., using some timestamp or joining with the source - based on some unique id field). I think the issue here is that each step in the Foreach is creating a new connection and then it reaches the limit. Besides the performance issue of processing row by row... David.

Another reason why regularly shrinking transaction logs is bad (by Paul Randall): http://technet.microsoft.com/en-us/magazine/hh395481.aspxMCTS, MCITP - Please mark posts as answered where appropriate.

Dear Koen, That is why I wrote (which you need). In some case you do not need a transaction log: for instance, to collect historical data in a datawarehouse, during off-peek times. Jan D'Hondt - Database and .NET development

Dear Koen, That is why I wrote (which you need). In some case you do not need a transaction log: for instance, to collect historical data in a datawarehouse, during off-peek times. Even if you don't need a point in time restore, the transaction log is still always there and truncating it can have performance issues, as explained by the article of Paul Randall.MCTS, MCITP - Please mark posts as answered where appropriate.

Are both databases on the same server? If yes, you can define a trigger on the first destination table that inserts all the inserted rows into the second table, together with the IDs of the first table. If the source is also on the same server, you can all write it into one TSQL statement using the OUTPUT clause. MCTS, MCITP - Please mark posts as answered where appropriate. Hi, Actually yes, the databases are in the same server, but I cannot modify the structure. So... No triggers. Actually, the problem is about the transaction, not the database structure. Regards.Matteo, .NET Developer and System Engineer

Hi, do you really need to do it using a Foreach loop? Can't you just insert rows in database1 (using Data Flow task) and then insert those rows in the other database? Both Data Flows should be inside the sequence container and you should find some way to get only the newly inserted records from database1 (e.g., using some timestamp or joining with the source - based on some unique id field). I think the issue here is that each step in the Foreach is creating a new connection and then it reaches the limit. Besides the performance issue of processing row by row... David. Actually, if I don't use a transaction, it works well. I am using a foreach loop because I have to get the id of the insterted row back. In the dataflow, if I insert all the data one shot, I cannot get the ids. Example : I must insert : Field1 Field2 ------ ------- a b b c a b c c As you can see, a-b must be inserted twice. But I must know wich one is the first and the second that was inserted. In other words, I must get the unique identifier back. Then I use it (the identifier) to reference that record in another database. Regards. Matteo, .NET Developer and System Engineer

I don't understand why you need to do this row by row. Please tell me what the difference is between the first "a, b" row and the second one? How can you (or your system) tell if you've "picked the wrong one"? Talk to me now on

.... But I must know wich one is the first and the second that was inserted. In other words, I must get the unique identifier back. Then I use it (the identifier) to reference that record in another database. Regards. Matteo, .NET Developer and System Engineer I get it, you're a .NET developer and you're used to do stuff the procedural way. However, SQL Server is build to work with data in a set-based manner and SQL itself is declarative. I already mentioned you could use a trigger or an output clause, depending on your configuration. The configuration you have now is suboptimal and there are surely much more efficient ways to reach your requirements.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, Actually yes, the databases are in the same server, but I cannot modify the structure. So... No triggers. Actually, the problem is about the transaction, not the database structure. The problem is not the transaction, but the row-by-row insert resulting in too much individual transactions. If you insert all the rows at once, you only have one transaction.MCTS, MCITP - Please mark posts as answered where appropriate.

You were right. I change the whole package using script transformations and other components to do the businsess logic and then insert the rows using an OLEDB destination. It is much faster! Thank you for your help! Best regards.Matteo, .NET Developer and System Engineer