{"id":26201,"date":"2016-06-20T00:00:00","date_gmt":"2016-06-20T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/ssis-in-ssdt-the-basics\/"},"modified":"2021-09-15T13:22:24","modified_gmt":"2021-09-15T13:22:24","slug":"ssis-in-ssdt-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/ssis-in-ssdt-the-basics\/","title":{"rendered":"SSIS in SSDT: The Basics"},"content":{"rendered":"<div class=\"article-content\">\n<p class=\"start\">SQL Server Integration Services (SSIS) can be a handy tool for developing and implementing extract, transform, and load (ETL) solutions, but getting started with SSIS can seem a daunting task, especially if you&#8217;re new to SSIS and ETL concepts. In such cases, it can help to see examples of how to build SSIS packages that carry out basic ETL operations. <\/p>\n<p>This article provides such an example. In addition to introducing you to SSIS concepts and components, the article demonstrates how to use those components to develop an SSIS solution that extracts data from a file, transforms the data, and loads it into SQL Server.<\/p>\n<p>To work through the examples in this article, you must be running Visual Studio with SQL Server Data Tools (SSDT) installed, a topic that has caused no end of confusion (and controversy) among SQL Server developers, some of whom still mourn the demise of Business Intelligence Development Studio (BIDS). If you&#8217;re running SQL Server 2008 or 2008 R2 and have access to BIDS, you&#8217;ll find that many of the concepts discussed here still apply, but the article is concerned primarily with SSIS development as it is implemented in SSDT, Microsoft&#8217;s current go-to tool for SSIS development.<\/p>\n<p>For those of you not sure how to install SSDT in Visual Studio, you can refer to the following MSDN articles to help you get started:<\/p>\n<ul>\n<li>         <a href=\"https:\/\/msdn.microsoft.com\/en-us\/hh500335(v=vs.103).aspx\">Install SQL Server Data Tools<\/a>     <\/li>\n<li>         <a href=\"https:\/\/msdn.microsoft.com\/en-us\/mt186501.aspx\">SQL Server Data Tools in Visual Studio 2015<\/a>.     <\/li>\n<\/ul>\n<p>Once you have SSDT and Visual Studio set up the way you want them, you can start working through the examples in this article. I developed the examples based on the      <strong>AdventureWorks2014<\/strong> database, running on a local instance of SQL Server 2014. To try out the example for yourself, you must have access to a SQL Server instance, preferably with the      <strong>AdventureWorks2014<\/strong> database installed. You can probably get away with an earlier version of the database, but I haven&#8217;t tested that for myself. <\/p>\n<h2>Getting Started<\/h2>\n<p>There are two preliminary steps you must take in order to try out the examples in this article. The first is to run a      <strong>bcp<\/strong> command similar to the following to export sample data to a text file: <\/p>\n<pre class=\"listing\">bcp AdventureWorks2014.HumanResources.vEmployee out C:\\DataFiles\\SsisExample\\source\\EmployeeData.txt -w -t, -S localhost\\SqlSrv2014 -T<\/pre>\n<p>The command retrieves data from the      <strong>vEmployee<\/strong> view and saves it to the      <strong>EmployeeData.txt<\/strong> file, which will be used as the source data for our SSIS package. Notice the      <strong>-t<\/strong> argument included in the command, followed by the comma. This indicates that a comma will be used to delimit that values. Before you run the command, be sure to substitute the correct file path and SQL Server instance name, as appropriate for your environment. <\/p>\n<p>The next step you must take is to create two tables (in the      <strong>AdventureWorks2014<\/strong> database, just to keep things simple). I used SQL Server Management Studio (SSMS) to create the tables, but you can instead use SSDT if you&#8217;re already familiar with the toolkit. In either environment, you should use the following      <strong>CREATE<\/strong>     <strong>TABLE<\/strong> statements to add the tables to the database: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE TABLE dbo.EmpSales\n(\n  EmpID INT PRIMARY KEY,\n  AltID\tNVARCHAR(20) NOT NULL,\n  FirstName NVARCHAR(50) NOT NULL,\n  MiddleName NVARCHAR(50) NULL,\n  LastName NVARCHAR(50) NOT NULL,\n  JobTitle NVARCHAR(50) NOT NULL,\n  SalesGroup NVARCHAR(50) NULL\n);\n\nCREATE TABLE dbo.EmpNonSales\n(\n  EmpID INT PRIMARY KEY,\n  AltID\tNVARCHAR(20) NOT NULL,\n  FirstName NVARCHAR(50) NOT NULL,\n  MiddleName NVARCHAR(50) NULL,\n  LastName NVARCHAR(50) NOT NULL,\n  JobTitle NVARCHAR(50) NOT NULL\n);\n<\/pre>\n<p>The tables are nearly identical. The      <strong>EmpSales<\/strong> table will store data about sales representatives, and the      <strong>EmpNonSales<\/strong> table will store data about all other employees. The major difference between the two tables is that the      <strong>EmpSales<\/strong> table includes the      <strong>SalesGroup<\/strong> column, but the      <strong>EmpNonSales<\/strong> table does not. <\/p>\n<p>The tables will serve as the target for our ETL operation. We will retrieve the data from the      <strong>EmployeeData.txt<\/strong> file, transform the data, and load it into the tables, taking a couple other steps along the way. <\/p>\n<h2>Creating an SSIS project<\/h2>\n<p>The first step in building our SSIS solution is to create an SSIS project in SSDT, based on the      <strong>Integration<\/strong>     <strong>Services<\/strong>     <strong>Project<\/strong> template. To create the project, open Visual Studio, click the      <strong>File<\/strong> menu, point to      <strong>N<\/strong>     <strong>ew<\/strong>, and then click      <strong>Project<\/strong>.  <\/p>\n<p>In the      <strong>New<\/strong>     <strong>Project<\/strong> dialog box, navigate to the      <strong>Integration<\/strong>     <strong>Services<\/strong> node (one of the      <strong>Business<\/strong>     <strong>Intelligence<\/strong> templates), and select      <strong>Integration<\/strong>     <strong>Service<\/strong>     <strong>s<\/strong>     <strong>Project<\/strong>. In the bottom section of the dialog box, provide a project and solution name and specify a location for the project, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-8cad5aed-93bc-4e2e-ae2d-ca144e7ac1d9.png\" alt=\"2434-8cad5aed-93bc-4e2e-ae2d-ca144e7ac1d\" \/><\/p>\n<p>When you click      <strong>OK<\/strong>, Visual Studio generates the initial project files, which include a package file, named      <strong>Package.dtsx<\/strong>. If you have the      <strong>Solution<\/strong>     <strong>Explorer<\/strong> pane open (shown in the following figure), you can see the project structure that has been created. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-f0616589-dfc0-4625-9897-2e4a68bb1280.png\" alt=\"2434-f0616589-dfc0-4625-9897-2e4a68bb128\" \/><\/p>\n<p>The package file provides a container for adding and configuring the components that carry out the ETL operation. We can rename the package file or add more package files, or simply stick with what we have, which is what we&#8217;ll do for this article.<\/p>\n<p>The initial package file opens by default when we create our solution. SSDT provides a design surface (main window in the figure above) for working with the components within the file. We&#8217;ll be discussing the design surface in more detail as we work through our examples.<\/p>\n<p>The figure above also shows the      <strong>SQL<\/strong>     <strong>Server<\/strong>     <strong>Object<\/strong>     <strong>Explorer<\/strong> pane for accessing SQL Server instances, much like you can in the      <strong>Object<\/strong>     <strong>Explorer<\/strong> pane in SQL Server Management Studio. You might or might not have this pane open on your system, which does not matter for what we&#8217;ll be doing here. What you will need, however, is the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane, which is different from the default Visual Studio      <strong>Toolbox<\/strong> pane.  <\/p>\n<p>The      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane provides the components we need for defining our ETL operation, much like the      <strong>Toolbox<\/strong> pane in BIDS. However, finding the option to open the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane can be a bit tricky. To do so, click the      <strong>View<\/strong> menu, point to      <strong>Other<\/strong>     <strong>Windows<\/strong>, and then click      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong>, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-5439c46e-eb8f-4b4a-b5f1-a23cbda33bba.png\" alt=\"2434-5439c46e-eb8f-4b4a-b5f1-a23cbda33bb\" \/><\/p>\n<p>When I first tried this in Visual Studio 2015, the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> option did not appear. I assumed that I was looking in the wrong place. I was not. I had to re-launch Visual Studio several times before the option appeared. When I tried to replicate this behavior, the results seemed inconsistent. I do not know whether this was a quirk with my system, with SSDT, or with Visual Studio. My advice to you? Once you get the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane open, don&#8217;t close it. Ever. <\/p>\n<p>After you open the pane, you should see something similar to the following figure, which shows many of the control flow components available to the package file.<\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-88c3ef6a-122d-4280-b988-7400138e22d9.png\" alt=\"2434-88c3ef6a-122d-4280-b988-7400138e22d\" \/><\/p>\n<p>If we were working with the data flow, we would see a different set of components, but you don&#8217;t need to worry about that for now. We&#8217;ll get to the data flow in a bit. Just know that you need to have the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane open before you can add those components to our package file. Before we do that, however, we&#8217;re going to first define a couple connection managers. <\/p>\n<h2>Adding connection managers<\/h2>\n<p>In SSIS, when you retrieve data from a source or load data into a destination, you must define a connection manager that provides the information necessary to make the connection. In this case, we need to create a connection manager for the      <strong>EmployeeData.txt<\/strong> file and one for the      <strong>AdventureWorks2014<\/strong> database. <\/p>\n<p>We don&#8217;t have to create the connection managers in advance; we can create them when adding components to the control flow or data flow. It&#8217;s mostly a matter of preference. Personally, I like to set up the connections as a separate step so I don&#8217;t have to mess with them when focusing on the other components.<\/p>\n<p>To set up the connection managers, we can use the      <strong>Connection<\/strong>     <strong>Managers<\/strong> pane at the bottom of our design surface. Let&#8217;s start with the one for the      <strong>AdventureWorks2014<\/strong> database. Right-click an area within the      <strong>Connection<\/strong>     <strong>Managers<\/strong> pane, and then click      <strong>New<\/strong>     <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Connection<\/strong>. In the      <strong>Configure<\/strong>     <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Connection<\/strong>     <strong>Manager<\/strong> dialog box, click      <strong>New<\/strong>.  <\/p>\n<p>When the      <strong>Connection<\/strong>     <strong>Manager<\/strong> dialog box appears, provide the name of the SQL Server instance, select the authentication type (specifying a user name and password, if necessary), and then select the      <strong>AdventureWorks2014<\/strong> database, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-1bc6e3bd-b4e7-4cc4-9608-223d36806795.png\" alt=\"2434-1bc6e3bd-b4e7-4cc4-9608-223d3680679\" \/><\/p>\n<p>At this point, it&#8217;s a good idea to test your connection. If you run into problems here, you will certainly run into problems when you try to configure other components or run the package. If you test it now, you&#8217;ll at least rule out any immediate issues, assuming you can successfully connect to the database.<\/p>\n<p>When you click      <strong>OK<\/strong>, SSIS creates the connection manager and lists it in the      <strong>Connection<\/strong>     <strong>Managers<\/strong> pane. By default, the connection manager name includes both the SQL Server instance and database names. In this case, I shortened the name to include only the database component, just to keep things simple when I reference it later in the article. You can use whatever name best serves your purposes.  <\/p>\n<p>To change the name, right-click the connection manager in the      <strong>Connection<\/strong>     <strong>Managers<\/strong> pane, click      <strong>Rename<\/strong>, and then edit the name. <\/p>\n<p>Now let&#8217;s create the connection manager for the      <strong>EmployeeData.txt<\/strong> file. Once again, right-click an area within the      <strong>Connection<\/strong>     <strong>Managers<\/strong> pane, but this time, click      <strong>New<\/strong>     <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>Connection<\/strong>. When the      <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>C<\/strong>     <strong>onnection<\/strong>     <strong>Manager<\/strong>     <strong>Editor<\/strong> dialog box appears, provide a name (I used      <strong>EmployeeData.txt<\/strong>) and description. For the      <strong>File<\/strong>     <strong>name<\/strong> option, navigate to folder where you created the      <strong>EmployeeData.txt<\/strong> file and select that file. The      <strong>File<\/strong>     <strong>name<\/strong> option should include the full path, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-d71eddbd-332c-4d5b-a9e6-eee3c7c75d94.png\" alt=\"2434-d71eddbd-332c-4d5b-a9e6-eee3c7c75d9\" \/><\/p>\n<p>Note the      <strong>Column<\/strong>     <strong>names<\/strong>     <strong>in<\/strong>     <strong>the<\/strong>     <strong>first<\/strong>     <strong>data<\/strong>     <strong>row<\/strong> option. This should not be selected because the source data does not include the column names. If it is selected, clear the check box. <\/p>\n<p>That&#8217;s all there is to creating the two connection managers. We can now reference them within any our package components as we define our ETL operation.<\/p>\n<h2>Defining the control flow<\/h2>\n<p>When you open an SSIS package file in Visual Studio, you&#8217;re presented with a design surface that includes several tabs. By default, the designer opens to the      <strong>Control<\/strong>     <strong>Flow<\/strong> tab, which is where we define the package workflow. We have to start with the      <strong>Control<\/strong>     <strong>Flow<\/strong> tab, at least initially, before we can define the data flow, the core of the ETL operation. <\/p>\n<p>SSIS provides the building blocks we need to define the control flow. They come in the form of tasks and containers that we drag from the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane to the control flow design surface. A task is a component that performs a specific function, such as run a T-SQL statement or send an email message. A container provides a structure for organizing a set of tasks into isolated actions, such as implementing looping logic. <\/p>\n<p>For this article, we will build a control flow that can be broken down into the following steps:<\/p>\n<ol>\n<li>Truncate the          <strong>EmpSales<\/strong> and          <strong>EmpNonSales<\/strong> tables so they contain no data when we insert the new data.     <\/li>\n<li>Provide a structure (referred to as the          <em>data flow<\/em>) for carrying out the actual ETL operation.     <\/li>\n<li>Archive the the          <strong>EmployeeData.txt<\/strong> file after the data has been loaded into the target tables.     <\/li>\n<\/ol>\n<p>For the first step, we&#8217;ll add a      <strong>Sequence<\/strong> container to the control flow and then add two      <strong>Execute<\/strong>     <strong>SQL<\/strong> tasks to the container. The      <strong>Sequence<\/strong> container provides a simple means for grouping tasks together so they can be treated as a unit. Although it&#8217;s not necessary to use the container, adding it will give you a general sense of how containers work.  <\/p>\n<p>To add the container, drag it from the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane to the design surface and change the name to      <strong>seq<\/strong>     <strong>&#8211;<\/strong>     <strong>truncate<\/strong>     <strong>target<\/strong>     <strong>tables<\/strong>. To rename the component, right-click the container, click      <strong>Rename<\/strong>, and then type in the new name. <\/p>\n<p>You can name a component anything you like, but it&#8217;s generally considered a best practice to provide a name that briefly shows what type of component it is and then describes what it does. In this case, I&#8217;ve used a three-letter code (     <strong>seq<\/strong>) to represent the      <strong>Sequence<\/strong> container, but you can use whatever system works for you. In the end, you should have a component on your design surface that looks similar to that shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-d2ee4f12-14a6-4624-89a8-946abacfbe02.png\" alt=\"2434-d2ee4f12-14a6-4624-89a8-946abacfbe0\" \/><\/p>\n<p>Next, drag the      <strong>Execute<\/strong>     <strong>SQL<\/strong> task from the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane to within the      <strong>Sequence<\/strong> container on the design surface, and then double-click the component to open the      <strong>Execute<\/strong>     <strong>SQL<\/strong>     <strong>Task<\/strong>     <strong>Editor<\/strong> dialog box. Here you can provide a name-I used      <strong>s<\/strong>     <strong>q<\/strong>     <strong>l<\/strong>     <strong>&#8211;<\/strong>     <strong>truncate<\/strong>     <strong>EmpSales<\/strong>-and a description, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-9bc03ff4-8e5d-425e-95ac-e4846f9f5ff6.png\" alt=\"2434-9bc03ff4-8e5d-425e-95ac-e4846f9f5ff\" \/><\/p>\n<p>Next, select      <strong>AdventureWorks2014<\/strong> for the      <strong>Connection<\/strong> property. This is one of the connection managers we defined earlier. Then, for the      <strong>SQLStatement<\/strong> property, add the following T-SQL statement: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">TRUNCATE TABLE dbo.EmpSales;<\/pre>\n<p>If you want to check whether your statement can be properly parsed, first change the      <strong>BypassPrepare<\/strong> property to      <strong>False<\/strong>, and then click      <strong>Parse<\/strong>     <strong>Query<\/strong>. You should receive a message indicating that the statement will parse correctly. Although this does not necessarily mean your statement will run when you execute the package, it does indicate that you probably got the syntax correct. <\/p>\n<p>Now repeat these steps to add and configure an      <strong>Execute<\/strong>     <strong>SQL<\/strong> task for the      <strong>EmpNonSales<\/strong> table. When you&#8217;re finished, your      <strong>Sequence<\/strong> container should contain two tasks and look similar to what is shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-eb9a2f94-1261-418b-bf2b-8f3f0b0d9644.png\" alt=\"2434-eb9a2f94-1261-418b-bf2b-8f3f0b0d964\" \/><\/p>\n<p>One of the advantages of a      <strong>Sequence<\/strong> container is that it provides an easy way to execute the tasks within the container without running the entire package. To try this out for yourself, right-click the container (without clicking either task), and then click      <strong>Execute<\/strong>     <strong>Container<\/strong>. The tasks and container should run successfully, as indicated by the green circles with white checkmarks, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-6858743b-440e-481d-828b-92470a62f6f1.png\" alt=\"2434-6858743b-440e-481d-828b-92470a62f6f\" \/><\/p>\n<p>To return to the regular view of the control flow (and get out of execution mode), click the      <strong>Stop<\/strong>     <strong>Debugging<\/strong> button on the toolbar. <\/p>\n<p>Now let&#8217;s move on to the second step of the control flow, which is to add a component that will carry out the actual ETL operation. Drag the      <strong>Data<\/strong>     <strong>Flow<\/strong> task from the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane to the control flow design surface, beneath the      <strong>Sequence<\/strong> container, and rename the task      <strong>dft<\/strong>     <strong>&#8211;<\/strong>     <strong>load<\/strong>     <strong>tables<\/strong>, or something like that. <\/p>\n<p>Next, connect the precedence constraint from the      <strong>Sequence<\/strong> container to the      <strong>Data<\/strong>     <strong>Flow<\/strong> task. A precedence constraint is a connector that links two components together to define the workflow. You can use precedence constraints to define the order in which the components run and under what conditions they&#8217;re executed. In this way, components run only when certain conditions are met. <\/p>\n<p>A precedence constraint moves the workflow in only one direction. By default, the downstream component runs after the successful execution of the upstream component. If the upstream component fails, that part of the workflow is interrupted, and the downstream component does not run. However, you can configure a precedence constraint to run whether or not the upstream component fails or to run      <em>only<\/em> if that component fails. <\/p>\n<p>For this example, we&#8217;ll go with the default behavior, using a precedence constraint to specify that the      <strong>Data<\/strong>     <strong>Flow<\/strong> task should run after the      <strong>Sequence<\/strong> container has been successfully executed. To connect the components, select the      <strong>Sequence<\/strong> container so that it displays a green arrow at the bottom, and drag the arrow to the      <strong>Data<\/strong>     <strong>Flow<\/strong> task. When you release the mouse, a green directional line should connect the two components, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-bff431cf-2b05-4f22-b654-fb2d27259379.png\" alt=\"2434-bff431cf-2b05-4f22-b654-fb2d2725937\" \/><\/p>\n<p>For now, that&#8217;s all we&#8217;ll do with the      <strong>Data<\/strong>     <strong>Flow<\/strong> task. We&#8217;ll cover the task in more detail in the next section. Instead, let&#8217;s move onto the third step in the control flow, which is to archive our source file.  <\/p>\n<p>When archiving the file, we&#8217;ll move it to a different folder, renaming it in the process. To make this work we must first define a variable that adds the current date to the file name. To set up the variable, you should first open the      <strong>V<\/strong>     <strong>ariables<\/strong> pane if it is not already open. You can open the pane just like you did the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane. Click the      <strong>View<\/strong> menu, point to      <strong>Other<\/strong>     <strong>Windows<\/strong>, and then click      <strong>Variables<\/strong>. <\/p>\n<p>In the      <strong>Variables<\/strong> pane, click the      <strong>Add<\/strong>     <strong>Variable<\/strong> button at the top of the pane. This adds a row to the grid where you can configure the variable&#8217;s properties. For the first four properties, use the following values: <\/p>\n<ul>\n<li>         <strong>Name:<\/strong> path     <\/li>\n<li>         <strong>Scope:<\/strong> Package     <\/li>\n<li>         <strong>Data type:<\/strong> String     <\/li>\n<li>         <strong>Value:<\/strong> &#8220;&#8221;     <\/li>\n<\/ul>\n<p>We need to specify only an empty string for the      <strong>Value<\/strong> property because we&#8217;ll get the actual value by creating an expression. To create the expression, click the ellipses button associated with the      <strong>Expression<\/strong> property. In the      <strong>Expression<\/strong>     <strong>Builder<\/strong> dialog box, type an expression in the      <strong>Expression<\/strong> text box similar to the following: <\/p>\n<pre class=\"listing\">\"C:\\\\DataFiles\\\\SsisExample\\\\archive\\\\EmployeeData_\" + LEFT((DT_STR, 29, 1252) GETDATE(), 10)  + \".txt\"<\/pre>\n<p>The expression specifies the full path of the target folder, along with the first part of the file name, ending with an underscore. Notice we must enclose the string in double quotes and use a second backslash to escape each backslash in the path. Be sure to replace the path we&#8217;ve used here with one that works for your environment.<\/p>\n<p>The expression then concatenates the path string with the current date, as returned by the      <strong>GETDATE<\/strong> function. However, the function returns a full date\/time value, but we want to use only the date portion. (Note that you can drag components from the upper two panes when building your expression.) <\/p>\n<p>To extract the date, we must first convert the value to a string, using the      <strong>DT_STR<\/strong> function, which precedes the      <strong>GETDATE<\/strong> function. When calling the      <strong>DT_STR<\/strong> function, we must include the function name, length of the string output, and the code page. In this case, we specify the length as      <strong>29<\/strong> and the code page as      <strong>1252<\/strong>, which refers to the Latin-1 code page. This is the code page that is most commonly used.  <\/p>\n<p>We then enclose the date portion within the      <strong>LEFT<\/strong> function, using the date expression as the function&#8217;s first argument and the number of characters (10, in this case) as the function&#8217;s second argument. The final part of the expression adds the .txt extension to the filename. The      <strong>Expression<\/strong>     <strong>Builder<\/strong> dialog box should now look similar to the one shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-81d7f57a-0916-42df-98f3-3415a0a53a0a.png\" alt=\"2434-81d7f57a-0916-42df-98f3-3415a0a53a0\" \/><\/p>\n<p>The      <strong>Evaluated<\/strong>     <strong>value<\/strong> section shows what the final variable value will look like, based on the current date. To get this value, you must click the      <strong>Evaluate<\/strong>     <strong>Expression<\/strong> button. If your expression is not correct, you will receive an error message, rather than an actual value. <\/p>\n<p>After you finish creating the variable, the      <strong>Variables<\/strong> pane should look similar to the one shown in the following figure. Depending on what other steps you might have taken within SSIS, the      <strong>Value<\/strong> property might still show an empty string or show an actual value. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-94719cbe-d185-4050-8c79-4cf9ad501b66.png\" alt=\"2434-94719cbe-d185-4050-8c79-4cf9ad501b6\" \/><\/p>\n<p>Now that we have our variable, we can do something with it. Drag the      <strong>File<\/strong>     <strong>System<\/strong> task from the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane to the control flow, beneath the      <strong>Data<\/strong>     <strong>Flow<\/strong> task. Connect the precedence constraint from the      <strong>Data<\/strong>     <strong>Flow<\/strong> task to the      <strong>File<\/strong>     <strong>System<\/strong> task, and then double-click the      <strong>File<\/strong>     <strong>System<\/strong> task. <\/p>\n<p>In the      <strong>File<\/strong>     <strong>System<\/strong>     <strong>Task<\/strong>     <strong>Editor<\/strong> dialog box, change the      <strong>Name<\/strong> property to      <strong>fst<\/strong>     <strong>&#8211;<\/strong>     <strong>archive<\/strong>     <strong>file<\/strong>, and provide a description. For the      <strong>Operation<\/strong> property, select      <strong>Rename<\/strong>     <strong>file<\/strong> from the drop-down list, and for the      <strong>SourceConnection<\/strong> property, select the      <strong>EmployeeData.txt<\/strong> connection manager. <\/p>\n<p>Next, change the      <strong>IsDestinationPathVariable<\/strong> property to      <strong>True<\/strong>, and for the      <strong>DestinationVariable<\/strong> property, select the      <strong>path<\/strong> variable you just created. It will be listed as      <strong>User::path<\/strong>, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-7237fcf1-3c34-4f06-b935-d3e6ecc4861d.png\" alt=\"2434-7237fcf1-3c34-4f06-b935-d3e6ecc4861\" \/><\/p>\n<p>Notice that the      <strong>OverwriteDestination<\/strong> property is set to      <strong>True<\/strong> in the figure above. This can be handy when testing a package so you can easily re-execute the components as often as necessary, without having to manually remove the file or take other steps. In a production environment, you might want to select      <strong>False<\/strong> for the property value <\/p>\n<p>You&#8217;ll likely want to set up more complex naming logic for the archive file than what we&#8217;ve implemented in our solution, but what we&#8217;ve done here should be enough to demonstrate how the variable assignments work. That said, your control flow should now look similar to the one shown in the following figure.<\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-00035642-a933-4334-936a-737635992175.png\" alt=\"2434-00035642-a933-4334-936a-73763599217\" \/><\/p>\n<p>As you can see, we&#8217;ve defined all three steps of our control flow. We can even run the package to test that the control flow works. It won&#8217;t do much, other than archive the source file, but it will let you know if you&#8217;ve introduced any glitches so far.<\/p>\n<p>To run the package, click the      <strong>Start<\/strong> button on the toolbar. If all goes well, your results should look similar to what is shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-8db8bafe-b88b-4187-8cdc-55c370fa1cc7.png\" alt=\"2434-8db8bafe-b88b-4187-8cdc-55c370fa1cc\" \/><\/p>\n<p>To return to the regular view of the control flow, click the      <strong>Stop<\/strong>     <strong>Debugging<\/strong> button on the toolbar. Keep in mind that, if you run the control flow, it will move and rename the file you created with the      <strong>bcp<\/strong> command, in which case, you will have to rerun that command in order to continue on to the next set of examples. <\/p>\n<h2>Defining the data flow<\/h2>\n<p>Although our control flow includes the      <strong>Data<\/strong>     <strong>Flow<\/strong> task, the way in which we configure the task (and subsequently the data flow itself) is much different from the other control flow tasks. When you double-click the      <strong>Data<\/strong>     <strong>Flow<\/strong> task, Visual Studio takes you to the      <strong>Data<\/strong>     <strong>Flow<\/strong> tab, the second tab in the SSIS design surface, as shown in the following figure. There you add data flow components specific to the selected      <strong>Data<\/strong>     <strong>Flow<\/strong> task, much like you add components to the control flow. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-272e57f9-9abe-4ef6-8277-406c128f29c6.png\" alt=\"2434-272e57f9-9abe-4ef6-8277-406c128f29c\" \/><\/p>\n<p>When you switch to the      <strong>Data<\/strong>     <strong>Flow<\/strong> tab, Visual Studio changes the components in the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane to those specific to the data flow, with each component carrying out a specific ETL task. SSIS supports the following three types of data flow components: <\/p>\n<ul>\n<li>         <strong>Source:<\/strong> A source component extracts data from a specified data source.     <\/li>\n<li>         <strong>Transformation<\/strong>         <strong>:<\/strong> A transformation component transforms the data that has been extracted from a data source.     <\/li>\n<li>         <strong>D<\/strong>         <strong>estination<\/strong>         <strong>:<\/strong> A destination component loads the transformed data into a specified data destination.     <\/li>\n<\/ul>\n<p>SSIS provides a variety of components for each type in order to address different ETL needs. SSIS also allows you to incorporate custom components into your dataflow for addressing situations that the built-in components cannot support.<\/p>\n<p>For the SSIS solution we&#8217;re creating in this article, the data flow will consist of the following seven steps, each of which is associated with a data flow component:<\/p>\n<ol>\n<li>Retrieve the employee data from the          <strong>EmployeeData.txt<\/strong> file.     <\/li>\n<li>Generate an alternate ID for each employee, based on the employee&#8217;s email address.<\/li>\n<li>Convert the data type of the new column to one with a smaller size.<\/li>\n<li>Split the data flow into two data groups: one for sales representatives, one for all other employees.<\/li>\n<li>Look up the sales group associated with each sales rep.<\/li>\n<li>Load the sales rep data in the          <strong>EmpSales<\/strong> table.     <\/li>\n<li>Load the data for the other employees into the          <strong>EmpNonSales<\/strong> table.     <\/li>\n<\/ol>\n<p>With this in mind, let&#8217;s get started with the first step by adding a      <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>Source<\/strong> component to the data flow. Drag the component from the      <strong>SSIS<\/strong>     <strong>Toolbox<\/strong> pane to the data flow design surface, rename it      <strong>ffs<\/strong>     <strong>&#8211;<\/strong>     <strong>retrieve<\/strong>     <strong>employee<\/strong>     <strong>data<\/strong>, and double-click the component to configure it. <\/p>\n<p>In the      <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>Source<\/strong>     <strong>Editor<\/strong> dialog box, select the      <strong>EmployeeData.txt<\/strong> connection manager from the drop-down list, and then select the option      <strong>Retain<\/strong>     <strong>null<\/strong>     <strong>values<\/strong>     <strong>from<\/strong>     <strong>the<\/strong>     <strong>source<\/strong>     <strong>as<\/strong>     <strong>null<\/strong>     <strong>values<\/strong>     <strong>in<\/strong>     <strong>the<\/strong>     <strong>data<\/strong>     <strong>flow<\/strong>, if it is not already selected, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-2e9ff707-3091-47c0-af46-f5e5c01f5b53.png\" alt=\"2434-2e9ff707-3091-47c0-af46-f5e5c01f5b5\" \/><\/p>\n<p>That&#8217;s all we need to do for the moment, but before you click      <strong>OK<\/strong>, it&#8217;s always a good idea to preview the data to make certain you&#8217;re getting the results you want. To do so, click the      <strong>Preview<\/strong> button. The      <strong>Data<\/strong>     <strong>View<\/strong> dialog box appears and provides a sample of the data, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-b31f83de-4de0-4074-8298-cc39c0f0c721.png\" alt=\"2434-b31f83de-4de0-4074-8298-cc39c0f0c72\" \/><\/p>\n<p>After you&#8217;ve previewed the data, click      <strong>Close<\/strong> and then click      <strong>OK<\/strong> to close the the      <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>Source<\/strong>     <strong>Editor<\/strong> dialog box. Now we need to do some advanced configuring to make it easier to work with the prepared data in the downstream components.  <\/p>\n<p>Right-click the      <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>Source<\/strong> component, and then click      <strong>Show<\/strong>     <strong>A<\/strong>     <strong>dvanced<\/strong>     <strong>Editor<\/strong>. In the      <strong>Advanc<\/strong>     <strong>ed<\/strong>     <strong>Editor<\/strong> dialog box, go to the      <strong>Input<\/strong>     <strong>and<\/strong>     <strong>Output<\/strong>     <strong>Properties<\/strong> tab, and expand the      <strong>Output<\/strong>     <strong>Columns<\/strong> node in the      <strong>Inputs<\/strong>     <strong>and<\/strong>     <strong>outputs<\/strong> pane. <\/p>\n<p>Because the source data includes no column names, each column is assigned a generic name, starting with      <strong>Column<\/strong>     <strong>0<\/strong>,      <strong>Column<\/strong>     <strong>1<\/strong>,      <strong>Column<\/strong>     <strong>2<\/strong>, and so on. For each column, you will either rename it or delete it: <\/p>\n<ul>\n<li>To rename a column, select the column and then update the          <strong>Name<\/strong> property in the right pane.      <\/li>\n<li>To delete a column, select the column and click          <strong>Remove<\/strong>         <strong>Column<\/strong>.     <\/li>\n<\/ul>\n<p>The following list shows which columns to rename, what names to use, or whether to delete the columns:<\/p>\n<ul>\n<li>         <strong>Column 0:<\/strong> Rename to          <strong>EmpID<\/strong>.     <\/li>\n<li>         <strong>Column 1<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 2<\/strong>         <strong>:<\/strong> Rename to          <strong>FirstName<\/strong>.     <\/li>\n<li>         <strong>Column 3<\/strong>         <strong>:<\/strong> Rename to          <strong>MiddleName<\/strong>.     <\/li>\n<li>         <strong>Column 4<\/strong>         <strong>:<\/strong> Rename to          <strong>LastName<\/strong>.     <\/li>\n<li>         <strong>Column 5:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 6<\/strong>         <strong>:<\/strong> Rename to          <strong>JobTitle<\/strong>.     <\/li>\n<li>         <strong>Column 7<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 8<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 9<\/strong>         <strong>:<\/strong> Rename to          <strong>EmailAddress<\/strong>.     <\/li>\n<li>         <strong>Column 10<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 11<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 12<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 13<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 14<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 15<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<li>         <strong>Column 16<\/strong>         <strong>:<\/strong> Rename to          <strong>CountryRegion<\/strong>.     <\/li>\n<li>         <strong>Column 17<\/strong>         <strong>:<\/strong> Delete.     <\/li>\n<\/ul>\n<p>After you&#8217;ve finished renaming and deleting columns, your      <strong>Input<\/strong>     <strong>and<\/strong>     <strong>Output<\/strong>     <strong>Properties<\/strong> tab should look like the one shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-b47e1a17-65fa-4e64-80ed-8ee43f95926b.png\" alt=\"2434-b47e1a17-65fa-4e64-80ed-8ee43f95926\" \/><\/p>\n<p>Next we will add a      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Transformation<\/strong> component to generate a new column that contains the employee alternate ID, based on the first part of the email address.  <\/p>\n<p>To create the column, drag the      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Transformation<\/strong> component onto the design surface, change its name to      <strong>drv<\/strong>     <strong>&#8211;<\/strong>     <strong>generate<\/strong>     <strong>EmpAltID<\/strong>, and connect the green connector from the      <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>Source<\/strong> component to the      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Transformation<\/strong> component. <\/p>\n<p>Although the connectors used in the data flow appear similar to those in the control flow, they are very different, which is why they&#8217;re referred to as      <em>data paths,<\/em> rather than precedence constraints. Each data path represents a section of the pipeline as data moves through the ETL process. Data path behavior can vary from component to component, depending on the component type. <\/p>\n<p>When we connect the data path from the      <strong>Flat<\/strong>     <strong>File<\/strong>     <strong>Source<\/strong> component to the      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Transformation<\/strong> component, we&#8217;re directing the data flow from the source component&#8217;s output to the transformation component&#8217;s input. We must establish this connection between the two components before we can properly configure the second component because it relies on the metadata from the first component, as it is presented via the data path. <\/p>\n<p>After you connect the data path between the two components, double click the      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Transformation<\/strong> component. In the      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Transformation<\/strong>     <strong>Editor<\/strong> dialog box, you&#8217;ll be working in the first row of the grid in the lower pane. To start, assign the name      <strong>EmpAltID<\/strong> to the      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Name<\/strong> property, and for the      <strong>Derived<\/strong>     <strong>Column<\/strong> property, select the option      <strong>add<\/strong>     <strong>as<\/strong>     <strong>new<\/strong>     <strong>column<\/strong> from the drop-down list, if it is not already selected. <\/p>\n<p>Next, add the following expression to the      <strong>Expression<\/strong> property: <\/p>\n<pre class=\"listing\">LEFT(EmailAddress,FINDSTRING(EmailAddress,\"@\",1) -1)<\/pre>\n<p>The expression uses the      <strong>LEFT<\/strong> function to return the value that precedes the at (     <strong>@<\/strong>) symbol. The      <strong>FINDSTRING<\/strong> function determines that exact location of the first occurrence of that symbol, subtracting 1 to account for the symbol itself. When you add the expression, SSIS automatically assigns the      <strong>DT_WSTR<\/strong> data type, which is a Unicode string, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-c4745a38-398a-4da1-a129-11fdf27d1c8f.png\" alt=\"2434-c4745a38-398a-4da1-a129-11fdf27d1c8\" \/><\/p>\n<p>The component also assigns a size to the data type, based on the source data (in this case, the      <strong>EmailAddress<\/strong> column). For the new column, we want to use the same data type, but reduce the size to 20, rather than 50. One way to do this is to update the expression to explicitly convert the output from the      <strong>LEFT<\/strong> function, as in the following example: <\/p>\n<pre class=\"listing\">(DT_WSTR,20) LEFT(EmailAddress,FINDSTRING(EmailAddress,\"@\",1) - 1)<\/pre>\n<p>Another approach is to use a      <strong>Data<\/strong>     <strong>Conv<\/strong>     <strong>ersion<\/strong>     <strong>Transformation<\/strong> component to reduce the column size as a separate step. In this case, we&#8217;ll take this approach so we can demonstrate how the transformation works. You are likely to run into situations in which you must explicitly convert the data. <\/p>\n<p>To make the change, add the      <strong>Data<\/strong>     <strong>Conversion<\/strong>     <strong>Transformation<\/strong> component to the control flow, connect the data path from the the      <strong>Derived<\/strong>     <strong>Column<\/strong>     <strong>Transformation<\/strong> component to the      <strong>Data<\/strong>     <strong>Conversion<\/strong>     <strong>Transformation<\/strong> component, and change its name to      <strong>cvt<\/strong>     <strong>&#8211;<\/strong>     <strong>trim<\/strong>     <strong>EmpAltID<\/strong>.  <\/p>\n<p>Next, double-click the component to open the      <strong>Data<\/strong>     <strong>Conversion<\/strong>     <strong>Transformation<\/strong>     <strong>Editor<\/strong> dialog box, and then select the      <strong>EmpAtlID<\/strong> column from the      <strong>Available<\/strong>     <strong>Input<\/strong>     <strong>Columns<\/strong> box. Selecting the column adds a row to the grid in the bottom pane. In this row, change the      <strong>Output<\/strong>     <strong>Alias<\/strong> property to      <strong>AltID<\/strong>, and change the      <strong>Length<\/strong> property to      <strong>20<\/strong>. The dialog box should now look similar to the one shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-9307fc82-b652-4570-838c-fccffa0c7287.png\" alt=\"2434-9307fc82-b652-4570-838c-fccffa0c728\" \/><\/p>\n<p>At this point, your data flow should include three components, one source and two transformations, with data paths connecting all three, as shown in the following figure.<\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-5b39b292-136f-4987-a1e9-55cab2df2a5b.png\" alt=\"2434-5b39b292-136f-4987-a1e9-55cab2df2a5\" \/><\/p>\n<p>The next step is to add a      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong> component to divide the data into two groups, one for sales reps and one for everyone else. To split the data, add the transformation to the data flow, connect the data path from the      <strong>Data<\/strong>     <strong>Conversion<\/strong>     <strong>Transformation<\/strong> component to the      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong> component, and rename the component      <strong>spl<\/strong>     <strong>&#8211;<\/strong>     <strong>separate<\/strong>     <strong>sales<\/strong>     <strong>staff<\/strong>. <\/p>\n<p>Next, double-click the component to open the      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong>     <strong>Editor<\/strong> dialog box. Here we will create two outputs, one for each data group. To add the first output, click within the first row and change the      <strong>Output<\/strong>     <strong>Name<\/strong> property to      <strong>SalesReps<\/strong>. Then add the following expression to the      <strong>Condition<\/strong> property: <\/p>\n<pre class=\"listing\">JobTitle == \"Sales Representative\"<\/pre>\n<p>The expression states that the      <strong>JobTitle<\/strong> value must equal      <strong>Sales<\/strong>     <strong>Representative<\/strong> for a row to be included in the      <strong>SalesReps<\/strong> output.  <\/p>\n<p>Next, click in the second row and name the output      <strong>NonReps<\/strong>. This time specify the following expression: <\/p>\n<pre class=\"listing\">JobTitle != \"Sales Representative\"<\/pre>\n<p>In this case, all rows whose      <strong>JobTitle<\/strong> value does not equal      <strong>Sales<\/strong>     <strong>Representative<\/strong> are added to the      <strong>NonReps<\/strong> output. <\/p>\n<p>The      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong>     <strong>Editor<\/strong> dialog box should now look like the one shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-35a0a2c4-b894-4a62-8bdb-41a965414035.png\" alt=\"2434-35a0a2c4-b894-4a62-8bdb-41a96541403\" \/><\/p>\n<p>Next, add a      <strong>Lookup<\/strong> component to the data flow and rename it      <strong>lkp<\/strong>     <strong>&#8211;<\/strong>     <strong>find<\/strong>     <strong>sales<\/strong>     <strong>group<\/strong>. We&#8217;ll use the transformation to look up the sales group for each sales rep. After you add and rename the component, connect the data path from the      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong> component to the      <strong>Lookup<\/strong> component.  <\/p>\n<p>When you connect the components, the      <strong>Input<\/strong>     <strong>Output<\/strong>     <strong>Select<\/strong> dialog appears. Here you select which output from the      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong> component to use for the      <strong>Lookup<\/strong> component. In this case, we&#8217;ll use the      <strong>SalesReps<\/strong> output, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-1f879fdd-cad3-4797-9d96-eca226488af9.png\" alt=\"2434-1f879fdd-cad3-4797-9d96-eca226488af\" \/><\/p>\n<p>Next, double-click the      <strong>Lookup<\/strong> component. In the      <strong>Lookup<\/strong>     <strong>Transformation<\/strong>     <strong>Editor<\/strong> dialog box, ensure that the following three options on the      <strong>General<\/strong> page are selected: <\/p>\n<ul>\n<li>         <strong>Full<\/strong>         <strong>cache<\/strong>         <strong>:<\/strong> Uses an in-memory cache to store the reference dataset.     <\/li>\n<li>         <strong>OLE<\/strong>         <strong>DB<\/strong>         <strong>connection<\/strong>         <strong>manager<\/strong>         <strong>:<\/strong> Uses an OLE DB connection manager to connect to the source of the lookup date.     <\/li>\n<li>         <strong>Ignore<\/strong>         <strong>Failure<\/strong>         <strong>:<\/strong> Ignores any lookup failures, rather than failing the component or redirecting the row if no matches are found.     <\/li>\n<\/ul>\n<p>The following figure shows the      <strong>General<\/strong> page with these three options selected. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-4ef44304-ae90-49cc-a0d7-c260a30f6195.png\" alt=\"2434-4ef44304-ae90-49cc-a0d7-c260a30f619\" \/><\/p>\n<p>Now go to the      <strong>Connection<\/strong> page. From the      <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>connection<\/strong>     <strong>manager<\/strong> drop-down list, select      <strong>AdventueWorks2014<\/strong>, and then select the      <strong>Use<\/strong>     <strong>results<\/strong>     <strong>of<\/strong>     <strong>an<\/strong>     <strong>SQL<\/strong>     <strong>query<\/strong> option. Beneath the option, type or paste the following      <strong>SELECT<\/strong> statement. <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">SELECT DISTINCT cr.Name, st.[Group]\nFROM Person.CountryRegion cr \n  INNER JOIN Sales.SalesTerritory st\n  ON cr.CountryRegionCode = st.CountryRegionCode;\n<\/pre>\n<p>The statement joins the      <strong>CountryRegion<\/strong> and      <strong>SalesTerritory<\/strong> tables in order to retrieve the sales group associated with each country. In this way, we can retrieve the sales group for each sales rep, based on that rep&#8217;s country, which is part of the source data. After you add the      <strong>SELECT<\/strong> statement, the      <strong>Connection<\/strong> page should look similar to the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-3431acc4-defc-4dfc-9cbf-5228f07d31d2.png\" alt=\"2434-3431acc4-defc-4dfc-9cbf-5228f07d31d\" \/><\/p>\n<p>At this point, you might want to parse the query and preview the data to make sure everything is looking as you would expect.<\/p>\n<p>Next, go to the      <strong>Column<\/strong>     <strong>s<\/strong> page and drag the      <strong>AltID<\/strong> column in the      <strong>Available<\/strong>     <strong>Input<\/strong>     <strong>Columns<\/strong> box to the      <strong>Name<\/strong> column in the      <strong>Available<\/strong>     <strong>Lookup<\/strong>     <strong>Columns<\/strong> box. When you release the mouse, a line should connect the two columns (sort of).  <\/p>\n<p>Now select the      <strong>Group<\/strong> column check box in the      <strong>Available<\/strong>     <strong>Lookup<\/strong>     <strong>Columns<\/strong> box. This adds a row to the grid in the bottom pane. In that row, change the name of the      <strong>Output<\/strong>     <strong>Alias<\/strong> property to      <strong>SalesGroup<\/strong>. The following figure shows the      <strong>Columns<\/strong> page after I configured it on my system. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-d07bcab8-4115-4163-afb4-4d62ff11f0e5.png\" alt=\"2434-d07bcab8-4115-4163-afb4-4d62ff11f0e\" \/><\/p>\n<p>If everything has gone as planned, your data flow should now look something like the one shown in the following figure.<\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-d0e31994-dadc-4b2b-a66b-1a4631970d03.png\" alt=\"2434-d0e31994-dadc-4b2b-a66b-1a4631970d0\" \/><\/p>\n<p>Next, add an      <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Destination<\/strong> component to the data flow and change the name to      <strong>ole<\/strong>     <strong>&#8211;<\/strong>     <strong>load<\/strong>     <strong>sales<\/strong>     <strong>data<\/strong>. Connect the output data path from the      <strong>Lookup<\/strong> component to the      <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Destination<\/strong> component. When the      <strong>Input<\/strong>     <strong>Output<\/strong>     <strong>Selections<\/strong> dialog box appears, prompting you to choose an output, select the      <strong>Lookup<\/strong>     <strong>Match<\/strong>     <strong>Output<\/strong> option.  <\/p>\n<p>Now double-click the      <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Destination<\/strong> component. When the      <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Destination<\/strong>     <strong>Editor<\/strong> dialog box appears, ensure that      <strong>AdventureWorks2014<\/strong> is selected as the connection manager and that      <strong>EmpSales<\/strong> is selected as the target table, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-265630f0-73ea-4a26-acbc-8cf41852c78d.png\" alt=\"2434-265630f0-73ea-4a26-acbc-8cf41852c78\" \/><\/p>\n<p>Next, go to the      <strong>Mappings<\/strong> page and ensure that the columns are properly mapped between the available input columns and output columns, as shown in the following figure. SSIS should have picked up the mappings automatically, but you should make sure they&#8217;re correct. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-ed393ab2-a9c7-433c-9772-8b2f4fe01bd7.png\" alt=\"2434-ed393ab2-a9c7-433c-9772-8b2f4fe01bd\" \/><\/p>\n<p>You now need to add a second      <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Destination<\/strong> component that connects from the      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong> component to the new destination. This time, use the      <strong>NonReps<\/strong> output from the      <strong>Conditional<\/strong>     <strong>Split<\/strong>     <strong>Transformation<\/strong> component and configure the      <strong>OLE<\/strong>     <strong>DB<\/strong>     <strong>Destination<\/strong> component to load data into the      <strong>EmpNonSales<\/strong> table. Your data flow should now look similar to the one in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-40ab2b84-a1f6-42c6-98bd-77ef36e906a2.png\" alt=\"2434-40ab2b84-a1f6-42c6-98bd-77ef36e906a\" \/><\/p>\n<p>That should complete your data flow. You can now run the package to verify that everything is working as it should. Make sure you run the      <strong>bcp<\/strong> command if necessary to generate your source file, if it no longer exists. <\/p>\n<p>To run the package, click the      <strong>Start<\/strong> button on the Visual Studio toolbar. If everything is working as it should, all the components will indicate that they have been successfully executed, as shown in the following figure. <\/p>\n<p class=\"illustration\">     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2434-eb70dd63-480c-4e02-98a2-b72d2acc6b14.png\" alt=\"2434-eb70dd63-480c-4e02-98a2-b72d2acc6b1\" \/><\/p>\n<p>Notice that the execution results show the number of rows that passed through each data path as the data moved from one component to the next. <\/p>\n<p>To return to the regular view of the data flow, click the      <strong>Stop<\/strong>     <strong>Debugging<\/strong> button on the toolbar. <\/p>\n<h2>Working with SSIS and SSDT <\/h2>\n<p>Although the SSIS package we developed here is a relatively basic one, it demonstrates many of the principles of how to build an SSIS solution. Of course, SSIS is able to do far more than what we&#8217;ve shown you here. Each component supports additional features, and there are many components we have not covered. <\/p>\n<p>As you work with SSIS, be sure to refer to SQL Server documentation for specific information about SSIS and SSDT. If you were already using SSIS prior to the switch from BIDS to SSDT, the transition should not be too difficult. Many of the concepts and features work the same way. <\/p>\n<p>What we have not touched upon is how to implement and manage an SSIS package. We&#8217;ll have to save that for another article. Just know that, before you can implement an SSIS package, you must have already developed that package and made sure you can run it. With luck, this article has provided the starting point you needed for developing that package and seeing for yourself how easily you can extract, transform, and load data. <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server Integration Services (SSIS) is a fast and reliable way of importing and exporting data, but it can be tricky to learn, initially. Rob Sheldon provides a beginner&#8217;s guide to get you started with using SSIS with Visual Studio and SQL Server Data Tools (SSDT)&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[],"class_list":["post-26201","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26201","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\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=26201"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26201\/revisions"}],"predecessor-version":[{"id":92440,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26201\/revisions\/92440"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=26201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=26201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=26201"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=26201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}