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).

ODBC Source in Data Flow Task in SSIS

ODBC Source is used to extract the data from the ODBC supported database. Like Postgre database or Oracle database. We can use table, view or SQL statement to extract the data from the database.

In the ODBC source either we can extract the data Row-by-Row or Batch mode.

Below is the ODBC Source

For the ODBC Source 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

Creating the ODBC Connection Manager and using this connection manager in ODBC Source. 

                 

Some important property of ODBC Source

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

Data access Mode:

Table Name: Here we are selecting the table or view name from where we want to pull the data. When we will click on the Name of the table or the view we will able to see the view name or table name. If our database having more than 1000 table in that case we may not able to see the table or view name in this case we can write the table name.

SQL command: Here we need to write the SQL script, or function or SP to pull the records from the source.

Some other we can see in the advance editor.  

Right click on the task we will see the Show Advance Editor.  

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.

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.