Tutorial: Creating a Basic Package Using a WizardMicrosoftIntegration Services provides the SQL Server Import and Export Wizard for building packages that perform data transfers. These packages can extract data from a source and load it into a destination, but the package can perform only minimal data transformation in the transfer process. In addition, the wizard is a quick way to create basic packages that can then be enhanced in SSIS Designer.In this tutorial, you will learn how to use the SQL Server Import and Export Wizard to create a basic package. The package that you create extracts data from an Excel workbook and loads it into a table in the AdventureWorks2008R2database. The table is defined as one of the steps in the wizard and then created dynamically when you run the package.In subsequent lessons, the package will be expanded to include a data flow that sorts the data, creates a new column, and populates the column with values. To generate the new values, you will learn how to use the new Integration Services expression language together with the graphical expression builder to write an expression that creates new values based on existing data columns.When you install the sample data that the tutorial uses, you also install the completed versions of the packages for each lesson of the tutorial. By using the completed lesson 1 package, you can skip ahead and begin the tutorial with lesson 2 if you like. If this is your first time working with packages, the SQL Server Import and Export Wizard, or the new development environment, we recommend that you begin with lesson 1.What You Will Learn

The best way to become acquainted with the new tools, controls, and features available in MicrosoftSQL ServerIntegration Services is to use them. This tutorial first walks you through the SQL Server Import and Export Wizard to create a basic data-transfer package, and then shows you how to enhance the data transformation capabilities of the package by using SSIS Designer.Requirements

This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in SQL ServerIntegration Services.To use this tutorial, your system must meet the following requirements: You must run the package that this tutorial creates in 32-bit mode. This sample uses the MicrosoftJet 4.0 OLE DB provider, for which there is no 64-bit version. The package fails if you run it in 64-bit mode.For more information about running packages in 32-bit mode on a 64-bit computer, see64 bit Considerations for Integration Services. SQL Server with the AdventureWorks2008R2 database must be installed on the computer. To enhance security, the sample databases are not installed by default. To install the sample databases, seeConsiderations for Installing SQL Server Samples and Sample Databases. You must have permission to create and drop tables in AdventureWorks2008R2. The sample data must be installed on the computer. The sample data is installed together with the samples. If you cannot find the sample data, return to the procedure above and complete installation as described.Note

When reviewing tutorials it is recommended you addNextandPreviousbuttons to the document viewer toolbar. For more information, seeAdding Next and Previous Buttons to Help.

This tutorial assumes that you have not reconfigured SSIS Designer to use auto-connect features between control flow elements or between data flow elements. If SSIS Designer uses auto-connect, an element may be connected automatically when added to the design surface. Also, the auto-connect feature for control flow supports the use ofFailureandCompletionas the default constraint, instead ofSuccess. If SSIS Designer is not usingSuccessas its default constraint, you should reset this configuration while doing the tutorial. You configure the auto-connect features in theBusiness Intelligence Designerssection in theOptionsdialog box that is available fromOptionson theToolmenu.Estimated time to complete this tutorial:1 hourLessons in This Tutorial

Lesson 1: Creating the Basic PackageIn this lesson, you will use the SQL Server Import and Export Wizard to create a data-transfer package.Lesson 2: Enhancing the Basic PackageIn this lesson, you will enhance the basic package to include a sort operation, and to add a new column and column values to the existing dataset.

Lesson 1: Creating the Basic PackageSQL Server 2008 R2Other Versions

In this lesson, you will create a basic package by using the SQL Server Import and Export Wizard. The package selects and extracts data from an Excel spreadsheet and writes that data to the ProspectiveCustomers table in the AdventureWorks2008R2sample database. The table is defined in the wizard and created when you run the package.The SQL Server Import and Export Wizard will be run in Business Intelligence Development Studio and you will launch the wizard from an Integration Services project.After you complete the SQL Server Import and Export Wizard the package is added to the Integration Services project. You will open the package in SSIS Designer, the Integration Services graphical tool for building complex packages, and verify that certain properties of the package are configured correctly.Finally, you will test the package by running it in Business Intelligence Development Studio.Important

This tutorial requires the AdventureWorks2008R2 sample database. For more information on installing and deploying AdventureWorks2008R2, seeConsiderations for Installing SQL Server Samples and Sample Databases.

Lesson Tasks

This lesson contains the following tasks: Step 1: Creating the Integration Services Project Step 2: Running the Wizard to Create the Basic Package Step 3: Testing the Lesson 1 Basic PackageStep 1: Creating the Integration Services ProjectSQL Server 2008 R2Other Versions

This tutorial uses the SQL Server Import and Export Wizard to create a basic package by running the wizard in Business Intelligence Development Studio. The wizard is launched from an Integration Services project. In this task you will create an Integration Services project.To create the Integration Services project1. On theStartmenu, point toAll Programs, point toMicrosoft SQL Server, and clickSQL ServerBusiness Intelligence Development Studio.2. On theFilemenu, point toNew, and clickProjectto create a new Integration Services project.3. In theNew Projectdialog box, selectIntegration Services Projectin theTemplatespane.4. In theNamebox, change the default name toSQL Server Import and Export Wizard Tutorial. Optionally, clear theCreate directory for solutioncheck box.5. Accept the default location, or clickBrowseto browse to locate the folder you want to use.6. In theProject Locationdialog box, click the folder and clickOpen.7. ClickOK.8. By default, an empty package, named Package.dtsx, is created and added to every new project. However, you will not use this package because the wizard will create its own package. You can delete Package.dtsx, or use it as the basis for a different package later. To delete it, right-click it and then clickDelete.Step 2: Running the Wizard to Create the Basic PackageSQL Server 2008 R2Other Versions

In this task, you will run the SQL Server Import and Export Wizard to create the basic data transfer package. Specify the data source. You will use the Excel workbook, Customers.xls, as the data source. On a wizard page you will provide a query that selects the customers who own at least one car from the Customers spreadsheet in the workbook. Specify the destination. You will write the data to a table in the AdventureWorks2008R2 database. The table, ProspectiveCustomers, will be created dynamically. You will define the metadata of the destination columns by using the wizard.After the wizard completes, you will rename the package and verify the values of certain locale-sensitive properties of the package, tasks, source, and destination. This step is important because the Excel workbook includes locale-sensitive date data in the BirthDate column. If the regional settings on your computer do not specify English (United States), you must update the values of the specified properties or the package cannot be run successfully.To run the SQL Server Import and Export Wizard1. If it is not already open, open the Integration Services project that you created in the previous task.2. In Solution Explorer, right-click theSSIS Packagesfolder and clickSSIS Import and Export Wizard. If the Solution Explorer window is not open, clickSolution Exploreron theViewmenu.3. On theWelcomepage of the SQL Server Import and Export Wizard, clickNext.4. On theChoose a Data Sourcepage, do the following steps:a. In theData sourcelist, select Microsoft Excel.b. ClickBrowse, navigate to C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Basic Package\Sample Data, click the Excel workbook file, Customers.xls, and then clickOpen.c. Verify that theExcel file pathbox contains "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Basic Package\Sample Data\Customers.xls".d. Verify that theExcel versionbox contains Microsoft Excel 97-2003 and theFirst Row has column namescheck box is selected.5. On theChoose Destinationpage, do the following steps:a. In theDestinationlist, SelectSQL Server Native Client, and in theServer namebox, typelocalhost.When you specify localhost as the server name, the connection manager connects to the default instance of SQL Server on the local computer. To use a remote default instance or a named instance of SQL Server, replace localhost with the name of the server or server and named instance to which you want to connect. To connect to a named instance use the format \.b. If the instance of the Database Engine that you specified supports Windows Authentication, use the default Windows Authentication mode; otherwise, clickUse SQL Server Authenticationand type a user name in theUser namebox and a password in thePasswordbox.c. In theDatabaselist, select AdventureWorks2008R2.6. On theSpecify Table Copy or Querypage, clickWrite a query to specify the data to transfer.7. On theProvide a Source Querypage, in theSQL statementbox, type or copy the following SQL statement:8. SELECT * FROM [Customers$] WHERE NumberCarsOwned > 09. On theSelect Source Tables and Viewspage, do the following steps:a. In theDestinationlist, click[dbo].[Query], and then change the table name,Query, toProspectiveCustomers.b. To edit column metadata and table options, clickEdit Mappings.10. On theColumns Mappingspage, do the following steps:a. Verify that theCreate Destination tableoption is selected, select theDrop and re-create destination tablecheck box, and modify the metadata of the destination columns.The following table lists the columns and the metadata changes that you need to make:Column nameDefault typeUpdated typeDefault sizeUpdated size

FirstNamenvarcharNo change25550

MiddleIntialnvarcharnchar2551

LastNamenvarcharNo change25550

BirthDatedatetimeNo changeN/AN/A

MaritalStatusnvarcharnchar2551

Gendernvarcharnchar2551

EmailAddressnvarcharNo change25550

YearlyIncomefloatmoneyN/AN/A

TotalChildrenfloattinyintN/AN/A

NumberChildrenAtHomefloattinyintN/AN/A

EducationnvarcharNo change25550

OccupationnvarcharNo change25550

HouseOwnerFlagfloatbitN/AN/A

NumberCarsOwnedfloattinyintN/AN/A

AddressLine1nvarcharNo change25560

AddressLine2nvarcharNo change25560

CitynvarcharNo change25530

StatenvarcharNo change2553

ZIPfloatNo changeN/AN/A

PhonenvarcharNo change25550

b. ClickOK.11. On theReview Data Type Mappingpage, accept the default settings and clickNext.12. On theComplete the Wizardpage, review information about the new package and clickFinish.13. On thePerforming Operationspage, view the actions that the wizard performs. When finished, theStatuscolumn for each action should display Success.14. ClickClose.15. In Solution Explorer, right-click the new package in the SSIS Packages folder, clickRename, and typeBasic PackageLesson 1. Make sure that the name includes the .dtsx extension.16. If asked whether to rename the object as well, clickYes.To set locale sensitive properties of the package1. Double click Basic Package Lesson 1.dtsx, click theControl Flowtab, and then click anywhere on the background of the design surface.2. On theViewmenu, clickProperties Window.3. In the Properties window, verify that theLocaleIDproperty is set toEnglish (United States).To set locale sensitive properties of the Execute SQL tasks1. On the Control Flow design surface, clickDrop Table(s) SQL Task.2. On theViewmenu, clickProperties Window.3. In the Properties window, verify that theCodepageproperty is set to1252and theLocaleIDproperty is set toEnglish (United States).4. Repeat steps 1-3 forPreparation SQL Task.To set locale sensitive properties of the Data Flow task1. ClickData Flow Task.2. On theViewmenu, clickProperties Window.3. In the Properties window, verify that theLocaleIDproperty is set toEnglish (United States).To set locale sensitive properties of data flow components1. Double-clickData Flow Taskor click theData Flowtab.2. On the Data flow tab, clickSource - Query.3. On theViewmenu, clickProperties Window.4. In the Properties window, verify that theLocaleIDproperty is set toEnglish (United States).5. Repeat steps 2-4 forDestination - ProspectiveCustomers.Step 3: Testing the Lesson 1 Basic PackageSQL Server 2008 R2Other Versions

In this lesson, you have done the following tasks: Created a new Integration Services project. Run the SQL Server Import and Export Wizard to create the basic package. Verified and updated the values of locale sensitive properties.Your package is now complete! It is time to test your package.Important

The first time you run the package, the task named Drop table(s) SQL Task will fail. This behavior is expected. The reason the task fails is that the package attempts to drop and re-create the ProspectiveCustomers table; however, the first time that the package runs the table does not exist and the DROP statement fails. This does not cause the package to fail because the precedence constraint between the Drop table(s) SQL Task and Preparation SQL tasks has been set to Completion rather than Success.

Checking the Package Layout

Before you test the package you should verify that the control and data flows in the lesson 1 package, Basic Package Lesson 1.dtsx, contain the objects shown in the following diagrams.Control Flow

Data Flow

Also, the package should include the following two connection managers. One connects to the Excel workbook file Customers.xls and the other one connects to the AdventureWorks2008R2 database.

To run the Lesson 1 package1. In Solution Explorer, click Basic Package Lesson 1.dtsx.2. On theDebugmenu, clickStart Debugging.The package will run, resulting in 809 rows successfully added into the ProspectiveCustomers table in AdventureWorks2008R2.3. After the package has completed running, on theDebugmenu, clickStop Debugging.Lesson 2: Enhancing the Basic PackageSQL Server 2008 R2Other Versions

InLesson 1: Creating the Basic Package, you used the SQL Server Import and Export Wizard to get a quick start on a basic Integration Services package. The package has limited functionality; it only extracts data from an Excel workbook file and loads the data into the ProspectiveCustomers table of the AdventureWorks2008R2sample database.Typically, a package also needs to manipulate and transform the data. Integration Services provides a wealth of transformations that you can use to copy, cleanse, modify, sort, and aggregate data. If you need to transform data in ways that are not supported by the standard transformations, you can easily write a script for the Script transformation or code a custom transformation to address your needs.In this lesson you will enhance the basic package to sort the data and add a new column based on values from other columns to the dataset. In this scenario, one column contains null values, which present problems when concatenating values from existing columns. To work around this problem and generate the value for the new column, you will use a new Integration Services featureexpressions. The Integration Services expression language includes functions, operators, and type casts that you can use to build complex expressions. You will use an expression to concatenate the values from three columns and conditionally insert a space between columns, and then add the new value to the new column.Because a new column is added to the dataset, the ProspectiveCustomers table and the OLE DB destination must be modified to include this column. You will update both the SQL statement in the Execute SQL task that created the ProspectiveCustomers table, and the OLE DB destination that writes data to the table, to include this new column. You will also map the new column in the dataset to the new column in the table.In this lesson, you will copy and then enhance the basic created in Lesson 1. If you have not completed the previous lesson, you can also copy the completed package for Lesson 1 that is included with the tutorial.Important

This tutorial requires the AdventureWorks2008R2 sample database. For more information about how to install and deploy AdventureWorks2008R2, seeConsiderations for Installing SQL Server Samples and Sample Databases.

Lesson Tasks

This lesson contains the following tasks: Step 1: Copying the Lesson 1 Basic Package Step 2: Updating the Execute SQL Task Step 3: Adding and Configuring the Sort Transformation Step 5: Modifying the OLE DB Destination Step 6: Testing the Lesson 2 Basic PackageStep 1: Copying the Lesson 1 Basic PackageSQL Server 2008 R2Other Versions

In this task, you will create a copy of the package that you created in Lesson 1, named Basic Package Lesson 1.dtsx. If you did not complete Lesson 1, you can add the completed Lesson 1 package that is included with the tutorial to the project, and then copy it instead. You will use this new copy throughout the rest of Lesson 2.To create the Lesson 2 package1. If Business Intelligence Development Studio is not already open, clickStart, point toAll Programs, point toMicrosoft SQL Server, and then clickBusiness Intelligence Development Studio.2. On theFilemenu, clickOpen, clickProject/Solution, click theSQL Server Import and Export Wizard Tutorialfolder and clickOpen, and then double-clickSQL Server Import and Export Wizard Tutorial.sln.3. In Solution Explorer, right-clickBasic PackageLesson 1.dtsx, and then clickCopy.4. In Solution Explorer, right-clickSSIS Packages, and then clickPaste.By default, the copied package will be named Basic Package Lesson2.dtsx.5. In Solution Explorer, double-clickBasic PackageLesson 2.dtsxto open the package.6. Right-click anywhere in the background of theControl Flowdesign surface and clickProperties.7. In the Properties window, update theNameproperty toBasic Package Lesson 2.8. Click the box for theIDproperty, and then in the list, click.To add the completed Lesson 1 package1. Open Business Intelligence Development Studio and open the SQL Server Import and Export Wizard Tutorial project.2. In Solution Explorer, right-clickSSIS Packages, and clickAdd Existing Package.3. In theAdd Copy of Existing Packagedialog box, inPackage location, selectFile system.4. Click the browse()button, navigate to C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Basic Package\Completed Packages, selectBasic Package Lesson 1.dtsx, and then clickOpen.5. Copy and paste the Basic Package Lesson 1 package as described in steps 3-8 in the previous procedure.Step 2: Updating the Execute SQL TaskSQL Server 2008 R2Other Versions

In this task, you will update the SQL statement in the Execute SQL task named Preparation SQL Task. The existing SQL statement was automatically generated from the options you specified when you stepped through the SQL Server Import and Export Wizard pages to create the lesson 1 package. This SQL statement creates the Query table in the AdventureWorks2008R2 database when the package is run.Later in this lesson, you will generate an additional column to the data that is extracted from the Excel spreadsheet, and you need to include a definition of that column in the SQL statement.To modify the SQL statement1. Click theControl Flowtab.2. Double-clickPreparation SQL Task.3. On the General page, click theSQLStatementproperty, and then click the browse button().4. In theEnter SQL Querydialog box, add a comma at the end of the line,[Phone] nvarchar (50), press Enter, and on the new line, type[FullName] nvarchar (103).The completed SQL statement should look like this:CREATE TABLE [AdventureWorks2008R2].[dbo].[Query] ([FirstName] nvarchar(50),[MiddleInitial] nchar(1),[LastName] nvarchar(50),[BirthDate] datetime,[MaritalStatus] nchar(1),[Gender] nchar(1) NOT NULL,[EmailAddress] nvarchar(50),[YearlyIncome] money,[TotalChildren] tinyint,[NumberChildrenAtHome] tinyint,[Education] nvarchar(50),[Occupation] nvarchar(50),[HouseOwnerFlag] bit,[NumberCarsOwned] tinyint,[AddressLine1] nvarchar(60),[AddressLine2] nvarchar(60),[City] nvarchar(30),[State] nchar(3),[ZIP] float,[Phone] nvarchar(50),[FullName] nvarchar (103))GO5. ClickOK.6. ClickParse Query. The SQL statement should parse successfully.7. ClickOK.Step 3: Adding and Configuring the Sort TransformationSQL Server 2008 R2Other Versions

In this task, you will add and configure a Sort transformation to your package. A Sort transformation is a data flow component that sorts data, and optionally applies rules to the comparison that the sort performs. The sort transformation can also be used to remove rows of data that have duplicate sort key values.The sort transformation will sort the data extracted from the Excel spreadsheet by state and by city.To add a Sort transformation1. Open theData Flowdesigner, either by double-clicking Data Flow Task or by clicking theData Flowtab.2. Right-click the path (the green arrow) betweenData ConversionandDestination - Queryand then clickDelete.3. In theToolbox, expandData FlowTransformations, and then dragSortonto the design surface of theData Flowtab, belowData Conversion. IfDestination - Queryis in the way, click it and drag it to a position lower on theData Flowdesign surface.4. On theData Flowdesign surface, clickSortin the Sort transformation, and change the name toSort by State and City.5. ClickSource - Queryand drag its green arrow toSort by State and City.6. Double-clickSort by State and Cityto open theSort Transformation Editordialog box.7. In theAvailable Input Columnslist, first select the check box to the left of theStatecolumn, and then the select the check box by theCitycolumn.The columns now appear in theInput Columnlist.Statehas the sort order 1 andCityhas the sort order 2. This means that the dataset is sorted first by state and then by city.8. In theInput Columnlist, click the row that contains State. Click theComparison Flagsbox, select theIgnore casecheck box, and then clickOK.9. ClickOK.10. Right-clickSort by State and Cityand then clickProperties.11. In the Properties window, verify that theLocaleIDproperty is set toEnglish (United States).Step 4: Adding and Configuring the Derived Column TransformationSQL Server 2008 R2Other Versions

In this task, you will add a Derived Column transformation to your package. A Derived Column transformation is a data flow component that creates new data values by using values in a dataset, constants, and variables, or by applying functions. You will use this transformation to add a new column and then populate the column with the evaluation results of an expression.The user interface for the Derived Column transformation includes the expression builder. This graphical tool makes it easy to quickly write complex expressions using drag and drop operations, and provides templates for functions, type casts, and operators as well as the input columns and variables.In the Derived Column transformation, you will create an expression that concatenates the values in theFirstName,MiddleInitial, andLastNamecolumns in the dataset and then writes the result to a new column. Because the middle initial may be null, the expression will include special handling of this column. The new column,FullName, will be added to the transformation output.To add a Derived Column transformation1. If not already open, open theData Flowdesigner, either by double-clickingData Flow Taskor by clicking theData Flowtab.2. In theToolbox, expandData FlowTransformations, and then drag aDerived Columntransformation onto the design surface of theData Flowtab, belowSort by State and City.3. On theData Flowdesign surface, clickDerived Columnin the Derived Column transformation, and change the name toAdd FullName Column.4. ClickSort by State and Cityand drag its green arrow toAdd FullName Column.5. Double-clickAdd FullName Columnto open theDerived Column Transformation Editordialog box.6. In the left pane, expand the Columns folder, click theFirstNamecolumn and drag it to theExpressionbox.7. In theExpressionbox, after [FirstName], type+ " " +.8. In the Columns folder, click theMiddileInitialcolumn and drag it to theExpressionbox.9. Update [MiddleInitial] to(ISNULL(MiddleInitial) ? "" : MiddleInitial + " ") +.10. In the Columns folder, click theLastNamecolumn and drag it to theExpressionbox.11. Verify that the value in theExpressionbox is the following:FirstName + " " + (ISNULL([MiddleInitial]) ? "" : [MiddleInitial] + " ") + [LastName]You may optionally remove the brackets that enclose column names in the expression. The column names are regular identifiers, which do not need to be enclosed in brackets. Names that contain invalid characters, such as spaces, must be enclosed in brackets. If the expression has been typed incorrectly, the expression text will appear in red.12. In theDerived Columnbox for the row you just created, select.13. In theDerived Column Namebox for the same row, typeFullName.14. If theData Typebox is not already set to Unicode string [DT_WSTR], selectUnicode string [DT_WSTR]in theData Typelist.15. Set the value of theLengthbox to 103 (the sum of the lengths of theFirstName,MiddleInitial,LastNamecolumns, and two spaces.)16. ClickOK.17. In the Properties window, verify that theLocaleIDproperty is set toEnglish (United States).Step 5: Modifying the OLE DB DestinationSQL Server 2008 R2Other Versions

Earlier in lesson 2, you updated the SQL statement in the Execute SQL task,Preparation SQL Task, to include a definition of theFullNamecolumn in theQuerytable. In this task, you will modify the OLE DB destination,Destination - Query, to support theFullNamecolumn.You will also restore the column mappings inDestination - Querythat are no longer valid because you added a Sort transformation to the data flow. The Sort transformation generates a new set of columns with different column identifiers, and you therefore need to remap the input columns and destination columns inDestination - Query.To modify the OLE DB destination1. If not already open, open theData Flowdesigner, either by double-clickingData Flow Taskor by clicking theData Flowtab.2. Click the Derived Column transformation namedAdd FullName Columnanddrag its green arrow toDestination - Query.3. Double-clickDestination - Query.4. In theRestore Invalid Column Reference Editordialog box, clickSelect All, select theoption in theColumn mapping option for selected rowslist, and then clickApply.You can clear theInclude downstream invalid column referencescheck box. In this package, there are no downstream data flow components and this option has no effect.5. ClickOK.6. Right-clickDestination - Queryand clickShow Advanced Editor.7. In theAdvanced Editordialog box, click theInput and Output Propertiestab, expandDestination Input, clickExternal Columns, and then clickAdd Column.A new column namedColumnis added to theExternal Columnsfolder.8. Click the new column.9. In the right-hand pane, update theNameproperty toFullName, click theDataTypeproperty and selectUnicode string [DT_WSTR]from the list. Update theLengthproperty to103.10. Click theColumns Mappingstab, and scroll down to the row withFullNamein theDestination Columnlist. Clickin theInput Columnlist of that row, and then clickFullNamein the list.11. Verify that all input and output columns that have the same names are mapped.12. ClickOK.Step 6: Testing the Lesson 2 Basic PackageSQL Server 2008 R2Other Versions

In this lesson, you have done the following tasks: Updated the SQL statement in the Execute SQL task to include a definition for an additional column. Added and configured a Sort transformation to sort the dataset by state and then by city. Added a Derived Column transformation and configured it to use an expression to generate values for a new column. Modified the OLE DB destination to write the new column, FullName, to the ProspectiveCustomers table.Your package is now complete! It is time to test your package.Important

The first time you run the package, the Drop table(s) Task will fail. This behavior is expected. The reason this happens is that the package attempts to drop and re-create the ProspectiveCustomers table; however, the first time that the package runs the table does not exist and the DROP statement fails.

Checking the Package Layout

Before you test the package, you should verify that the control and data flows in the Lesson 1 package contain the objects shown in the following diagrams.Control Flow

Data Flow

Also, the package should include the following two connection managers. One connects to the customers.xls Excel workbook file and the other one connects to the AdventureWorks2008R2 database.

To run the Lesson 2 package1. In Solution Explorer, click Basic Package Lesson 2.dtsx.2. On theDebugmenu, clickStart Debugging.The package will run, resulting in 809 rows successfully added into the Query table in the AdventureWorks2008R2 database.3. After the package has completed running, on theDebugmenu, clickStop Debugging.To verify the contents of the ProspectiveCustomers table1. On theStartmenu, point toAll Programs, point toMicrosoft SQL Server, and clickSQL ServerManagement Studio.2. In theConnect to Serverdialog, selectDatabase Enginein theServer typelist, provide the name of the server on which the AdventureWorks2008R2database is installed in theServer namebox, and select an authentication mode option. If you select SQL Server Authentication, provide a user name and a password.3. ClickConnect. SQL Server Management Studio opens.4. On the toolbar, clickNew Query.5. Type or copy the following query in the query window.SELECT * FROM AdventureWorks2008R2.dbo.ProspectiveCustomers6. On the toolbar, clickExecute. TheResultspane shows the dataset, including the new FullName column. You can verify that your expression formatted the column value correctly depending on whether the middle initial is null.