SSIS ADO.NET Destination

The SSIS ADO.NET destination is used to load data into database tables or views using .Net provider. ADO.Net editor provides us the choice to select the existing table(s), views or you can create new table. In this article we will show you, How to configure SSIS ADO.Net destination to load or store data into the SQL Server Database.

NOTE: The SSIS ADO.NET Destination uses ADO.NET Connection Manager to create connection with relational databases

Configuring SSIS ADO.NET Destination

STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it to SSIS ADO.NET Destination.

Double click on it will open data flow tab.

STEP 2: Drag and drop OLE DB Source and ADO.NET destination on to the data flow region. In this example, we will explain about ADO.NET destination only so, Please refer OLE DB Source in SSIS article to understand the steps involved in creating it.

Before we start, Double click on OLE DB source to see the table / SQL Command we used. From the below screenshot you can see that, we are using the [Dim Products] and [Fact Internet Sales] tables present in the Adventure Works DW 2014 database

SQL Command we used in the above screenshot is:

USE AdventureWorksDW2014
GO
SELECT PROD.[EnglishProductName]
     ,PROD.[Color]
     ,[SalesOrderNumber]
     ,[UnitPrice]
     ,[ExtendedAmount]
     ,[ProductStandardCost]
     ,[TotalProductCost]
     ,[SalesAmount]
     ,[TaxAmt]
     ,[Freight]
FROM [FactInternetSales]
INNER JOIN
[DimProduct] AS PROD ON
  [FactInternetSales].[ProductKey] = PROD.[ProductKey]

STEP 3: Double-click on the ADO.NET Destination will open the to ADO.NET Destination Editor to configure the target database. Below screenshot will show you the list of available options in Connection Manager tab:

• Connection Manager: Here you have to select the existing ADO.NET Connection Manger (if any) otherwise, click on new button to create one.
• Use a table or View: Here you have to select the existing table or view to store the data otherwise, click new button to create new destination table.
• Use Bulk Insert when Possible: This option will allow the SQL Server integration service to use the bulk insert option when it is possible. It is always good practice to select this option (for better performance).

STEP 4: Here we are selecting the connection manager that we created previously. If you haven’t created ADO.NET Connection Manger before click on the New button and configure it.

STEP 5: If you click on the down arrow button beside the Use a table or View option, it will display the list of available Tables and Views present in the current connection manager as shown below.

For the time being we are selecting the existing table Table called “SSIS ADO.NET Destination”. If you haven’t created the table before or you want to create new table for this example then click on the New button as we shown below. This will open the new window to write your custom SQL Query to Create New table.

Preview Button: This button will help you to see the already existing data in your target table.

STEP 6: Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.

NOTE: If your input column names and destination column names are same then, the intelligence will automatically map. if there is any changes in the column names (any alias columns or any calculated columns) then we have map them manually.

STEP 7: Clicking OK to finish configuring SSIS ADO.NET Destination package. Let us run the package and see whether we transferred the data from OLE DB Source to ADO.NET Destination or not

Let us open the SQL Server Management Studio Query window to Preview the data. From the below screenshot you can observe that we successfully loaded the incoming data into SQL Server table using SSIS ADO.NET Destination.

Thank you for Visiting Our Blog