The Source Assistance in SSIS will help you to choose the source from the available list. If you are confused to select your source or if you don’t know which one to select then, Source Assistance can be very helpful.
Configuring Source Assistance in SSIS
In this article we will show you, How to configure the source assistance in SSIS (SQL Server Integration Services) to extract data from SQL Server Database.
First, Drag and drop the data flow task from the toolbox to control flow. Next, change the name as Source Assistance in SSIS as shown below.
Double click on it will open data flow tab. Now, Drag and drop Source Assistance from SSIS toolbox into the data flow region. Once you drop it on the data flow region, a new window called Source Assistance – Add New Source will be displayed as shown below.
If you observe the window closely, Select source type region is displaying the four most commonly used sources. Please select the source as per your requirements. In this example we are using SQL Server as our source. So, we are selecting the SQL Server and click New connection Manager.
TIP: If you already created the connection manager then you can select it from the list.
Once you click on New connection Manager, following window will be displayed. Here we are selecting the Adventure Works DW 2014 database from our SQL Server. Before we close the Connection manager window, Please test the connection and if it is successful then click ok button.
TIP: Please refer OLE DB Connection Manager in SSIS article to understand the properties in detail.
From the below screenshot you can observe that, our source assistance created a OLE DB Source here.
Double click on OLE DB source in the data flow region will open the OLE DB source Editor along with already created OLE DB connection manager.
For the time being we are selecting the DimProducts table from our Adventure Works DW 2014 database. Please refer OLE DB Source in SSIS article to understand each and every property present inside this OLE DB source Editor
Click on columns tab to verify the columns. Here, we can also remove the unwanted columns by uncheck them. Here we are removing few columns for the demonstration purpose
Click ok to finish configuring Source Assistance in SSIS
Let drag and drop the Union All transformation and enable the data viewer to se the data inside our newly created source.
From the below screenshot you can see that it is passing 606 records.
Thank You for Visiting Our blog