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-
MSBI__Community_Post9_Window1
3.  Configure ADO .NET Connection Manager window will open. Click on ‘New’.
MSBI__Community_Post9_Window2
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.
MSBI__Community_Post9_Window3
5. Now select the appropriate Server Name and Database name.
MSBI__Community_Post9_Window4
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.
MSBI__Community_Post9_Window5
10. Now select the table ‘Currency’ as below and Click on ‘Preview’ button.
MSBI__Community_Post9_Window6
MSBI__Community_Post9_Window7
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-
MSBI__Community_Post9_Window8
I hope you have enjoyed this tutorial and it is useful for you.

Comments

Post a Comment

Popular posts from this blog

SharePoint 2007 - Simple Task Dashboard

-
Image
example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them to quickly build a solution. Scenario: We want to provide a way for users to easily see a summary of the current project tasks. We want them to be able to see the following information: Open Tasks Assigned to Them Overdue Tasks Tasks Due This Week Task Calendar Tasks Assigned to Others Solution: To build this solution we will be using several different tools available in SharePoint, all included in the list below: Task List Task List Views We will create views for each of the different ways we want to see the data displayed on our dashboard. Web Part Page List View Web Parts Custom Navigation The first thing we will do is create our task lists and then configure all the views that we need. To create the list, go to View All Site Content, Create and then select the Task list. Next, we will need to configure our custom views. To do this, w
Read more

MERGE transformation in SSIS

-
Image
In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two  sorted  dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations. The transformation inputs are not sorted The combined output does not need to be sorted. The transformation has more than two inputs. MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example. I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
Read more