ADO .NET Source Task in ssis

ADO .NET Source consumes data from SQL Server, OLE DB, ODBC or ORACLE using corresponding .Net Framework data provider. Use a T-SQL statement to define the result set.

For Example: Extract data from SQL server with the .Net Framework Data provider for SQL Server.

ADO .Net is an extra Layer over OLE DB and ODBC with retro features at a cost of performance.

The ADO .NET Source is very similar to the OLE DB source, but adds overhead when extracting data from OLE DB compliant sources so should only be used to access those sources when specifically required,

For Example: When they need to be access in code. For non OLE DB compliant sources, such as ODBC, it adds a wide range of connection capabilities and extends the number of sources SSIS can work against.

First you will have to create a SSIS Project.  You can refer post ‘How to Create SSIS Project?‘ to create this.

1. Now in the project, drag & drop a Data Flow Task in Control Flow Tab.

2. Right click in Connection Manager Window.Select ‘New ADO.NET Connection…’.Refer below screenshot-

3.  Configure ADO .NET Connection Manager window will open. Click on ‘New’.

4.  Connection Manage window will open as below having  ‘.Net Provider\SQL Client Data Provider’ by default.

While in the window SQL Client Data Provider, OracleClient Data Provider and ODBC Data Provider are .Net Providers where as there are several .Net Providers for OLEDB.

Microsoft is planning to depreciate the OLE DB in next SSIS version release therefore we are not using SQL Server Native Client 11.0 as it will be removed in next SSIS versions.

Microsoft has also provided SqlClient Data Provider which is basically a .Net Provider and very specific.  Its not a extra layer, just a .Net Manager connection to SQL Server.

5. Now select the appropriate Server Name and Database name.

6. Click on Test Connection. A new window will appear having message ” Test connection succeeded”.

7. Click on ‘OK’.

8. ADO . Net  Connection has been created now. Double click on Data Flow Task, it will redirect you to Data Flow window.

9. Drag & Drop an ADO .NET Source and double click on it.  ADO .NET source editor window will appear where ADO.Net Connection has automatically selected as below.

10. Now select the table ‘Currency’ as below and Click on ‘Preview’ button.

11. Now drag and drop a ‘Derived Column’ to preview the data and connect ADO .Net source to it using data path.And enable the Data Viewer.

12. Execute the package. Results will be as below-

I hope you have enjoyed this tutorial and it is useful for you.