{"id":1340,"date":"2012-05-21T00:00:00","date_gmt":"2012-05-21T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/ssis-basics-setting-up-your-initial-package\/"},"modified":"2021-06-03T16:45:26","modified_gmt":"2021-06-03T16:45:26","slug":"ssis-basics-setting-up-your-initial-package","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/ssis-basics-setting-up-your-initial-package\/","title":{"rendered":"SSIS Basics: Setting Up Your Initial Package"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">I started using SQL Server Integration Services (SSIS) when I had a job that required me to move and manipulate data between files and other data sources. I did a bit of research using the resources available-Twitter, Simple-Talk, SQL Server Central, etc.-and concluded that SSIS was the right way to go. I had to get my head around it quite rapidly, but I couldn&#8217;t find help at the level I required. For that reason, I noted the points where I had struggled so that, once I&#8217;d learned more, I could help others who might otherwise struggle as I did.<\/p>\n<p>In this article, the first of the &#8220;SSIS Basics&#8221; series, I go through the basics required for anyone starting out with SSIS, before he or she can venture off into more exotic uses for the tool. In subsequent articles, we&#8217;ll cover such topics as variables, for-each loops, and XML. If you&#8217;re already a regular SSIS user, this series is not for you!<\/p>\n<h2>What can you use SSIS for?<\/h2>\n<p>Essentially, SSIS can be used for any task related to files or data. You can, for example,<\/p>\n<ul>\n<li>Move and rename files  <\/li>\n<li>Delete, update or insert data  <\/li>\n<li>Execute SQL scripts or stored procedures  <\/li>\n<li>Import and export data between different file types, such as Access, Excel, or any data source that supports an ODBC connection<\/li>\n<\/ul>\n<p>These are, of course, only a few of the tasks you can perform in SSIS. As we work through this series, you&#8217;ll get a better sense of how extensive SSIS actually is.<\/p>\n<h2>Getting Started<\/h2>\n<p>SSIS is available only in SQL Server 2005 onwards. You create and develop SSIS in SQL Server Business Intelligence Development Studio (BIDS), a visual development tool based on Microsoft Visual Studio. (BIDS has morphed into SQL Server Data Tools (SSDT) in SQL Server 2012.)<\/p>\n<p>Before going further, there is some terminology that&#8217;s important to understand. SSIS files are organized into <i>packages<\/i>, <i>projects<\/i> and <i>solutions<\/i>. The package is at the bottom of the hierarchy and contains the tasks necessary to perform the actual extract, transform, and load (ETL) operations. Each package is saved as a .dtsx file and is part of a project. You can include one or more packages in a project. That project, in turn, is part of a solution, which is at the top of the hierarchy. You can include one or more projects within a solution.<\/p>\n<p>When you first open BIDS, you&#8217;re presented with the interface shown in Figure 1.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image002.png\" width=\"358\" height=\"217\" alt=\"1494-clip_image002.png\" \/><\/p>\n<p class=\"caption\">Figure 1: The SSIS interface in BIDS<\/p>\n<p>To create an SSIS package, point to the <code>File<\/code> menu, point to <code>New<\/code>, and click <code>Project<\/code>. This launches the <code>New<\/code> <code>Project<\/code> dialog box, shown in Figure 2.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image004.png\" width=\"401\" height=\"269\" alt=\"1494-clip_image004.png\" \/><\/p>\n<p class=\"caption\">Figure 2: The <code>New Project<\/code> dialog box in BIDS<\/p>\n<p>In the <code>New<\/code> <code>Project<\/code> dialog box, select the <code>Integration<\/code> <code>Services<\/code> <code>Project<\/code> template. Then, provide a name for the project in the <code>Name<\/code> text box. Next, in the <code>Location<\/code> text box, specify the folder where your project files should be saved, and then provide a name for the solution in the <code>Solution<\/code> <code>Name<\/code> text box.<\/p>\n<p>After you&#8217;ve entered the project and solution information, click <code>OK<\/code>. Your new package will open in the SSIS window, as shown in Figure 3.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image006.png\" width=\"602\" height=\"364\" alt=\"1494-clip_image006.png\" \/><\/p>\n<p class=\"caption\">Figure 3: Creating a new SSIS package in BIDS<\/p>\n<p>Notice that the SSIS interface is divided into the following five sections (windows): <\/p>\n<ul>\n<li><code>Control Flow Items<\/code><b>:<\/b> The components necessary to control a package&#8217;s workflow. For example, the section includes components that let you move or copy data, run SQL statements, or send emails. (The components will be explained in more detail in this article and in articles that will follow.)  <\/li>\n<li><code>Connection Managers<\/code><b>:<\/b> The connections to your data sources (whether retrieving or loading data). Your data sources can include SQL Server databases, CSV files, Excel spreadsheets, and a variety of other sources.  <\/li>\n<li><code>Solution Explorer<\/code><b>:<\/b> A hierarchical view of the data sources, data source views, packages, and other components included within the current solution.  <\/li>\n<li><code>Properties<\/code><b>:<\/b> The properties and their values for the package or the selected component within that package.  <\/li>\n<li><code>SSIS Designer<\/code><b>:<\/b> The main working area for developing your SSIS package. <code>SSIS Designer<\/code> is broken into four tabs: <code>Control<\/code> <code>Flow<\/code>, <code>Data<\/code> <code>Flow<\/code>, <code>Event<\/code> <code>Handlers<\/code>, and <code>Package<\/code> <code>Explorer<\/code>. We&#8217;ll look at each of these in greater detail as we progress through this series.\n<\/p>\n<\/li>\n<\/ul>\n<h3>Control Flow Items<\/h3>\n<p>In this article, I focus on setting up the SSIS package and defining the data connections. I do not cover all the components in the <code>Control Flow Items<\/code> window. In the next article, I will demonstrate using, what I think is, the most important of these components-the <code>Data Flow Task<\/code>-and cover other control flow tasks in subsequent articles<code>.<\/code><\/p>\n<h3>Connection Managers<\/h3>\n<p>I will now explain how to create connection managers that connect to both Excel files and a SQL Server database.&#160; However, it is important to note that any connection created through the <code>Connection Manager<\/code> window is available only to the package it is created in.<\/p>\n<h4>Connecting to an Excel File<\/h4>\n<p>One of the first steps you&#8217;ll often take when developing an SSIS package is to create the connection managers necessary to retrieve data from or load data into your data sources. You can also set up connections &#8220;on the fly,&#8221; so if you miss creating one here it can be done as part of other tasks. This approach is most commonly used when you wish to create a connection based on the source. For example, if you wish to copy data out of a SQL Server database and export it to an Excel spreadsheet, you can create the connection manager when you set up your data flow.<\/p>\n<p>To add a connection manager, right-click the blank area in the <code>Connection<\/code> <code>Manager<\/code> window, where it says <code>Right-click here to add a new connection manager to the SSIS package<\/code>, as shown in Figure 4.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image008.png\" width=\"602\" height=\"55\" alt=\"1494-clip_image008.png\" \/><\/p>\n<p class=\"caption\">Figure 4: Adding a connection manager to your SSIS package<\/p>\n<p>This will launch a context menu that provides a number of options for creating various types of connections, as Figure 5 illustrates.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image010.png\" width=\"133\" height=\"156\" alt=\"1494-clip_image010.png\" \/><\/p>\n<p class=\"caption\">Figure 5: Selecting which type of connection manager to create<\/p>\n<p>Notice you can create connections for such sources as OLE DB, ADO.NET, Analysis Services, and different types of files. In this case, we want to create a connection to an Excel file, so click the <code>New<\/code> <code>File<\/code> <code>Connection<\/code> option. This will launch the <code>File<\/code> <code>Connection<\/code> <code>Manager<\/code> <code>Editor<\/code> dialog box, shown in Figure 6.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image012.png\" width=\"307\" height=\"146\" alt=\"1494-clip_image012.png\" \/><\/p>\n<p class=\"caption\">Figure 6: The <code>File Connection Manager Editor<\/code> dialog box<\/p>\n<p>For this example, we&#8217;ll be connecting to an Excel file I created for demonstration purposes. Figure 7 shows the worksheet I set up in this file.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image013.jpg\" width=\"383\" height=\"88\" alt=\"1494-clip_image013.jpg\" \/><\/p>\n<p class=\"caption\">Figure 7: Excel worksheet used for demonstration purposes<\/p>\n<p>I named the Excel file <code>Employees.xlsx<\/code> and saved it in the <code>C:\\Users\\Annette\\Documents<\/code> folder.<\/p>\n<p>In the <code>Usage type<\/code> drop-down list in the <code>File Connection Manager Editor<\/code> dialog box, select <code>Existing<\/code> <code>file<\/code>. Next, click the <code>Browse<\/code> button, navigate to the folder that contains the Excel file, and select the file. The dialog box should now look like the one shown in Figure 8.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image015.jpg\" width=\"473\" height=\"185\" alt=\"1494-clip_image015.jpg\" \/><\/p>\n<p class=\"caption\">Figure 8: Configuring the <code>File Connection Manager Editor<\/code> dialog box<\/p>\n<p>Once you&#8217;ve selected the file, click <code>OK<\/code>. The new connection manager will be added to the <code>Connection<\/code> <code>Managers<\/code> window and will be assigned the name of the file, as shown in Figure 9.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image016.jpg\" width=\"426\" height=\"88\" alt=\"1494-clip_image016.jpg\" \/><\/p>\n<p class=\"caption\">Figure 9: Viewing the new connection manager in the <code>Connection Managers <\/code>window<\/p>\n<p>It is very easy to rename the connection manager to something that may be more appropriate. To do so, right-click the new connection manager and select <code>Rename<\/code> from the context menu, as show in Figure 10<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image018.jpg\" width=\"214\" height=\"272\" alt=\"1494-clip_image018.jpg\" \/><\/p>\n<p class=\"caption\">Figure 10: Renaming a connection manager<\/p>\n<p>The name then becomes updateable and you can rename it to whatever you like. In this case, I renamed the connection manager <code>Employees (Excel)<\/code>, as shown in Figure 11.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image019.jpg\" width=\"430\" height=\"62\" alt=\"1494-clip_image019.jpg\" \/><\/p>\n<p class=\"caption\">Figure 11: Viewing the new connection manager name <\/p>\n<p>When you view a connection manager in the <code>Connection<\/code> <code>Managers<\/code> window, you&#8217;ll see that each connection type is associated with a different icon. If you created an Excel connection from here, it is displayed with the same icon used for any flat file connection. However if you create an Excel connection when adding a component to the <code>Data Flow<\/code> tab, the connection manager will display an Excel Icon. <\/p>\n<h4>Connecting to a SQL Server Table<\/h4>\n<p>Because our example will retrieve data from a SQL Server database, you&#8217;ll also need to create a connection manager for that database. To do so, you should again right-click the <code>Connection<\/code> <code>Managers<\/code> window to open the context menu, but this time, click the <code>New<\/code> <code>OLE<\/code> <code>DB<\/code> <code>Connection<\/code> option. The <code>Configure OLE DB Connection Manager<\/code> dialog box will appear, as shown in Figure 12.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image021.jpg\" width=\"303\" height=\"256\" alt=\"1494-clip_image021.jpg\" \/><\/p>\n<p class=\"caption\">Figure 12: Creating an <code>OLE DB<\/code> connection manager<\/p>\n<p>If any <code>OLE DB<\/code> connections have already been defined on the package, they will appear in <code>Data<\/code> <code>connections<\/code> list. You can use one of these, if it fits your needs, or you can create a new one. To create a new connection, click the <code>New<\/code> button to launch the <code>Connection<\/code> <code>Manager<\/code> dialog box, shown in Figure 13.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image023.png\" width=\"304\" height=\"310\" alt=\"1494-clip_image023.png\" \/><\/p>\n<p class=\"caption\">Figure 13: Configuring an <code>OLE DB<\/code> connection manager<\/p>\n<p>To configure the connection manager, select the SQL Server instance from the <code>Server<\/code> <code>name<\/code> drop-down list, and then select the authentication type. In this case, I selected the <code>Use<\/code> <code>SQL<\/code> <code>Server<\/code> <code>Authentication<\/code> option and provided a username and password. You might decide to select the <code>Use<\/code> <code>Windows<\/code> <code>Authentication<\/code> option, in which case your current Windows credentials will be used to establish the connection with SQL Server. In a later article, when we look at deploying the package, we will look at how the connections can be altered at run time and therefore how the login details can be changed then. For now, ensure that you set up the login the way you need it to run the package while you&#8217;re developing it.<\/p>\n<p>From the <code>Select or enter a database name<\/code> drop-down list, select the name of the <code>AdventureWorks<\/code> database. Your <code>Connection<\/code> <code>Manager<\/code> dialog box should now look similar to the one shown in Figure 14.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image025.png\" width=\"304\" height=\"310\" alt=\"1494-clip_image025.png\" \/><\/p>\n<p class=\"caption\">Figure 14: Configuring an <code>OLE DB<\/code> connection manager<\/p>\n<p>Be sure to click the <code>Test<\/code> <code>Connection<\/code> button to verify that you can connect to the target database. The system will display a message similar to the one in Figure 15 to confirm whether you&#8217;ve successfully connected to the database.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image027.png\" width=\"362\" height=\"67\" alt=\"1494-clip_image027.png\" \/><\/p>\n<p class=\"caption\">Figure 15: Testing your database connection<\/p>\n<p>After you&#8217;ve confirmed your connection, click <code>OK<\/code> to close the message box, and then click <code>OK<\/code> to close the <code>Connection<\/code> <code>Manager<\/code> dialog box. You will be returned to the <code>Configure<\/code> <code>OLE<\/code> <code>DB<\/code> <code>Connection<\/code> <code>Manager<\/code> dialog box, shown in Figure 16.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image029.png\" width=\"330\" height=\"279\" alt=\"1494-clip_image029.png\" \/><\/p>\n<p class=\"caption\">Figure 16: Finalizing your <code>OLE DB<\/code> connection manager<\/p>\n<p>Notice that your new connection has been added to the <code>Data<\/code> <code>connections<\/code> list. Click <code>OK<\/code> to close the dialog box. The <code>Connection<\/code> <code>Managers<\/code> window will show your two connections. You&#8217;re now ready to start working with them.<\/p>\n<h3>Solution Explorer<\/h3>\n<p>Within <code>Solution Explorer<\/code>, you can view all projects<i>, <\/i>packages, data sources and data source views that make up the solution. &#160;&#160;<\/p>\n<h4>Adding New Projects<\/h4>\n<p>If you wish to add an additional project to a package, point to <code>File<\/code> on the menu bar, point to <code>Add<\/code>, and click <code>New Project<\/code>, as shown in Figure 17.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image031.jpg\" width=\"329\" height=\"269\" alt=\"1494-clip_image031.jpg\" \/><\/p>\n<p class=\"caption\">Figure&#160; 17: Adding a new project to a solution<\/p>\n<p>The <code>Add New Project<\/code> window opens. Select <code>Integration Services Project<\/code> and in the Name box enter the name you wish to call the new project as shown in Figure 18.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image033.jpg\" width=\"377\" height=\"240\" alt=\"1494-clip_image033.jpg\" \/><\/p>\n<p class=\"caption\">Figure 18: Add New Project Wizard <\/p>\n<p>As you can see in Figure 19 a new project is added to the solution named &#8220;Dev&#8221; and will appear in <code>Solution Explorer<\/code>. The project will contain three empty folders named <code>Data Sources<\/code>, <code>Data Source Views<\/code> and <code>Miscellaneous<\/code>. The project will also contain a folder named <code>SSIS Packages<\/code> and within the folder a file named <code>Package.dtsx<\/code>, which<i> <\/i>is an empty SSIS package created automatically when the project is created. Figure 19 shows the new project and its folders in <code>Solution Explorer<\/code>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image035.jpg\" width=\"189\" height=\"93\" alt=\"1494-clip_image035.jpg\" \/><\/p>\n<p class=\"caption\">Figure 19: The folders and package created in a new SSIS project<\/p>\n<h4>Data Sources<\/h4>\n<p>Earlier I showed you how to create connections in the <code>Connection Managers<\/code> window. As I mentioned, if a connection is created in the <code>Connection Managers<\/code> window, it is available only to the package it was created in. However, you can also create connections known as <i>data sources,<\/i> which are available to all packages in a project. <\/p>\n<p>To create a new data source, right-click <code>Data Sources<\/code> in <code>Solution Explorer<\/code> to open the <code>Connection Manager<\/code> dialog box (shown in Figure 20). Then fill in the options as you did when you created an <code>OLE DB<\/code> connection manager. Be sure to click <code>Test Connection<\/code> to confirm the connection has been created successfully.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image037.jpg\" width=\"323\" height=\"330\" alt=\"1494-clip_image037.jpg\" \/><\/p>\n<p class=\"caption\">Figure 20: Creating a new data source connection<\/p>\n<p>The <code>Data Source Wizard<\/code> will appear, with the new data connection highlighted, as shown in Figure 21. After you review the settings, click <code>Next<\/code>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image039.jpg\" width=\"328\" height=\"298\" alt=\"1494-clip_image039.jpg\" \/><\/p>\n<p class=\"caption\">Figure 21: The data connection in the <code>Data Source Wizard<\/code><\/p>\n<p>When the next page of the wizard appears, type in a name for the data source. As this is project wide, I would recommend you fully describe the source using the server and database name. I have renamed my data source <code>RGTest_AdventureWorks2008<\/code>, as shown in Figure 22. I try to set up and follow consistent naming conventions.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image041.jpg\" width=\"353\" height=\"320\" alt=\"1494-clip_image041.jpg\" \/><\/p>\n<p class=\"caption\">Figure 22: Renaming the data source<\/p>\n<p>After you&#8217;ve renamed the data source, click <code>Finish<\/code>. Your <code>Data Source<\/code> should now be listed under <code>Data Sources<\/code> in <code>Solution Explorer<\/code>, as shown in Figure 23. Notice that the data source is saved with the .ds file extension to indicate that it is indeed a data source.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image043.jpg\" width=\"198\" height=\"115\" alt=\"1494-clip_image043.jpg\" \/><\/p>\n<p class=\"caption\">Figure 23: Creating a data source in <code>Solution Explorer<\/code><\/p>\n<p>Initially, the new data source is not listed in your package&#8217;s <code>Connection Managers<\/code> window; however, it is available to your package. Once you have made use of the data source in the package it will be visible in the <code>Connection Managers<\/code> window.Data Source Views<\/p>\n<p>Data source views, like data sources, are available to all packages in a project. A data source view is used to define a subset of a data from a data source. The data source view can include only some of the tables or it can be used to define relationships or calculated columns. <\/p>\n<p>Because a data source view is based on a data source, you would normally create the data source before starting to create the data source view. However, this is not compulsory because you can create the data source when you&#8217;re creating the data source view. To create a data source view, right-click the <code>Data Source Views<\/code> folder and click <code>New Data Source View<\/code>, as shown in Figure 24.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image045.jpg\" width=\"198\" height=\"115\" alt=\"1494-clip_image045.jpg\" \/><\/p>\n<p class=\"caption\">Figure 24: Creating a data source view in <code>Solution Explorer<\/code><\/p>\n<p>When the <code>Data Source View Wizard<\/code> appears, click <code>Next<\/code>. The next page shows the data sources available to the project, as shown in Figure 25. (In this case, there&#8217;s only one.)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image047.jpg\" width=\"355\" height=\"321\" alt=\"1494-clip_image047.jpg\" \/><\/p>\n<p class=\"caption\">Figure 25: Available data sources<\/p>\n<p>As you can see, the page shows the name of the data source in the <code>Relational data sources<\/code> list. The properties for the selected data source appear to the right, in the <code>Data source properties<\/code> window. A data source must be selected before you can continue with the wizard. If you haven&#8217;t created the data source you need, you can create one now by clicking the <code>New Data Source<\/code> button.<\/p>\n<p>Once you&#8217;ve selected the necessary data source, click <code>Next<\/code>. The new page provides a list of the tables and views available through the selected data source. If you wish to filter the list, type the filter criteria in the <code>Filter<\/code> text box below the <code>Available Objects<\/code> list, and then click the filter icon to the right of the text box. For example, I typed <code>Emp<\/code> in the <code>Filter<\/code> text box, which reduced the list of available objects to those that contain &#8220;Emp&#8221; in their name, as shown in Figure 26.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image049.jpg\" width=\"303\" height=\"274\" alt=\"1494-clip_image049.jpg\" \/><\/p>\n<p class=\"caption\">Figure 26: Filtering tables and views in the data source<\/p>\n<p>The next step is to determine which tables and views you want to include in your data source view. From the filtered list of tables and views in the <code>Available Objects<\/code> list, select the objects you want to include. You can select more than one object by clicking the first one, holding down the Ctrl key, and then clicking the additional objects. Once you&#8217;ve selected the objects, click the single right arrow button to move those objects to the <code>Included Objects<\/code> window. If you want to move all the listed objects, simply click the double right arrow button.<\/p>\n<p>Once an object has been moved to the <code>Included Objects<\/code> list, the single left arrow button and double left arrow button become active. These work the same as the right arrows. The single left arrow moves a single selected object or multiple selected objects from the <code>Included objects<\/code> list back to the <code>Available objects<\/code> list. The double left arrow moves all objects in the <code>Included objects<\/code> list back to the <code>Available objects<\/code> list. <\/p>\n<p>Figure 27 shows the full list of available objects (without filters), minus the two objects that have been moved to the <code>Included objects<\/code> list. Notice that two additional objects are selected in the <code>Available objects<\/code> window. As you would expect, you can move the files to the <code>Included objects<\/code> list by clicking the single right arrow button.<\/p>\n<p>If you click the <code>Add Related Tables<\/code> button beneath the <code>Included objects<\/code> list, all tables related to the objects in the <code>Included objects<\/code> list will be automatically added.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image051.jpg\" width=\"394\" height=\"357\" alt=\"1494-clip_image051.jpg\" \/><\/p>\n<p class=\"caption\">Figure 27: Adding tables and views to a data source view<\/p>\n<p>Once all required objects have been selected, click <code>Next<\/code>. You can now see a preview of what you have selected, and you can rename the data source view to something more appropriate. If you have missed an object, click the <code>Back<\/code> button to return to the previous page. <\/p>\n<p>For this example, I renamed my data source view <code>AW2008-Employees<\/code>. As you&#8217;re changing the name in the <code>Name<\/code> textbox, the name is also updated in the <code>Preview <\/code>window, as shown in Figure 28.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image053.jpg\" width=\"346\" height=\"313\" alt=\"1494-clip_image053.jpg\" \/><\/p>\n<p class=\"caption\">Figure 28: Renaming the data source view<\/p>\n<p>If you are happy with the configuration, click <code>Finish<\/code>. The data source view is saved with the .dsv<b> <\/b>file extension and is added to the <code>Data Source Views<\/code> folder in <code>Solution Explorer<\/code>. A new window appears in <code>SSIS Designer<\/code> and shows the data source view in design mode, as shown in Figure 29.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image055.jpg\" width=\"602\" height=\"210\" alt=\"1494-clip_image055.jpg\" \/><\/p>\n<p class=\"caption\">Figure 29: Data source view in design mode<\/p>\n<h5>Amending a Data Source View<\/h5>\n<p>SSIS provides a number of options for modifying a data source view. Most of those options are at the table level. If you right-click the table name either on the design surface or in the <code>Tables<\/code> pane (on the left side of the screen), you can choose from the following options:<\/p>\n<ul>\n<li><code>Adding a calculation<\/code>  <\/li>\n<li><code>Adding a relationship<\/code>  <\/li>\n<li><code>Replacing a table<\/code>  <\/li>\n<li><code>Deleting a table<\/code>  <\/li>\n<li><code>Reviewing data<\/code> <\/li>\n<\/ul>\n<h5>Deleting an object<\/h5>\n<p>Suppose I added the <code>Store<\/code> table in error. I can delete from table from my data source view by right-clicking the table name and selecting the <code>Delete table from DSV<\/code> option, as shown in Figure 30.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image057.jpg\" width=\"235\" height=\"170\" alt=\"1494-clip_image057.jpg\" \/><\/p>\n<p class=\"caption\">Figure 30: Deleting a table from a data source view<\/p>\n<p>You&#8217;ll then be prompted to confirm your deletion. When the <code>Delete Objects<\/code> message box appears, click <code>OK<\/code>, as shown in Figure 31.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image059.jpg\" width=\"231\" height=\"175\" alt=\"1494-clip_image059.jpg\" \/><\/p>\n<p class=\"caption\">Figure 31: Deleting objects from a data source view<\/p>\n<p>When you click <code>OK<\/code>, the object is permanently removed from the data source view.<\/p>\n<h5>Adding a new column<\/h5>\n<p>To add a calculated column to a data source view, right-click the table name and select <code>New Named Calculation<\/code> to open the <code>Create Named Calculation<\/code> dialog box. Enter the new column name in the <code>Column name<\/code> text box, add an appropriate description in the <code>Description<\/code> text box, if required, and then create the calculation in the <code>Expression<\/code> text box. For this example, I&#8217;ve assigned the name <code>Age<\/code> to the column and added the description <code>Current Age based on Birth Date<\/code><i>.<\/i> For the expression, I added the one shown in Figure 32. Note that, at this stage, there is no way to test whether your code is correct!<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-Figure32.jpg\" alt=\"1494-Figure32.jpg\" \/><\/p>\n<p class=\"caption\">Figure 32: Creating a calculated column<\/p>\n<p>Figure 33 shows us that the <code>Age<\/code> column has been added to our table. The icon next to the column shows that it is a calculated column.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image063.jpg\" width=\"99\" height=\"223\" alt=\"1494-clip_image063.jpg\" \/><\/p>\n<p class=\"caption\">Figure 33: Verifying that the calculated column has been added<\/p>\n<p>To view the data in the table and verify that the new column has been created correctly, right-click one of the columns and then click <code>Explore Data<\/code>,<i> <\/i>as shown in Figure 34.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image065.jpg\" width=\"142\" height=\"207\" alt=\"1494-clip_image065.jpg\" \/><\/p>\n<p class=\"caption\">Figure 34: Viewing the data in the table<\/p>\n<p>The <code>Explore Employee Table<\/code> window appears,<i> <\/i>as shown in Figure 35. We can now view all the data in the <code>Employee<\/code> table. Notice that the <code>Age<\/code> column has been added to the table (on the far right side) and displays the data returned by our expression.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1494-clip_image067.jpg\" width=\"402\" height=\"169\" alt=\"1494-clip_image067.jpg\" \/><\/p>\n<p class=\"caption\">Figure 35: Viewing data in the <code>Employee<\/code> table<\/p>\n<p>Once you have made all the necessary changes, save the data source view. It will then be available for you to use in any of your packages in the project.<\/p>\n<h2>Summary<\/h2>\n<p>In this article, I&#8217;ve shown you how to create an SSIS package and set up connection managers, data sources, and data source views. In the next article, I will show you how to set up a package that retrieves data from a SQL Server database and loads it into an Excel file. I will also show you how to add a derived column that calculates the data to be inserted into the file. In addition, I will demonstrate how to run the package.<\/p>\n<p>In future articles, I plan to show you how to deploy the package so it can be run as part of a scheduled job or called in other ways. I also plan to cover how to use variables and how they can be passed 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<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>When working with databases, the use of SQL Server Integration Services (SSIS) is a skill that often needs to be acquired quickly, from scratch. Up until now, it has been a curiously frustrating search to find  out the basics, fast, in order to get up and running quickly. No longer, as Annette comes up with a simple introduction for the rest of us.&hellip;<\/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,4168,4150,4151,4306],"coauthors":[],"class_list":["post-1340","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-basics","tag-database","tag-sql","tag-sql-server","tag-ssis"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1340","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=1340"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1340\/revisions"}],"predecessor-version":[{"id":91230,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1340\/revisions\/91230"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1340"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1340"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1340"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1340"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}