Can anyone suggest, Is it a best practice / recommend to use SQL command access mode inside OLEDB Destination in SSIS.?? What could be the pros and cons of this access mode. Thanks in advance.
1
-
See here: stackoverflow.com/questions/17144405/…Tab Alleman– Tab Alleman2018-12-07 15:57:11 +00:00Commented Dec 7, 2018 at 15:57
Add a comment
|
1 Answer
You will be more likely to obtain better performance using the Table or view - fast load option as opposed the using a SQL command. By default, using the SQL Command will not take advantage of the same features as the fast-load option. By using fast-load this will internally work as a BULK INSERT. This option also obtains Exclusive (X) page locks and Intent Exclusive (IX) table locks, while the SQL Command option will only take Shared (S) and Schema Stability (Sch-S) locks on the respective objects. You can confirm by viewing these locks either through sys.dm_tran_locks or an Extend Event, such as the lock_acquired event. If you're using an Extended Event, to make your transaction easier to distinguish when doing this set the Application Name property in the connection string and look for this. The X table locks will prevent others from accessing the table during this operation and the potential blocking is something that you will want to weigh when considering both options.
If you're destination is a SQL Server table or view, The SQL Server Destination is recommended. However this option is only for loading to a local destination and this restriction will need to be considered when planning for long term development. So if you current destination is local, but several years from now this may change then then OLE DB Destination will be the better option. Similar to the fast load option of the OLE DB Destination, this will utilize the BULK INSERT capabilities, however SQL Server Destinations have the advantage of applying transformations to column data prior to loading the destination table. The SQL Server Destination can avoid the table locking of the OLE DB destination by setting BulkInsertTabLock to false. I've found that using the SQL Server Destination tends to perform better than the OLE DB Destination, however you should verify this using a test environment the specifically mimics when the process will be performed the assess the results yourself. The MSDN reference for destinations types is below.
1 Comment
Yahfoufi
I have found many article and posts online that don't recommend using SQL Server Destination such as stackoverflow.com/questions/47388925/… and dba.stackexchange.com/questions/228720/… even if Microsoft recommend that. In addition, i tried using this component it is awful since it is always throwing meaningless exceptions. I prefer using OLEDB Destination with fast load