Basics on creating SSIS packages 24/12/2009

Banking & financial Services Jyothi Prasad Vemulapalli [email protected]

TCS Public

Basics on creating SSIS packages

REVISION LIST
Document Name: Basics on Creating SSIS Packages Version Author Date Draft Jyothi Prasad V 12/24/2009 Changes Done Initial Draft

TCS Confidential

Basics on creating SSIS packages

TABLE OF CONTENTS

BUSINESS INTELLIGENCE DEVELOPMENT STUDIO: ..................................................................................4 BIDS CONSISTS OF FOLLOWING FOUR MAIN PANES: ..............................................................................................4 Tool Box Pane:............................................................................................................................................4 SSIS Designer Pane:...................................................................................................................................4 Solution Explorer Pane: .............................................................................................................................5 Properties Pane:..........................................................................................................................................5 SSIS PACKAGES: ......................................................................................................................................................5 INTRODUCTION TO SSIS: .........................................................................................................................................5 SSIS FEATURES:.......................................................................................................................................................6 SSIS LOGGING: ........................................................................................................................................................6 ADVANTAGES OF SSIS:...........................................................................................................................................6 BENEFITS AND DRAWBACKS OF SSIS PACKAGE: ................................................................................................11 DIFFERENCE BETWEEN CONTROL FLOW AND DATA FLOW:................................................................11 DIFFERENCE BETWEEN DTS AND SSIS PACKAGE: ....................................................................................12 BUILDING A PACKAGE:.......................................................................................................................................12 USING CONTROL FLOW OR DATA FLOW: .............................................................................................................12 SQL SERVER IMPORT AND EXPORT WIZARD: ............................................................................................13 EXECUTING PACKAGES: ....................................................................................................................................14 CREATING SSIS PACKAGE: ................................................................................................................................15 CREATING A DATA FLOW PACKAGE: ............................................................................................................16 SUMMARY: ..............................................................................................................................................................18 CONCLUSION:.........................................................................................................................................................19

TCS Confidential

Basics on creating SSIS packages

Business Intelligence Development Studio: It is shortly called as a BIDS, a version of Microsoft Visual Studio 2005 that is focused on SQL SERVER 2005 business intelligence. It is not restricted to working on SSIS packages. A Primary environment like user uses to develop the projects on Analysis Service (SSAS) and Reporting Service (SSRS). Business Intelligence Development Studio (BIDS) projects are stored with in Solutions. It allows creating the projects for existing solutions also. BIDS automatically creates a solution for the user when user initiates a project. This Document focuses on SSIS, to initiate a project in which user create on SSIS package, first launch the BIDS by choosing the SQL SERVER Business Intelligence Development Studio from the Microsoft SQL SERVER 2005 program menu. FILENEWPROJECT. The New Project dialogue box opens. From the Visual Studio Installed Templates pane of this dialog box, select Integration Services Project. Type a name for the project then click on OK. After user started an Integration Service Project, user are presented with the Microsoft Visual Studio Window followed by his project Name. BIDS Consists of following four main panes: Tool Box Pane: Located in the left side of the window, It contains items that user can use in construction projects. The availability of the items is dependent on the present task. When user editing an SSIS packages Control Flow Items and Maintenance Plan Tasks selections are the only selections available in the Toolbox. When user editing an SSIS packages Data Flow Sources, Data Flow Transformations, and Data Flow Destinations are the only selections available in the Toolbox. SSIS Designer Pane: Located in the middle of the window, this pane is creating or modifies the business intelligence objects. Create the Control Flow in a package. Create the Data Flow in a package. Add event handlers to the package and package objects. View package content.

TCS Confidential

Basics on creating SSIS packages

View the execution progress of a package. Solution Explorer Pane: Located in the top at right side of the window, it contains all items associated with the present project. Properties Pane: Located in the bottom right of the window, it contains the properties of an object.

SSIS Packages: A package is a collection of tasks and workflow elements. The order of the task execution is depend on the outcome of earlier steps in the workflow, Depending on the result tasks execution, a branch might occur. For example, if task first task successfully executes, second task executes next. When user finish creating packages, user can save them to a SQL SERVERSS database or to an XML structured.DTSX file. Packages include one or more of the following components: Checkpoints and Restarts Configurations Connection Managers Control Flow Elements Data Flow Elements Data tasks Event handlers Logging Security Settings SSIS Variables Transaction attributes.
Introduction to SSIS:

SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005.It is the replacement of Data Transformation Services (DTS) (a feature common since Version 7.0 of SQL Server). Microsoft SQL Server 2005 Integration Services (SSIS) is a platform where we can use to transfer, transform, and consolidate our information from multiple sources and load it to multiple systems. As the product features a fast and flexible data extraction, transformation, and loading engine, the primary use of SSIS is DATAWAREHOUSING. Similar to DTS, SSIS provides for moving data from one place to another and manipulating that data at runtime. The SQL SERVER Management Studio (SSMS) is an environment for managing the storage and execution of deployed packages. The Business Intelligence Design Studio (BIDS) is an environment for designing packages.

TCS Confidential

Basics on creating SSIS packages

SSIS Features: Some of the SSIS Features are: We can use SSIS to transfer lakhs of rows of data and from multiple sources. SSIS is integrated with SQL Server Reporting Services integration, which lets us to treat an SSIS package as the data source for reporting. SSIS now fully supports the Microsoft .NET framework, allowing developers to program SSIS in their choice of .NET supported languages.

SSIS Logging: SSIS Package logging provides a file that traces the execution of packages. This implies that a package logs entries into a file (text, XML, etc,) about the execution of package. The SSIS takes the input from the configuration file and logs the information with the specified file name and in the specified location. Advantages Of SSIS: SQL Server Integration Services (SSIS) is a new, highly scalable platform for building high performance data integration solutions, including extraction, transformation and load (ETL) packages for data warehousing. SQL Server Integration Services (SSIS) replaces Data Transformation Services (DTS), which was first introduced as a component of SQL Server7.0.DTS provided only a limited set of tasks and transformations. It was a difficult challenge for the developer to construct packages with complex and repeating workflows, and to apply different types of column-level transformations to data. DTS designer that was used to create packages combined the workflow and data flow on a single design surface and offered limited control flow options. In SQL Server Integration Services (SSIS), solves many of the difficulties and limitations of DTS.SSIS enhancements include a new extensible architecture, a new package designer, and a multitude of new tasks, looping structures, and transformations and also improvements in package deployment, management, and performance. Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as SQL command execution, FTP operations, and e-mail messaging; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying

TCS Confidential

Basics on creating SSIS packages

data; a management service for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model. SQL Server Integration Services (SSIS) introduces new features and enhancements that increase the power and productivity of developers, administrators, and knowledge workers who develop data transformation solutions: Graphical tools such as SSIS Designer and the SQL Server Import and Export Wizard. The ability to create packages programmatically and to extend the Integration Services object model by using custom tasks, sources, destinations, and transformations.

SQL Server Data Transformation Services (DTS) combined data transformation, tasks, and package control flow into a single component. This made the creation of complex packages difficult. In SQL SERVER 2005, the SSIS architecture separates data flow from control flow by introducing two different engines, the Integration Services run-time engine and the Integration Services data flow engine. This separation provides better control of package execution, increases the execution of data transformations, and enhances the extensibility of SSIS by simplifying the creation and implementation of custom tasks and transformations. Services run-time engine stores package layout, executes packages, controls workflow between tasks, and provides run-time services such as debugging, logging, event handling, and management of connections, variables, and transactions.

Services data flow engine satisfies the needs of enterprises whose extraction, transformation, and loading (ETL) processes require fast, flexible, extensible, and dependable data movement. It is optimized for high-performance data movement and transformation. This data flow engine supports multiple sources, multiple transformations, and multiple destinations in one fast, flexible data flow. Integration Services includes more than 25 transformations and more than 10 sources and destinations for use in data flows. The Data Flow task, which represents the Integration Services data flow engine graphically in SSIS Designer, replaces various DTS data-oriented tasks, such as the Data Transformation task and the Data Driven Query task. A powerful integrated development environment (IDE) is combined with support for the .NET Framework to accelerate the creation of powerful custom Integration Services tasks, transformations, and data adapters. The custom extensions that can be developed include tasks, log providers, enumerators, connection managers,

TCS Confidential

Basics on creating SSIS packages

and data flow components. These custom objects can be integrated into the user interface of Business Intelligence Development Studio. The Integration Services run-time API and data flow API let developers extend and customize. New or existing Integration Services packages can be loaded, modified, and executed programmatically, giving developers the ability to fully automate package maintenance and execution. SSIS Designer provides an integrated developer experience for designing, creating, testing, and debugging Integration Services packages. The user interface of SSIS Designer helps us to build and configure packages by using drag-and-drop methods and by selecting options in dialog boxes for each package object. Separate design surfaces for package control flow, data flow, and event handlers, just like the Visual Studio A top to down view of package content in Solution Explorer. Containers that can be expanded and collapsed for grouping related tasks, allowing easier viewing, organization, and management of package layout, which allows us to see the layout package and easily identify the file which needs to be corrected during bug-fixing and layout change . Instructions that make package control flow, data flows, and event handlers self-explanatory so that they can be easily understandable to others. Dialog boxes for adding custom variables, configuring logging, creating configurations, and signing packages with digital signatures. Debugging tools that provide the ability to set breakpoints on packages, containers, and task events, and data viewers for watching data as it moves through the data flow. A progress window that lists the start time of a package and its tasks, their execution state, and any warnings and error messages. A graphical representation of package execution including the progress, precedence, and execution outcome of individual tasks, containers, and data flow components. SQL Server 2005 introduces Business Intelligence Development Studio (BIDS) for building data transformation solutions and SQL Server Management Studio for managing Integration Services packages. Business Intelligence

TCS Confidential

Basics on creating SSIS packages

Development Studio hosts SSIS Designer, the graphical tool for creating Integration Services packages, and provides all the powerful features of the Visual Studio development environment to the SQL Server Integration Services (SSIS) developer. The redesign of the SQL Server Import and Export Wizard helps on copying data. This wizard is the simplest way to quickly create SQL Server Integration Services (SSIS) packages that copy data between two data stores. The SQL Server Import and Export Wizard include many new features, including better support for data in flat files and real-time preview of data. Saved packages created by using the SQL Server Import and Export Wizard can be opened in Business Intelligence Development Studio and extended by using SSIS Designer. Many new workflow features, including containers for looping, event handlers, and enhanced precedence constraints, give the package developer more accurate control over package execution. Workflow containers provide structure to packages and services to tasks. They support repeating control flows in packages, and they group tasks and containers into meaningful units of work. These are the few containers provided by SSIS: The Sequence container, for grouping tasks and other workflow structures into a unit of work that can be managed as one item. The For Loop container, for grouping tasks and other workflow structures into a unit of work that is repeated by evaluating an expression. The For each Loop container, for grouping tasks and other workflow structures into a unit of work that is repeated by enumerating objects. The Integration Services object model supports a nested hierarchy of containers in a package control flow. Workflow containers can also include other containers, providing support for complex package workflow.

SQL Server Integration Services (SSIS) includes a number of tasks that perform workflow operations, such as executing other packages, running applications, and sending e-mail messages. SQL Server 2005, Integration Services packages can extract data from many additional types of data sources. To access these data sources, Integration Services provides the following new sources and destinations in addition to the SQL Server, OLE DB, and flat file sources and destinations: SQL Server destination, for inserting and updating data into SQL Server databases.

TCS Confidential

Basics on creating SSIS packages

DataReader source and destination, for consuming and providing data to any .NET Framework data provider. XML source, for exacting data from XML documents. Raw File source and destination, for reading and writing raw data to files. Recordset destination, for creating and populating an in-memory ADODB recordset. Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension Processing destinations, for working with analytic objects such as mining models, cubes, and dimensions.

The Integration Services data flow engine supports data flows that have multiple sources, multiple transformations, and multiple destinations. New transformations make it easy for developers to build packages that have complex data flow without writing any code. These transformations include the following: Conditional Split and Multicast transformation, for distributing data rows to multiple downstream data flow components. Union All, Merge, and Merge Join transformations, for combining data rows from multiple upstream data flow components. Sort transformation, for sorting data and identifying duplicate data rows, and the Fuzzy Grouping transformation for identifying similar data rows. Lookup and Fuzzy Lookup transformations, for extending data with values from a lookup table. Term Extraction and Term Lookup transformations, for text mining applications. Copy Column, Data Conversion, and Derived Column transformations, for copying and modifying column values, and the Aggregate transformation for summarizing data.

TCS Confidential

10

Basics on creating SSIS packages

Benefits And Drawbacks Of SSIS Package: While running a package programmatically using SSIS Object Model, the advantage User has is everything runs in process, it is very easy to set variables or modify package before executing it. User can also get events about package progress or ask it to stop by setting CancelEvent. The drawback is, this is local execution User need to install SSIS on same machine where the app runs. This method also can't be used from .NET 1.1 applications, unless it is moved to .NET 2.0. While starting DTEXEC.EXE process. DTEXEC is command line utility for executing SSIS packages. The advantage here is running package out of process gains reliability. It can be used from any programming language (including .NET 1.1).Easy to pass parameters by setting variables values. The drawback here is that it is local only (execution of package). It is difficult to get information about package progress (but SSIS logging gives the most functionality). While using SQL Agent. User can configure an Agent job to run the package either by manually in advance if the package is static, or programmatically using SMO or using SQL stored procedures just before running the package, and then start it programmatically using SMO or sp_start_job. The advantage here is User can get remote package execution. User can get execution serialization (only one instance of a job runs at a time). User can run the package under any account by using Agent proxy. The drawback here is that SQL Agent requires installation of SQL Server engine. User can't pass parameters directly - it requires modification to the job, or some side-channel, like config file or SQL table. While creating a custom application that will run the package, exposing the package as a web services, User call this service from the program. The advantage is that it is easy to add custom logic. User can get remote package execution. It is easy to pass parameters. The drawback here is that there is no need of writing code.

Difference Between Control Flow and Data Flow: The process of creating SSIS package involves collecting together control flow and data flow tasks. Perhaps It is possible to create an SSIS package without any data flow tasks, Most SSIS packages are a combination of the both. At its simplest, user can think of control floe task that generally does something with in the files that contain data, such as copying a file or executing a script. A data flow task does something with the data itself, for example data from a db table and sorting it. In combining the two, user might decide to extract data, make changes to it, and then use a control floe task to copy the result some where else. User want to include data flow tasks in his SSIS package, user must drag a data flow task onto to the control flow pane and select it. When the data flow

TCS Confidential

11

Basics on creating SSIS packages

task is selected, user needs to navigate to the data\ flow pane to configure the data flow for the task. If you have multiple data flow pane will change depending on which data flow task is selected on the control flow pane. Difference between DTS and SSIS Package: Sql Server Integration Services (SSIS) is the replacement of SQL SERVER 200 Data Transformation Services (DTS) package is provided to help organizations with the transition between SQL SERVER 2000 and SQL SERVER 2005. When user install SSIS, the following are also Installed: DTS runtime, package enumeration, and the package Migration Wizard. Whereas DTS (Data Transformation Services) packages are COM-based, SSIS packages are Microsoft .NET Framework complaint and support .NET language. No Data Transformation Services (DTS) packages editor is provided with SSIS, So if user wants to make changes to his DTS package, user will need to migrate it to Sql Server Integration Services (SSIS). Building A Package: Here we have two different approaches to building a package. About first approach, user drags the tasks from the toolbar onto either the control flow or data flow pane and link the tasks together. About Second approach, he follows the SQL SERVER Import And Export wizard to a build a basic package. Using Control Flow or Data Flow: Building a package involves dragging tasks from the toolbar onto either the Control Flow or Data Flow and linking them together. User can link two or more tasks on the Control Flow Pane by selecting the first one and dragging an arrow that appears under to the second task. User can have multiple links from one task to another. After user can right click (RC) the link and specify whether it is activated when the previous task has completed successfully, when the previous task has failed, or when the previous task has executed regardless of the previous tasks success or failure. The nature of the links between tasks visually represented by three different line colors: Green: The linked task will execute only if the prior task Completes successfully. Blue: The linked task will execute if the prior task completes. It does not matter whether the prior task completes successfully or completes with a failure. Red: The linked task will execute if the prior task fails. The entire actions user can find the window.

TCS Confidential

12

Basics on creating SSIS packages

Double clicking an item that you have dragged across to the Control Flow or Data Flow Pane allows user to edit its properties. The properties of each item are unique to that item. For example, the properties if the FTP task involve configuring destination servers and whether files are downloaded or uploaded; the properties of the Send Mail task involve configuring properties of the e-mail that will be sent. Because it isnt feasible to go through each task in the Toolbox and describe how the user configure it without making this section five times longer , user should explore each task himself by dragging the tasks across to the Control Flow or Data Flow Pane then viewing their properties. However the user builds an SSIS package depends on what the user want the packages to do. The best place to start is to break down the package that we want to build into separate tasks and then join those tasks together on the Control Flow Pane. Breaking a problem into small pieces can simplify it greatly. SQL SERVER Import And Export Wizard: The Other way to create basic packages is to use the SQL SERVER Import And Export Wizard. The Wizard always allows the user to create a basic Sql Sever Integration Services (SSIS) packages that copies data to and the below sources:

SQL SERVER Flat Files Microsoft Access Microsoft Excel OLE DB Providers Microsoft .NET Data Provider for SAP Business Suite.

User can use ADO.NET providers as sources for the wizard, but the user can not use them as destinations. User can start the SQL SERVER Import And Export Wizard either from SQL SERVER Management Studio (SSMS), from the command line, or from Business Intelligence Development Studio (BIDS). To start the Wizard from SQL SERVER Management Studio (SSMS), connect to the database server, right click (RC) on a database, choose tasks and then choose either Import data or Export data. It is also possible to start the SQL SERVER Import And Export Wizard from the command line using the DTSWizard.exe command , located in c:\Program Files\Microsoft Sql Server\90\DTS\Binn. User can find this in the Wizard. To start the wizard from BIDS, start a new project and do one of the below tasks: Right Click (RC) on the SSIS packages folder and select Import And Export Wizard.

TCS Confidential

13

Basics on creating SSIS packages

Select SSIS Import And Export Wizard from the Project menu.

The SQL SERVER Import And Export Wizard is designed to create only Packages. To accomplish more advanced tasks, user need to use the full functionality of the SSIS Designer in BIDS. The SQL SERVER Import And Export Wizard does not support common level transformations and providers little in the way pf transformations and provides little in the way of transformation capabilities other than setting names, data types, and its properties in destination files and tables. It is possible to edit existing packages that user created with the SQL SERVER Import And Export Wizard by using the SSIS Designer. Executing Packages: User can use the Execute Package utility and the dtexec command line utility to run packages in development and production environments. User uses two tools for instant rather than scheduled package execution. If user is going to use the Execute Package Utility, user must the Integration Services Service is running. For execute packages using the Execute Package Utility, Perform the below steps: Open SQL SERVER Management Studio (SSMS) on the SQL SERVER 2005 computer on which the packages are installed, and click Connect in Object Explorer to establish a connection to the local integration services. Locate the existing packages or stored packages folder under Integration Services. Depending on how the user has installed the packages, they are located under either the file system or the MSDB node. Right Click (RC) the package that user want to execute and select Run package. This initiates the Execute Package Utility. Click on Execute and the package executes, displaying the package Execution Progress dialog box so that user can view in-progress execution results. If necessary, user can click on stop to stop the execution.

TCS Confidential

14

Basics on creating SSIS packages

CREATING SSIS PACKAGE: In this User create two types of SSIS package that is basic and more advanced SSIS package using the SSIS Designer in BIDS. User create a basic package using SQL SERVER Business Intelligence Studio (BIDS) that uses Notepad to create a text file and then Copies the Created file to a separate directory. 1. Create the directories c:\temp1 and c:\temp2 on the SQL SERVER 2005. 2. Open the SQL SERVER Business Intelligence Development Studio. (BIDS). 3. From the File MenuNewProject (press OK or Enter). 4. Select the Integration Services Project from the list of Visual Studio Installed templates. 5. Enter the Project Name sample-first-project, and ensure that the Create Directory for the Solution Check box is selected. And then click on OK. 6. From the Control Flow Items Pane of the Toolbox, drag the Execute Process task across to the Center of the Design Pane. By Double Clicking the Execute Process Task to open the Execute Process Task Editor dialog box.

7. On the General tab in the Name text box, name task Notepad. Click on the Process tab, and then type notepad.exe in the Execute text box. 8. In the Argument text box, type c:\temp1\example.txt and then click on Ok, by clicking on Ok button the window automatically gets closed, 9. From the Control Flow Items section of the Toolbox, drag the file system Task Across to Design pane to a spot near the existing Execute Process Task. 10. Double-click the File System task to configure it. 11. In the Name field, type Copy Files. In the Destination Connection drop down list, Select <New Connection>. This opens the File Connection Manager Editor Dialog box. 12. In Usage Type Drop-Down list, select Existing Folder. 13. Click on the Browse button, and select the c:\temp2 folder. Click on Ok. By clicking on the Ok Button the window gets closed automatically.

TCS Confidential

15

Basics on creating SSIS packages

14. Click on Ok to close the File Connection Manager Editor dialog box.

15. Ensure that the Operation is set to copy file. Set IsSourcePathVariable to True. In the SourceVariable drop-down list, select <New Variable>. This opens the Add Variable dialog box. 16. Change the Container to File System Task, set the name of the variable to Text-File, and set the value text box to c:\temp1\example.txt. Clicking Ok twice to return to the Design pane.

17. In the Design Pane, select the Execute Process Notepad task. Drag the green arrow that appears under the task appears under the task so that it connects with the File System task. 18. From the Debug menu, choose Start Debugging.

19. Notepad is launched, and user is asked whether user want to create a new file. Click on yes. Type I will Get the Result. Into Notepad and save and close the file. 20. From the Debug menu, select the Stop Debugging. Close BIDS, and verify the existence of the example.txt file in the C:\temp2 Directory. Creating a Data Flow Package: In this User create a more complicated Data Flow package that retrieves data from the Sample Adventure Works database, makes alterations to it, and then writes it to a flat file. 1. Open the SQL SERVER Business Intelligence Development Studio. 2. From the FileNewProject (press OK or Enter). 3. Verify that the Integration Services Project template is selected from the list of Visual Studio Installed Template. 4. Enter the Project Name sample-second-project, ensure that the Create Directory For Solution Check box is selected, and click on OK. 5. In the Solution Explorer pane, Right-Click (RC) package.dtsx and rename it to SAMPEExport.dtsx. Click on Yes to rename the package object.

TCS Confidential

16

Basics on creating SSIS packages

6. Drag Data Flow Task from the Toolbox onto the Control Flow Pane, and then Double click on the Data Flow Task to open the Data Flow Pane. 7. Drag an OLE DB Source onto the Data Flow Pane from the Data Flow sources section of the toolbox. 8. Double Click on the OLE DB Source to open the OLE DB Source Editor Dialog box. 9. Click on New next to OLE DB Connection Manager, and then click New again to open the Connection Manager Dialog box. 10. Set Server Name to the SQL SERVER 2005 computer that user is using. 11. In the Connect To A Database Drop-Down list, select the Adventure Works database. 12. Click on Ok two times to return to the OLE DB Source Editor Dialog box. 13. In the Data Access Mode drop down list, select Table or View, and use the Name Of the Table or The View drop down list to select the [HumanResources].[EmployeepayHistory] table. 14. From the list on the left side of the page, select Columns. Ensure that only The EmployeeID, Rate, and PayFrequency check boxes are selected. Click on OK to close the OLE DB Source Editor Dialog box. 15. Drag the Derived Column task to a spot under the OLE DB Source task from the Data Flow Transformations section of the Toolbox. 16. Select the OLE DB Source, and drag the green arrow that appears under to the Derived Column task. 17. Double Click on the derived column task to open the Derived Column Transformation 18. In the Derived Column Name text box, type TotalSalary. 19. Expand Columns in the top left box, and drag both Rate and PayFrequency into the Expression field. Type an asterisk (*) between [Rate] and [PayFrequency]. Click on OK to close the Derived Column Transformation Editor.

TCS Confidential

17

Basics on creating SSIS packages

20. Drag a Sort task from the Data Flow Transformation section of the Toolbox to a section of the Data Flow under the Derived Column task. Select the Derived Column task. Drag the green arrow that appears onto the Sort task. 21. Double-click on the Sort Task to open Sort Transformation Editor dialog box .Select the TotalSalary Column check box, and change the Sort type to descending. Click Ok to close the Sort Transformation Editor dialog box.

22. Drag a Flat file type destination section of the tool box to a place on the data pane under the Sort task. Select the Sort task, and drag the green arrow that appears onto the Flat File Destination task. 23. Double Click the Flat file destination to open the Flat File Destination Editor dialog box .Click new to open the flat file format dialog box. Verify that the file delimited format is selected for the destination flat file. and click ok. In the file name text box, type c:\temp1\output .csv. Select the column in the fist data row check box. Click OK. From the list on the left side select mappings and click ok to close the Flat File Destination Editor dialog box. 24. From the Debug menu, choose Start Debugging. Ensure that each task completes successfully. Open the file c:\temp1\output.csv using Notepad, and verify that the output is sorted by TotalSalry from highest to lowest. 25. From the Debug menu, choose stop Debugging. Save the project and close Business Intelligence Development Studio. Summary: A package is an organized collection of tasks and workflow elements. The order of task execution is dependent on the outcome of earlier steps in the workflow. Packages can be constructed visually using BIDS. Packages can be saved to a SQL Servers msdb database or to an XML structured .DTSX file. The process of creating SSIS package , drag a Data Flow Task onto the Control Flow, select it and then navigate to the Data Flow Pane to configure the task. Double- click on item that user have dragged across to the Control Flow or Data Flow Pane allows you to edit its properties. The Execute Package Utility and the dtexec command line utility can be used to run packages in development production environments.

TCS Confidential

18

Basics on creating SSIS packages

Conclusion: SQL Server Integration Services (SSIS) introduces new features and enhancements that increase the power and productivity of developers. It has Graphical tools such as SSIS Designer and the SQL Server Import and Export Wizard. The ability to create packages programmatically and to extend the Integration Services object model by using custom tasks, sources, destinations, and transformations. SQL Server Integration Services (SSIS), solves many of the difficulties and limitations of DTS. SSIS architecture separates data flow from control flow by introducing two different engines, the Integration Services run-time engine and the Integration Services data flow engine. This separation provides better control of package execution, increases the execution of data transformations, and enhances the extensibility of SSIS by simplifying the creation and implementation of custom tasks and transformations. DataReader source and destination, for consuming and providing data to any .NET Framework data provider. They are XML source, for exacting data from XML documents.

TCS Confidential

19