Inserting data from one SQL Server Database view or table to another SQL table
To do so, please drag and drop the data flow task from the toolbox to the control flow.
Double click on data flow task it will open the data flow tab. Now, Drag and drop OLE DB Source from SSIS toolbox into the data flow region.
Double click on OLE DB source in the data flow region will open the connection manager settings.
If you haven’t created the OLE DB Connection Manager before click on the New button and configure it.
Data Access Mode: This property of SSIS OLE DB Source provides four options for us:
- Table Or View: If you select this option, It will display the list of available Tables present in the Database. And our job is to select the required tables from them.
- SQL Command: If you select this option then, We have to write our own SQL Command.
- Table name Or View name variable: For this option, you have to provide the variable name which holds the Table name or Views name.
- SQL Command from variable: If you select this option, We have to provide the variable name which holds the SQL Command.
The SQL Command option provides following options
- SQL Command Text: If you are familiar with SQL Queries, write it in the empty space provided by this option.
- Build Query: It will open a Query Designer to design the required query using the Graphical User interface. Please refer to Query Builder in SSIS article.
- Parameters: If your SQL query is parameterized (with ?), please assign the appropriate parameters using the dialog box.
- Browse: Allow us to select the Query present in the file system.
- Parse Query: This option checks whether the query successfully parsed or not
Click on the preview button to see the data present in the table.
Click ok to finish configuring OLE DB Source in SSIS.
Destination table data type
The SSIS OLE DB Destination is used to load data into a variety of database tables or views or SQL Commands. OLE DB destination editor provides us the choice to select the existing table(s), View(s), or you can create a new table.
Drag and drop the data flow task from the toolbox to the control flow. Next, rename it to SSIS OLE DB Destination.
Below screenshot will show you the list of available options in the Connection Manager tab:
- OLE DB Connection Manager: Here, you have to select the existing OLE DB Connection Manager (if any); otherwise, click on the new button to create one. For this example, we are using the already created Connection manager.
- Name of the table or the 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.
- Keep Identity: Please specify whether you want to load the identity values into the destination table or not by checking this option. This option is available only for the fast loads, and it unchecked by default.
- Keep Nulls: Please specify whether you want to load the NULL values into the destination table or not by checking this option. This option is available only for the fast loads, and it unchecked by default.
- Table Lock: While loading the data, Please specify whether you want to Lock the destination table or not
- Check Constraints: While loading the data, please specify whether the destination table Check Constraints or not.
- Rows Per Batch: Please specify the number of rows you want to send per batch. In real-time, it is always advisable to specify the value, and this can improve your performance.
- Maximum insert commit size: Please specify the maximum batch size here.
Click on the Mappings tab to check whether the source columns exactly mapped to the SSIS OLE DB destination columns.
Clicking OK to finish configuring the SSIS OLE DB Destination package.
