Data Flow Task Plus

Requirements

In order to complete this task you must have Microsoft SQL Server 2017 Enterprise Edition or Developer Edition, and your computer must have the following minimum requirements:

Operating Systems

• Windows 10 version 1507 or higher: Home, Professional, Education, and Enterprise (LTSC and S are not supported)
• Windows Server 2016: Standard and Datacenter
• Windows 8.1 (with Update 2919355): Core, Professional, and Enterprise
• Windows Server 2012 R2 (with Update 2919355): Essentials, Standard, Datacenter
• Windows 7 SP1 (with latest Windows Updates): Home Premium, Professional, Enterprise, Ultimate

Hardware

• 1.8 GHz or faster processor. Dual-core or better recommended
• 2 GB of RAM; 4 GB of RAM recommended (2.5 GB minimum if running on a virtual machine)
• Hard disk space: up to 130 GB of available space, depending on features installed; typical installations require 20-50 GB of free space.
• Hard disk speed: to improve performance, install Windows and Visual Studio on a solid state drive (SSD).
• Video card that supports a minimum display resolution of 720p (1280 by 720); Visual Studio will work best at a resolution of WXGA (1366 by 768) or higher.

Create Source and Destination Connections

Before we can begin to setup the source and destination components, we will need to create two Connection Managers. Since our source and target is a SQL Server database, we will use OLE DB Connection for both Connection Managers.

1. Right-Click in the Connection Manager and Select New OLE DB Connection from the pop-up menu option.
2. When the Configure OLE DB Connection Manager dialog appears, Click the New button.
3. Once the Connection Manager dialog appears, Type or Select the Server Name.

4. Then, Select the Database Name and Test Connection.
5. Click the Ok button to create the Source Connection.
6. Repeat steps 2 through 5 for the Destination Connection.
7. After the Source and Destination Connections have been created, rename them to SQLSourceConn and SQLTargetConn.

Setup Data Flow – OLE DB Source Component

1. Drag and drop the Data Flow Task Plus from the SSIS Toolbox to the design surface.
2. Before we add the OLE DB Source Component, set the DelayValidation of the Task Properties to True (See Figure-03) by right clicking on the Task and selecting “Properties” from the drop-down menu.

Note: by setting this property to True, we are delaying validation of the task until run-time. Otherwise, errors and warnings will be generated before the package is actually executed.

6. Right-Click the OLE DB Source Component to open Show Advanced Editor.
7. Select the SQLSourceConn that we created earlier from the OLE DB Connection Manager (See Figure-05).
8. Click the Component Properties tab. Then Select the SQL Command from the Data Access Mode.

9. Enter the following SQL Statement in the SQL Command Text box (See Figure-06).

   SELECT * FROM SALES.CUSTOMER

Note: we are using a SQL query to retrieve data from the OLE DB data source because in the next two articles, I will show how we can use variables and expressions to modify the SQL Query at run-time and make the package more dynamic.

10. Click the Input and Output Properties tab.
11. Select the External Columns from the Inputs and Outputs box.
12. Click the Add Column to add the THUNK_COLUMN.
13. Set the Data Type to string [DT_STR] and Length of the Data Type Properties to 50.
14. Repeat the steps for the Output Columns and set the Data Type and Length accordingly (See Figure-07).

Note: to ensure that the component passes validation, the THUNK_COLUMN is added to both Input and Output Properties. The THUNK_COLUMN is used as a dummy column, which is removed at run-time and replaced with the available columns during execution.

15. Click the Column Mapping tab to map the External Column to Output (See Figure-08).
16. Click the OK button to accept the changes.

Setup Data Flow – OLE DB Destination Component

1. Add the OLE DB Destination Component from the SSIS Toolbox, by dragging the component to the design surface.
2. Set the ValidateExternalMetadata property for OLE DB Destination Component to “False” (See Figure-09) by Right-Clicking on the component and selecting “Properties” from the drop-down menu.
3. Right-Click the OLE DB Destination Component to open Show Advance Editor.

4. Select the SQLTargetConn that we created earlier from the OLE DB Connection Manager (See Figure-10).

5. Click the Component Properties tab and Set the AccessMode to OpenRowset Using FastLoad and enter the table name, [dbo].[Src_Customer], in the OpenRowset text box (See Figure-11).

6. Click the Input and Output Properties tab.
7. Click the Add Column button to add the THUNK_COLUMN to the External Columns (See Figure-12).

Note: make sure the DataType and Length are the same as the Input Columns.

8. Click the OK button.

9. Click on the Control Flow tab
10. Drag the connector arrow from OLE DB Source to the OLE DB Destination (See Figure-13).

11. Right-Click the OLE DB Destination Component to open Show Advance Editor again.
12. Click on the Column Mappings to ensure that the Input Columns and Output Columns are mapped (See Figure-14).
13. Click the OK button.

Note: if an error appears because of the mapping, open the editor again in advance mode and review the changes. When you close the editor, the error should have disappeared.

Control Flow – Enable Dynamic

Our last step in setting up the Data Flow Task Plus is to set the component to be processed dynamically. Enabling this property of the component triggers the collections of metadata for the source and destination to be determined at run-time.

1. Click the Control Flow tab
2. Double-Click the Data Flow Task Plus to open up the editor
3. Click the Dynamic tab
4. Expand OLE DB Source and Destination and set Enabled to True
5. Click the OK button
6. Click File from Menu and Save All.

Summary

In this Quick Start article, I have shown you How-To create an SSIS package using the Data Flow Task Plus that establishes a connection to an OLE DB Source to read the data from a local AdventureWorks2016CTP3 database and at run-time dynamically maps the source and destination columns to write the data to a different table in another database using the OLE DB Destination component.

Requirements

In order to complete this task you must have Microsoft SQL Server 2017 Enterprise Edition or Developer Edition, and your computer must have the following minimum requirements:

Operating Systems

• Windows 10 version 1507 or higher: Home, Professional, Education, and Enterprise (LTSC and S are not supported)
• Windows Server 2016: Standard and Datacenter
• Windows 8.1 (with Update 2919355): Core, Professional, and Enterprise
• Windows Server 2012 R2 (with Update 2919355): Essentials, Standard, Datacenter
• Windows 7 SP1 (with latest Windows Updates): Home Premium, Professional, Enterprise, Ultimate

Hardware

• 1.8 GHz or faster processor. Dual-core or better recommended
• 2 GB of RAM; 4 GB of RAM recommended (2.5 GB minimum if running on a virtual machine)
• Hard disk space: up to 130 GB of available space, depending on features installed; typical installations require 20-50 GB of free space.
• Hard disk speed: to improve performance, install Windows and Visual Studio on a solid state drive (SSD).
• Video card that supports a minimum display resolution of 720p (1280 by 720); Visual Studio will work best at a resolution of WXGA (1366 by 768) or higher.

Create and Configure the Package Variables

Before we can begin to create the Connection Managers for the source and destination components, we will need to create a few package variables. These variables will be used to store the name of the source and destination. The variables are then sourced in the expression to build the SQL query dynamically and set the destination, thus, making the package more dynamic at execution time.

1. To set the scope of the variable to the package, Click anywhere on the design surface of the Control Flow tab.
2. From the SSIS sub-menu, Click on Variables.
3. When the Variables window appears, Click the Add Variable icon.
4. After the new variable is added to the list, rename it accordingly (See Figure-02-01).
5. Perform step 03 through 04 for the remaining variables.
   Note: in addition to renaming the variable, you will need to set the data type, Expression, and default value.

Create the Source and Destination Connection Managers

Now that we have created the necessary variables, we can continue to setup the source and destination components. We will need to create two Connection Managers. Since our source and target are SQL Server databases, we will use the OLE DB Connection for both Connection Managers.

1. Right-Click in the Connection Manager and Select New OLE DB Connection from the pop-up menu option.
2. When the Configure OLE DB Connection Manager dialog appears, Click the New button.
3. Once the Connection Manager dialog appears, Type or Select the Server Name.

4. Then, Select the Database Name and Test Connection.
5. Click the Ok button to create the Source Connection (See Figure-02-02).
6. Repeat steps 2 through 5 for the Destination Connection.
7. After the Source and Destination Connections have been created, rename them to SQLSourceConn and SQLTargetConn.

Configure the Recordset Destination

In this section, we will set up a Recordset destination to use with the Foreach Loop container. The Recordset destination will allow us to save the data in memory in a recordset and later reference via a package variable of the Object data type.
The data retrieved from the reference table (a.k.a, metadata table) consists of the source and destination name, which will be used in an expression to dynamically construct the SQL query and set the destination.

1. Drag and drop the Execute SQL Task from the SSIS Toolbox to the design surface.
2. Double-Click on the Execute SQL Task to open the editor.
3. When the Execute SQL Task Editor window appears, set the following properties accordingly (See Figure-02-07):

4. When done, Click on the Result Set Page.
5. Set the Result Name to 0 and map the result set to the variable to User::gobjEtlTableMapping (See Figure-02-08).
6. Click on the OK button.

Note: because the result set type is Full result set, we must use 0 as the result set name.

Configure the Foreach Loop Container

In the previous section, we setup the Recordset destination to hold the values of the source and destination name from the metadata table. In this section, we will configure the Foreach Loop container with the Foreach ADO enumerator to read one row of the recordset at a time and call the Data Flow Task Plus. When the data flow for the Data Flow Task Plus is executed, the SQL query for Source component will be constructed dynamically using an expression and the name of Destination will assigned.

1. Drag and drop the Foreach Loop container from the SSIS Toolbox to the design surface.
2. Drag the connector arrow from the Execute SQL Task to the Foreach Loop container (See Figure-02-09).
3. Double-Click on the Foreach Loop to open the Editor.
4. When the Foreach Loop Editor window appears, Click on the Collection page and set the following properties accordingly (See Figure-02-10):

5. When done, Click on the Variable Mapping page.
6. On the Variable Mappings page, map the User::gsSrcTableName to index 0 and User::TrgTableName to index 1 (See Figure-02-11).
7. Click on the OK button.

Setting up the Data Flow – OLE DB Source Component

1. Drag and drop the Data Flow Task Plus from the SSIS Toolbox into the Foreach Loop container.
2. Before we add the OLE DB Source Component, set the DelayValidation of the Task Properties to True (See Figure-02-12) by right clicking on the Task and selecting “Properties” from the drop-down menu.

Note: by setting this property to True, we are delaying validation of the task until run-time. Otherwise, errors and warnings will be generated before the package is actually executed.

3. Click the Data Flow tab.
4. Add the OLE DB Source Component from the SSIS Toolbox by dragging the component to the design surface.
5. Before we edit the OLE DB Source Component, set the ValidateExternalMetadata property to “False” (See Figure-02-13) by Right-Clicking on the component and selecting “Properties” from the drop-down menu.

Note: by setting this property to False, we are delaying the validation of metadata until run-time.

6. Right-Click the OLE DB Source Component to open Show Advanced Editor.
7. Select the SQLSourceConn that we created earlier from the OLE DB Connection Manager (See Figure-02-14).
8. Click the Component Properties tab and set the following properties accordingly (See Figure-02-15):

Note: we are using a SQL query to retrieve data from the OLE DB data source. However, the SQL query will be constructed dynamically using an expression and the information retrieved from the reference table at run-time. Thus, making the package more dynamic.

10. Click the Input and Output Properties tab.
11. Select the External Columns from the Inputs and Outputs box.
12. Click the Add Column to add the THUNK_COLUMN.
13. Set the Data Type to string [DT_STR] and Length of the Data Type Properties to 50.
14. Repeat the steps for the Output Columns and set the Data Type and Length accordingly (See Figure-02-16).

Setting up the Data Flow – OLE DB Destination Component

1. Add the OLE DB Destination Component from the SSIS Toolbox, by dragging the component to the design surface.
2. Set the ValidateExternalMetadata property for OLE DB Destination Component to “False” (See Figure-02-17) by Right-Clicking on the component and selecting “Properties” from the drop-down menu.
3. Right-Click the OLE DB Destination Component to open Show Advanced Editor.

4. Select the SQLTargetConn that we created earlier from the OLE DB Connection Manager (See Figure-02-18).
5. Click the Component Properties tab and Set the AccessMode to OpenRowset Using FastLoad and enter the table name, [dbo].[Src_Customer], in the OpenRowset text box (See Figure-02-19).
6. Click the Input and Output Properties tab.
7. Click the Add Column button to add the THUNK_COLUMN to the External Columns (See Figure-02-20).
8. Click the OK button.

Note: make sure the DataType and Length are the same as the Input Columns.

9. Click on the Control Flow tab
10. Drag the connector arrow from OLE DB Source to the OLE DB Destination (See Figure-02-21).

11. Right-Click the OLE DB Destination Component to open Show Advance Editor again.
12. Click on the Column Mappings to ensure that the Input Columns and Output Columns are mapped (See Figure-02-22).
13. Click the OK button.

Note: if an error appears because of the mapping, open the editor again in advanced mode and review the changes. When you close the editor, the error should have disappeared.

Data Flow Task Plus – Enabling the Dynamic Feature

Our last step in setting up the Data Flow Task Plus is to set the component to be processed dynamically. Enabling this property of the component enables the collection of metadata for the source and destination to be determined at run-time.

1. Click on the Control Flow tab.
2. Double-Click the Data Flow Task Plus to open up the editor
3. Click the Dynamic tab
4. Expand OLE DB Source and Destination and set Enabled to True (See Figure-02-23).
5. Click the OK button
6. Click File from Menu and Save All.

Summary

In this Quick Start article, I have shown you How-To create an SSIS package using COZYROC's Data Flow Task Plus. Using Variables, Expressions, Recordset Destination, Foreach Loop, and a metadata table for referencing the source and destination names, we made the package more dynamic, hence, allowing us to extract data from various OLE DB Sources and load several OLE DB Destinations with a single SSIS package. Finally, by enabling the dynamic feature of the Data Flow Task Plus, we can postpone the mapping and dynamically map the source and destination columns at run-time, thus, saving hours of resource and effort.