The OLE DB Source component

Steps  to create a simple package in SSIS

STEP 1:-

STEP 2:-

STEP 3:-

STEP 4 Working With Control Flow:-

STEP 5:-

STEP 6:- 

STEP 7 Working With Data Flow

STEP 8 Working With Data Flow Sources:-

STEP 9:-Working With Data flow Sources OLE DB  source connection

STEP 10:-

STEP 11:-

STEP 12:-

STEP 13:-

STEP 14:-

STEP 15:-

STEP 16:-

STEP 17:-

Data Access Mode

The data access mode is the means by which the OLEDB source component determines what query to send through to the underlying database to retrieve data.  This can either be hardcoded in the component or populated using a variable.  It is specified using the drop down circled in the image below.

There are four are available data access modes:

• Table or view
• Table or view name variable
• SQL Command
• SQL Command from variable

You will note that two of the available data access modes are essentially duplicates that allow the data access mode to be populated from a variable.  Each mode is demonstrated in the sample packages, but I will only discuss the basic (i.e. non variable populated) modes below.

Data Access Mode: Table or view

The table view data access mode allows you to select the entire contents of any table or view within the database that you have connected to.  However, it does not allow for any constraints on the data that is retrieved, and if you wanted to restrict the rows that you processed you would have to do that downstream in the package.  From an efficiency point of view this is not ideal unless you genuinely needs to retrieve all the rows from the source database object.  If you follow what is sent to the database using SQL profiler you will see that the component sends a ‘SELECT*’ query against the specified object.

Because of the inability to filter what data you retrieve from the database, this data access mode is not commonly used unless shifting data wholesale from one source to another.

Data Access Mode: SQL Command

The a SQL Command data access mode is much more powerful as it allows you to craft custom T-SQL queries to retrieve the specific data you require from the database, including the use of stored procedures to return data.  An SQL Command can also be parameterised to allow even greater flexibility by passing package variables through to the query.

It is sensible to avoid having complex SQL in the SQL Command however, as the parsing engine can get confused, and can send incorrect SQL through to the database, or be unable to understand the columns that it is expected to return. When things start getting too complex,  it is best to push the complex code back to the database layer as a view or stored procedure.
STEP 18:-

STEP 19:-

STEP 20:-

Print this post