ADO.NET Source in SSIS

The ADO.NET Source in SSIS (SQL Server Integration Services) is used to extract data from the Database using .Net provider. The ADO.NET Source in SSIS uses ADO.NET Connection Manager to connect with the Database.

Configuring ADO.NET Source in SSIS

Drag and drop the data flow task from the toolbox to control flow and change the name as Configuring ADO.NET Source in SSIS.

Double click on it will open the data flow tab. Now, Drag and drop ADO.NET Source from SSIS toolbox into the data flow region

Double click on SSIS ADO.NET source in the data flow region will open the connection manager settings. If you haven’t created ADO.NET Connection Manger before click on the New button and configure it.

In this example, we are selecting the already created ADO.NET Connection Manager. Please refer to ADO.NET Connection Manager in SSIS article

Data Access Mode: This property provides 2 options for us:

Table Or View: If you select this option then, It will display the list of available Tables and Views present in the Database, and our job is to select the required table or view.
SQL Command: If you select this option then, We have to write the SQL Command on our own.

SQL Command option provides the following options

SQL Command Text: If you are familiar with SQL Queries, then you can write it in the space provided by this option. We usually write queries in SQL Management Studio and then copy them in the SQL Command Text.
Build Query: If you click on this option, it will open a Query Designer to design the required query using the Graphical User interface. Please refer to Query Builder in the SSIS article.
Browse: This will allow us to select the SQL Query present in the file system.

For the time being, we are selecting the Table or View option here. If you chose this option, the Name of the table or the View option displays all the available Tables and Views present in the current connection manager.