This article demonstrate how to create a new SSIS project using SSDT (SQL Server Data Tool).
SSIS projects are created to transform data from a wide variety of sources such as flat files, and relational data sources, and then load the data into one or more destinations.
It includes various built-in tasks and transformations, graphical tools for building packages.
Lets see the step by step implementation of new SSIS project.
First go and open SQL Server Data Tool (SSDT) as shown below.
Now click on File tab in menu bar then select Project.. to create a new SSIS project.
Once you click on Project.., a New Project window opens.
Select the Integration Services Project under the Business Intelligence, after that provide a the project name named as ssis_project, and location then click on OK button.
Once you click on OK button, you will see a new SSIS project is created.
Lets understand the user interface of newly created SSIS project, you can see different – different pane/window as follows:
1. Solution Explorer: The Solution Explorer pane is where you can find all your created SSIS packages/create new new SSIS packages, project connection managers, project parameters, and any other miscellaneous files needed for the project.
2. SSIS ToolBox: It holds the different elements/component such as tasks, transforms, containers, sources and destinations that is used to construct an SSIS package workflow.
3. Control Flow: It allows you to build and modify the control follow in package, and helps you to arranges the order of execution for all its components.
SSIS provides three different types of control flow: containers that provide structures in packages, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an ordered control flow.
4. Data Flow: It allows you to build and modify the data flow for the selected Data Flow Task, SSIS provides three different types of data flow components: sources, transformations, and destinations.
Sources extract the data from relational database, excel, flat file etc and. Transformations modify, summarize, and clean the data. Destinations load data into data stores, relational database, excel, or create in-memory datasets.
5. Parameters: Parameters view is used to add, modify or remove packages parameters.
SSIS parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level.
6. Event Handlers: Event handlers view is used to build and modify the control flow for the selected event handler.
7. Package Explorer: Package Explorer view display the content of package in tree view which reflects the container hierarchy of the Integration Services architecture. The package container is at the top of the hierarchy, and you can expand the package to view the connections, executables, event handlers, log providers, precedence constraints, and variables in the package.
Load Flat file data into SQL Server Table
Load Excel File data into SQL Server Table
3,530 total views, 1 views today