{"id":1351,"date":"2012-06-12T00:00:00","date_gmt":"2012-06-12T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/ssis-basics-adding-data-flow-to-your-package\/"},"modified":"2021-06-03T16:45:25","modified_gmt":"2021-06-03T16:45:25","slug":"ssis-basics-adding-data-flow-to-your-package","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/ssis-basics-adding-data-flow-to-your-package\/","title":{"rendered":"SSIS Basics: Adding Data Flow to Your Package"},"content":{"rendered":"
\n

In my previous article, “SSIS Basics: Setting Up Your Initial Package<\/a>“, I showed you how to create an SSIS package and configure connection managers, data sources, and data source views. In this article, I will show you how to use some of those data connections to retrieve data from a SQL Server database and load the data into an Excel <\/strong>file. I will also show you how to add a computed column based on data derived from the data flow. In addition, I will demonstrate how to run the package. <\/p>\n

SSIS supports many control flow items that manage a package’s workflow, but the one I think to be the most important and most often used is the Data Flow<\/strong> task. For this reason, I focus on that task in this article. In future articles, I’ll cover other control flow items. <\/p>\n

\n

Note:<\/strong>
If you want to try out the examples in this article, you’ll need to create an OLE DB connection manager<\/strong> that points to the AdventureWorks<\/strong> database and a Flat File<\/strong> connection manager that points to an Excel <\/strong>file. You can find details about how to set up these connection managers in my previous article, which is referenced above. I created the examples shown in this article and the last one on a local instance of SQL Server 2008 R2. <\/p>\n<\/div>\n

Adding a Data Flow Task <\/h2>\n

Our goal in creating this package is to move data from a SQL Server database to an Excel <\/strong>file. As part of that goal, we also want to insert an additional column into the Excel <\/strong>file that’s based on derived data. <\/p>\n

To carry out our goal, we must add a Data Flow<\/strong> task to our control flow. The task lets us retrieve data from our data source, transform that data, and insert it into our destination, the Excel <\/strong>file. The Data Flow<\/strong> task is one of the most important and powerful components in SSIS and as such has it’s own workspace, which is represented by the Data Flow<\/strong> tab in SSIS Designer<\/strong>, as shown in Figure 1. <\/p>\n

\"1506-image001.jpg\"<\/p>\n

Figure 1: The Data Flow tab in SSIS Designer <\/p>\n

Before we can do anything on the Data Flow<\/strong> tab, we must first add a Data Flow<\/strong> task to our control flow. To add the task, drag it from the Control Flow Items<\/strong> window to the Control Flow tab of the SSIS Designer<\/strong> screen, as illustrated in Figure 2. <\/p>\n

\"1506-image002.jpg\"<\/p>\n

Figure 2: Adding a Data Flow task to the control flow <\/p>\n

To configure the data flow, double-click the Data Flow<\/strong> task in the control flow. This will move you to the Data Flow<\/strong> tab, shown in Figure 3. <\/p>\n

\"1506-image003.jpg\"<\/p>\n

Figure 3: The Data Flow tab in SSIS Designer <\/p>\n

Configuring the Data Flow <\/h2>\n

You configure a Data Flow<\/strong> task by adding components to the Data Flow<\/strong> tab. SSIS supports three types of data flow components: <\/p>\n

    \n
  • Sources:<\/strong> Where the data comes from <\/li>\n
  • Transformations:<\/strong> How you can modify the data <\/li>\n
  • Destinations:<\/strong> Where you want to put the data <\/li>\n<\/ul>\n

    A Data Flow<\/strong> task will always start with a source and will usually end with a destination, but not always. You can also add as many transformations as necessary to prepare the data for the destination. For example, you can use the Derived Column<\/strong> transformation to add a computed column to the data flow, or you can use a Conditional Split<\/strong> transformation to split data into different destinations based on specified criteria. This and other components will be explained in future articles. <\/p>\n

    To add components to the Data Flow<\/strong> task, you need to open the Toolbox<\/strong> if it’s not already open. To do this, point to the View<\/strong> menu and then click ToolBox, as shown in Figure 4. <\/p>\n

    \"1506-image004.jpg\"<\/p>\n

    Figure 4: Opening the Toolbox to view the data flow components <\/p>\n

    At the left side of the Data Flow<\/strong> tab, you should now find the Toolbox<\/strong> window, which lists the various components you can add to your data flow. The Toolbox<\/strong> organizes the components according to their function, as shown in Figure 5. <\/p>\n

    \"1506-image005.png\"<\/p>\n

    Figure 5: The component categories as they appear in the Toolbox <\/p>\n

    To view the actual components, you must expand the categories. For example, to view the source components, you must expand the Data Flow Sources<\/strong> category, as shown in Figure 6 <\/p>\n

    \"1506-image006.png\"<\/p>\n

    Figure 6: Viewing the data flow source components <\/p>\n

    Adding an OLE DB Source <\/h2>\n

    The first component we’re going to add to the data flow is a source. Because we’re going to be retrieving data from a SQL Server database, we’ll use an OLE DB<\/strong> source. To add the component, expand the Data Flow Sources<\/strong> category in the Toolbox<\/strong>. Then drag an OLE DB<\/strong> source from to the Data Flow<\/strong> window. Your data flow should now look similar to Figure 7. <\/p>\n

    \"1506-image007.jpg\"<\/p>\n

    Figure 7: Adding an OLE DB source to your data flow <\/p>\n

    You will see that we have a new item named OLE DB<\/strong> Source. You can rename the component by right-clicking it and selecting rename. For this example, I renamed it Employees<\/strong>. <\/p>\n

    There are several other features about the OLE DB<\/strong> source noting: <\/p>\n

      \n
    • A database icon is associated with that source type. Other source types will show different icons. <\/li>\n
    • A reversed red X appears to the right of the name. This indicates that the component has not yet been properly configured. <\/li>\n
    • Two arrows extend below the component. These are called d<\/em>ata paths<\/em>. In this case, there is one green and one red. The green data path marks the flow of data that has no errors. The red data path redirects rows whose values are truncated or that generate an error. Together these data paths enable the developer to specifically control the flow of data, even if errors are present. <\/li>\n<\/ul>\n

      To configure the OLE DB<\/strong> source, right-click the component and then click Edit<\/strong>. The OLE DB<\/strong> Source Edit<\/strong>or appears, as shown in Figure 8. <\/p>\n

      \"1506-image008.png\"<\/p>\n

      Figure 8: Configuring the OLEDB source <\/p>\n

      From the OLE DB connection manager<\/strong> drop-down list, select the OLE DB<\/strong> connection manager we set up in the last article, the one that connects to the AdventureWorks<\/strong> database. <\/p>\n

      Next, you must select one of the following four options from the Data access mode<\/strong> drop-down list: <\/p>\n

        \n
      • Table or view <\/li>\n
      • Table name or view name variable <\/li>\n
      • SQL command <\/li>\n
      • SQL command from variable <\/li>\n<\/ul>\n

        For this example, we’ll select the Table or View<\/strong> option because we’ll be retrieving our data through the uvw_GetEmployeePayRate<\/strong><\/strong> view, which returns the latest employee pay raise and the amount of that raise. Listing 1 shows the Transact-SQL used to create the view in the AdventureWorks<\/strong> database. <\/p>\n

        CREATE VIEW uvw_GetEmployeePayRate\nAS\n    SELECT  H.EmployeeID ,\n            RateChangeDate ,\n            Rate\n    FROM    HumanResources.EmployeePayHistory H\n            JOIN ( SELECT   EmployeeID ,\n                            MAX(RateChangeDate) AS [MaxDate]\n                   FROM     HumanResources.EmployeePayHistory\n                   GROUP BY EmployeeID\n                 ) xx ON H.EmployeeID = xx.EmployeeID\n                         AND H.RateChangeDate = xx.MaxDate\nGO<\/pre>\n
        Listing 1: The uvw_GetEmployeePayRate<\/strong> view definition <\/p>\n
        After you ensure that Table or view is selected in the Data access mode<\/strong> drop-down list, select the uvw_GetEmployeePayRate<\/strong><\/strong> view from the Name of the table or the view<\/strong> drop-down list. Now go to the Columns<\/strong> page to select the columns that will be returned from the data source. By default, all columns are selected. Figure 9 shows the columns (EmployeeID<\/strong>, Rate<\/strong>ChangeDate<\/strong>, and Rate<\/strong>) that will be added to the data flow for our package, as they appear on the Columns<\/strong> page. <\/p>\n
        \"1506-image009.jpg\"<\/p>\n
        Figure 9: The Columns page of the OLE DB Source Editor <\/p>\n
        If there are columns you don’t wish to use, you can simply uncheck them in the Available External Columns<\/strong> box. <\/p>\n
        Now click on the Error Output<\/strong> page (shown in Figure 10) to view the actions that the SSIS package will take if it encounters errors. <\/p>\n
        \"1506-image010.jpg\"<\/p>\n
        Figure 10: The Error Output page of the OLE DB Source Editor <\/p>\n
        By default, if there is an error or truncation, the component will fail. You can override the default behavior, but explaining how to do that is beyond the scope of this article. You’ll learn about error handling in future articles. <\/p>\n
        Now return to the Connection Manager<\/strong> page and click the Preview<\/strong> button to view a sample dataset in the Preview Query Results<\/strong> window, shown in Figure 11. Previewing the data ensures that what is being returned is what you are expecting. <\/p>\n
        \"1506-image011.jpg\"<\/p>\n
        Figure 11: Previewing a sample dataset <\/p>\n
        After you’ve configured the OLE DB<\/strong> Source component, click OK<\/strong>. <\/p>\n
        Adding a Derived Column Transformation <\/h2>\n
        The next step in configuring our data flow is to add a transformation component. In this case, we’ll add the Derived Column<\/strong> transformation to create a column that calculates the annual pay increase for each employee record we retrieve through the OLE DB<\/strong> source. <\/p>\n
        To add the component, expand the Data Flow Transformations<\/strong> category in the Toolbox<\/strong> window, and drag the Derived Column<\/strong> transformation (shown in Figure 12) to the Data Flow<\/strong> tab design surface. <\/p>\n
        \"1506-image012.jpg\"<\/p>\n
        Figure 12: The Derived Column transformation as its listed in the Toolbox <\/p>\n
        Drag the green data path from the OLE DB<\/strong> source to the Derived Column<\/strong> transformation to associate the two components, as shown in Figure 13. (If you don’t connect the two components, they won’t be linked and, as a result, you won’t be able to edit the transformation.) <\/p>\n
        \"1506-image013.jpg\"<\/p>\n
        Figure 13: Using the data path to connect the two components <\/p>\n
        The next step is to configure the Derived Column<\/strong> component. Double-click the component to open the Derived Column<\/strong> Transformation Edit<\/strong>or, as shown in Figure 14. <\/p>\n
        \"1506-image014.jpg\"<\/p>\n
        Figure 14: Configuring the Derived Column transformation <\/p>\n
        This editor is made up of three regions, which I’ve labeled 1<\/strong>, 2<\/strong> and 3<\/strong>: <\/p>\n
          \n
        1. Objects you can use as a starting point. For example you can either select columns from your data flow or select a variable. (We will be working with variables in a future article.)  <\/li>\n
        2. Functions and operators you can use in your derived column expression. For example, you can use a mathematical function to calculate data returned from a column or use a date\/time function to extract the year from a selected date.  <\/li>\n
        3. Workspace where you build one or more derived columns. Each row in the grid contains the details necessary to define a derived column. <\/li>\n<\/ol>\n
          For this exercise, we’ll be creating a derived column that calculates a pay raise for employees. The first step is to select the existing column that will be the basis for our new column. <\/p>\n
          To select the column, expand the Columns<\/strong> node, and drag the Rate<\/strong> column to the Expression<\/strong><\/strong> column of the first row in the derived columns grid, as shown in Figure 15. <\/p>\n
          \"1506-image015.jpg\"<\/p>\n
          Figure 15: Adding a column to the Expression column of the derived column grid <\/p>\n
          When you add your column to the Expression<\/strong><\/strong> column, SSIS prepopulates the other columns in that row of the grid, as shown in Figure 16. <\/p>\n
          \"1506-image016.jpg\"<\/p>\n
          Figure 16: Prepopulated values in derived column grid <\/p>\n
          As you can see, SSIS has assigned our derived column the name Derived Column 1<\/strong> and set the Derived Column<\/strong> value to <add as new column><\/strong>. In addition, our [Rate]<\/strong> field now appears in the Expression<\/strong> column, and the currency[DT_CY]<\/strong> value has been assigned to the Data Type<\/strong> column. <\/p>\n
          You can change the Derived Column<\/strong> Name value by simply typing a new name in the box. For this example, I’ve renamed the column NewPayRate<\/strong>. <\/p>\n
          For the Derived Column<\/strong> value, you can choose to add a new column to your data flow (which is the default value, <add as new column><\/strong>) or to replace one of the existing columns in your data flow. In this instance, we’ll add a new column, but there may be times when overwriting a column is required. <\/p>\n
          The data type is automatically created by the system and can’t be changed at this stage. <\/p>\n
          Our next step is to refine our expression. Currently, because only the Rate<\/strong> column is included in the expression, the derived column will return the existing values in that column. However, we want to calculate a new pay rate. The first step, then, is to add an operator. To view the list of available operators, expand the list and scroll through them. Some of the operators are for string functions and some for math functions. <\/p>\n
          To increase the employee’s pay rate by 5%, we’ll use the following calculation: <\/p>\n
          [Rate] * 1.05<\/strong> <\/p>\n
          To do this in the Expression<\/strong> box, either type the multiplication operator (*), or drag it from the list of operators to our expression (just after the column name), and then type 1.05, as shown in Figure 17. <\/p>\n
          \"1506-image017.jpg\"<\/p>\n
          Figure 17: Defining an expression for our derived column <\/p>\n
          You will see that the Data Type<\/strong> has now changed to numeric [DT_NUMERIC]<\/strong>. <\/p>\n
          Once you are happy with the expression, click on OK to complete the process. You will be returned to the Data Flow<\/strong> tab. From here, you can rename the Derived Column<\/strong> transformation to clearly show what it does. Again, there are two data paths to use to link to further transformations or to connect to destinations. <\/p>\n
          Adding an Excel Destination <\/h2>\n
          Now we need to add a destination to our data flow to enable us to export our results into an Excel <\/strong>spreadsheet. <\/p>\n
          To add the destination, expand the Data Flow<\/strong> Destinations category in the Toolbox<\/strong>, and drag the Excel<\/strong> destination to the SSIS Designer<\/strong> workspace, as shown in Figure 18. <\/p>\n
          \"1506-image018.jpg\"<\/p>\n
          Figure 18: Adding an Excel destination to your data flow <\/p>\n
          Now connect the green data path from the Derived Column<\/strong> transformation to the Excel<\/strong> destination to associate the two components, as shown in Figure 19. <\/p>\n
          \"1506-image019.jpg\"<\/p>\n
          Figure 19: Connecting the data path from the transformation to the destination <\/p>\n
          As you can see, even though we have connected the PayRate<\/strong> transformation to the Excel <\/strong>destination, we still have the reversed red X showing us that there is a connection issue. This is because we have not yet selected the connection manager or linked the data flow columns to those in the Excel<\/strong> destination. <\/p>\n
          Next, right-click the Excel<\/strong> destination, and click Edit<\/strong>. This launches the Excel Destination Editor <\/strong>dialog box, shown in Figure 20. On the Connection Manager<\/strong> page, under OLE DB connection manager<\/strong>, click on the New <\/strong>button then under Excel File Path<\/strong> click on the Browse button and select the file you created in the previous article and click on OK, then under Name of the Excel Sheet<\/strong> select the appropriate sheet from the file. <\/p>\n
          \"1506-image020.jpg\"<\/p>\n
          Figure 20: Configuring the Excel destination component <\/p>\n
          At the bottom of the Connection Manager<\/strong> page, you’ll notice a message that indicates we haven’t mapped the source columns with the destination columns. To do this, go to the Mappings<\/strong> page (shown in Figure 21) and ensure that the columns in the data flow (the input columns) map correctly to the columns in the destination Excel <\/strong>file. The package will make a best guess based on field names; however, for this example, I have purposefully named my columns in the excel spreadsheet differently from those in the source database so they wouldn’t be matched automatically. <\/p>\n
          \"1506-image021.jpg\"<\/p>\n
          Figure 21: The Mappings page of the Excel Destination Editor <\/p>\n
          To match the remaining columns, click the column name in the Input Column<\/strong> grid at the bottom of the page, and select the correct column. As you select the column, the list will be reduced so that only those columns not linked are available. At the same time, the source and destination columns in the top diagram will be connected by arrows, as shown in Figure 22. <\/p>\n
          \"1506-image022.jpg\"<\/p>\n
          Figure 22: Mapping the columns between the data flow and the destination <\/p>\n
          Once you’ve properly mapped the columns, click OK<\/strong>. The Data Flow<\/strong> tab should now look similar to the screenshot in Figure 23. <\/p>\n
          \"1506-image023.jpg\"<\/p>\n
          Figure 23: The configured data flow in your SSIS package <\/p>\n
          Running an SSIS Package in BIDS <\/h2>\n
          Now all we need to do is execute the package and see if it works. To do this, click the Execute<\/strong> button. It’s the green arrow on the toolbar, as shown in Figure 24. <\/p>\n
          \"1506-image024.png\"<\/p>\n
          Figure 24: Clicking the Execute button to run your SSIS package <\/p>\n
          As the package progresses through the data flow components, each one will change color. The component will turn yellow while it is running, then turn green or red on completion. If it turns green, it has run successfully, and if it turns red, it has failed. Note, however, that if a component runs too quickly, you won’t see it turn yellow. Instead, it will go straight from white to green or red. <\/p>\n
          The Data Flow<\/strong> tab also shows the number of rows that are processed along each step of the way. That number is displayed next to the data path. For our example package, 290 rows were processed between the Employees<\/strong> source and the PayRate<\/strong> transformation, and 290 rows were processed between the transformation and the Excel <\/strong>destination. Figure 25 shows the data flow after the three components ran successfully. Note that the number of processed rows are also displayed. <\/p>\n
          \"1506-image025.jpg\"<\/p>\n
          Figure 25: The data flow after if has completed running <\/p>\n
          You can also find details about the package’s execution on the Progress<\/strong> tab (shown in Figure 26). The tab displays each step of the execution process. If there is an error, a red exclamation mark is displayed next to the step’s description. If there is a warning, a yellow exclamation mark is displayed. We will go into resolving errors and how to find them in a future article. <\/p>\n
          \"1506-image026.jpg\"<\/p>\n
          Figure 26: The Progress tab in SSIS Designer <\/p>\n
          Now all that’s needed is to check the Excel <\/strong>file to ensure that the data was properly added. You should expect to see results similar to those in Figure 27. <\/p>\n
          \"1506-image027.jpg\"<\/p>\n
          Figure 27: Reviewing the Excel file after package execution <\/p>\n
          Summary <\/h2>\n
          In this article of the “SSIS Basics” series, I’ve shown you how to add the data flow to your SSIS package in order to retrieve data from a SQL Server database and load it into an Excel <\/strong>file. I’ve also shown you how to add a derived column that calculates the data to be inserted into the file. In addition, I’ve demonstrated how to run the package. <\/p>\n
          In future articles, I plan to show you how to deploy the package so you can run it as part of a scheduled job or call in other ways. In addition, I’ll explain how to use variables in your package and pass them between tasks. I also aim to cover more control flow tasks and data flow components, including those that address conditional flow logic and for-each looping logic. There is much much more that can be done using SSIS, and I hope over the course of this series to cover as much information as possible. <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
          Annette continues her popular series for SSIS beginners by  showing how a data flow task can be used in a package to move data from a SQL Server database to an Excel file and insert an additional column into the Excel file that's based on derived data.…<\/p>\n","protected":false},"author":10747,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4242,5664,4168,4150,4151,4306,5665],"coauthors":[],"class_list":["post-1351","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-basics","tag-data-flow","tag-database","tag-sql","tag-sql-server","tag-ssis","tag-ssis-designer"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1351","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/10747"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1351"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1351\/revisions"}],"predecessor-version":[{"id":91229,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1351\/revisions\/91229"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1351"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1351"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1351"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1351"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}