ODBC Destination in Data Flow Task in SSIS

The ODBC destination is used to bulk or row by row data load into the ODBC Supported database tables. It uses ODBC connection manager to connect the destination.

For the ODBC destination we need to create ODBC connection manager.

Read here Step by step how to create the OBDC connection manager:

https://bageshkumarbagi-msbi.blogspot.com/2022/01/creating-odbc-connection-for-postgresql.html

Generally we are not able to find out ODBC Destination on the Other Destination pan.

  

 We will see it into the Common  

Taking ODBC Destination and configuring it using ODBC connection manager.

Some important property of ODBC Source

ODBC Connection Manager: here we will create or add the ODBC connection Manager.

Data access mode:

Table Name – row by row: if we select this option record are inserted one row at a time.

Table Name – Batch: If we select Batch we need to provide the batch size to insert records in to the destination table. 

If we select batch and provider doesn’t support this method at the ODBC destination in this case automatically switches to Row by row mode.

Other important properties we can see in the Show Advance tab. 

Batch Size:  Here we can give the size of the batch for the bulk extraction. If selected ODBC provider does not support the array in that case batch size will by 1.

Insert Method:   it will be 0 if we select Data access mode as table name – row by row and 1 if table name – batch.

LobChunksize :  Determines the chunk size allocation for LOB column. By default value of this property is 32768.

StatementTimeout :  Number of seconds to wait for an SQL statement to execute before returning to the application. The default value is 0. A value of 0 indicates that the system does not time out.

TransactionSize : In this property we can set the transaction size(Number of rows inserted under the same transaction).