The ADO.NET Source in SSIS (SQL Server Integration Services) is used to extract data from the Database using a .Net provider. The ADO.NET Source 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 to Configuring ADO.NET Source.
Double-click on it to open the data flow tab. Now, drag and drop the ADO.NET Source from the toolbox into the data flow region. For more information on data Loading, please refer to the SSIS Sources and Destinations article.
Double click on the SQL Server Integration Services (SSIS) ADO.NET source in the data flow region, which will open the connection manager settings. If you haven’t created an ADO.NET Connection Manager 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 the ADO.NET Connection Manager article. For an overview of all connections, please refer to the Connection Manager article.
Data Access Mode: This property provides 2 options for us:
- Table Or View: If you select this option, 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.
- If you select this option, 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 Queries, you can write it in the space provided by this option. We usually write queries in Management Studio and copy them into 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 the Query Builder article.
- Browse: This will allow us to select the Query present in the file system.
For the time being, we are selecting the Table or View option here. If you choose this option, the Name of the table or the View option displays all the available Tables and Views present in the current connection manager.
We are selecting the DimProduct table from the list mentioned above
Next, click on the preview button to see the data present in the table. The SSIS ADO.NET Source will convert the unmatched data types to the DT_NTEXT Data type.
Click on the columns tab to verify the columns.
Click ok to finish configuring ADO.NET Source in SQL Server Integration Services package.
NOTE: Once we have the ADO.NET source, we can use it in any Transformation, including Derived Column, Conditional Split, Multicast, Merge Join, Lookup, etc.