היום שאלו אותי לגבי bulk insert ע"מ להקטין את הלוגים שנוצרים
בטעינות.
קודם כל צריך להבין שכדי לעשות זאת יש להשתמש ב sql server destination . הסברים בלינק:
אם אתם עושים זאת ע"מ להקטין את הלוג אז יש לי כמה המלצות:
Minimize logged operations.
When you insert data into your target SQL Server database, use minimally logged operations if possible. When data is inserted into the database in fully logged mode, the log will grow quickly because each row entering the table also goes into the log.
Therefore, when designing Integration Services packages, consider the following:
•Try to perform your data flows in bulk mode instead of row by row. By doing this in bulk mode, you will minimize the number of entries that are added to the log file. This reduction will improve the underlying disk I/O for other inserts and will minimize the bottleneck created by writing to the log.
•If you need to perform delete operations, organize your data in a way so that you can TRUNCATE the table instead of running a DELETE. The latter will place an entry for each row deleted into the log. But the former will simply remove all of the data in the table with a small log entry representing the fact that the TRUNCATE occurred. In contrast with popular belief, a TRUNCATE statement can participate in a transaction.
•Use the SWITCH statement and partitioning. If partitions need to be moved around, you can use the SWITCH statement (to switch in a new partition or switch out the oldest partition), which is a minimally logged statement.
•Be careful when using DML statements; if you mix in DML statements within your INSERT statements, minimum logging is suppressed.
