The SSIS ADO.NET destination is used to load data into database tables or views using .Net provider. SSIS ADO.Net destination editor provides us the choice to select the existing table(s), views, or you can create a 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 a 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 the 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.
Before we start, Double click on the 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 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 SSIS 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 the Connection Manager tab:
- Connection Manager: Here, you have to select the existing ADO.NET Connection Manager (if any); otherwise, click on the 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 the New button to create a 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.
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 a new table for this example, click on the New button. It opens a 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 the Mappings tab to check whether the source columns exactly mapped to the destination columns.
NOTE: If your input column names and destination column names are the same, the intelligence will automatically map. If there are any changes in the column names (any alias columns or any calculated columns), we have to map them manually.
STEP 7: Clicking OK to finish configuring the 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 to Preview the data. From the below screenshot, you can observe that we successfully loaded the incoming data into the SQL Server table using SSIS ADO.NET Destination.
