OLEDB

• OLE DB (Object Linking and Embedding, Database), an API designed by Microsoft, allows accessing data from a variety of sources in a uniform manner.
• SQL SERVER,DB2, ORACLE
• PREFER use sql server authertication

OLEDB Source:

• OLE DB source extracts data from a variety of OLE DB-compliant relational databases by using a database table, a view, or an SQL command.

• For example, the OLE DB source can extract data from tables in Microsoft Office Access or SQL Server databases.

• Access Modes in OLEDB Source are:
  A table or view.
  A table or view specified in a variable.
  The results of an SQL statement. The query can be a parameterized query.
  The results of an SQL statement stored in a variable.

OLEDB Destination:

The OLE DB destination loads data into a variety of database table or view or an SQL command.

For example, the OLE DB source can load data into tables in Microsoft Office Access and SQL Server databases.

The OLE DB destination provides five different data access modes for loading data:
A table or view. You can specify an existing table or view, or you create a new table.
A table or view using fast-load options. You can specify an existing table or create a new table.
A table or view specified in a variable.
A table or view specified in a variable using fast-load options.
The results of an SQL statement.

error output

• fail component(default)
• ignore failure : it will ignore failure and keep execute
• redirect row: it also ignore failure ,but give you the position of failure

input method: difference in the OLEDB destination

because there are two method to insert data: row by row and rows per batch

image.png

eg: if we have 1000 rows,and seperate 2 batch, it means one batch has 500 rows, but it maybe exceed the maximum insert size, so it will stop maybe on 300 rows

• rows per batch VS maximum insert commit size:
  只要有一个先满足条件，就不能继续插入了

difference between data access model

a table or view VS fast load

• a table or view： row by row loading will cause problem: 如果中间有不符合格式的，根据自己设置的error处理方式，会有不同的结果
  fail component: it will stop(?)
  ignore failure : it will ignore failure and keep execute
  redirect row: it also ignore failure ,but give you the position of failure

• fast load :batch loading 错误的那个batch会roll back eg: 20个插入,59发生问题，errorfillment(default)结果是1-40
  其他的结果就是1-40;41-58;60-