In SSIS Package are a combination of Connection Managers, Control Flow Tasks, Data Flow Elements, Parameters, and Event Handlers that you use to perform ETL operation.
SSIS Package is all about extracting data from different sources transform it and then load it in a completely different destination.
Create SSIS Package
When you create a New Project, BIDS will automatically create a new package for you. However, you have an option to create a new package in SSIS.
To do so, right-click on the SSIS Packages folder, and select New SSIS package option from the context menu
This will create a new package as shown in below screenshot
SSIS Package Tutorial
If you observe the below screenshot, each package has six different tabs.
Connection Managers
We need connection manager to establish the connection between the SSIS package and source, and package and destination. I suggest you to refer Introduction to Connection Managers article to understand them.
Control Flow Tasks in SSIS Package
A package should contain at least one control flow element. Control Flow is a combination of Containers, Tasks, and Precedence Constraints.
Containers
For Loop and Foreach Loop containers works similar to For Loop and Foreach loop in any programming language. Sequence container helps to group multiple tasks.
Tasks
Control Flow Tasks are used to work with local files, FTP files, Web Services, Scripts etc. You can use these tasks to perform Database administrative tasks as well.
Precedence Constraints
Use Precedence Constraints to control the tasks flow. For example, you can write qualified expressions that have to meet before executing any task.
SSIS Package Data Flow Elements
Data Flow tab is a combination of Sources, Transformations, and Destinations. By default, you cannot work in the Data Flow tab. Therefore, before you start working on the Data Flow tab, you have to add data Flow Task in the Control Flow tab.
From the below screenshot, you can observe that there are three sections and a Favorite section:
Favourite section
If you do not know the basic steps involved in connecting with Source or Destination then you can use Source Assistance and Destination Assistance. These two provide a nice and easy Wizard. By using this wizard, you can establish a connection with Source and Destination.
Sources
A source is a place from where you want to extract data. SSIS package supports a wide variety of sources such as Excel Files, Flat Files, XML Files, Relational databases
Transformations
List of transformations that are available under this section is used to transform (slice and dice) data as per your requirement. For example, you can Pivot Data, Merge 2 tables, Sort tables etc within your package
SSIS Destinations
A destination is a place where we load the final data (after transformation). SSIS package supports a wide variety of destinations such as Relational databases, OLE DB, ODBC destinations, Excel Destination, Flat Files Destination, XML Destination, etc.
Parameters in SSIS Package
Parameters help you to modify the package without modifying the entire package and replotting it. There are two types of parameters in SSIS and they are Project Parameters, and Package Parameters. I suggest you to refer SSIS Parameters article to understand the process of creation of parameters.
Event Handlers
This tab is useful to handle the raised events in the run-time. For example, if you want the package to send an email to a manager after the package executed then you can create a script task for the onPostExecute event. I suggest you refer Event Handlers in SSIS article to understand the process of configuring events.
SSIS Package Explorer
This tab will display all the elements in a package that includes: Connection Managers, Tasks, Parameters, Event Handlers, Variables etc.
