IntroductionThe purpose of this tutorial is to provide detailed steps on how to create a SSIS Package. The package given below reads the data from a source tab delimited text file and based on certain conditions, saves the output in two separate Excel Output Files. The output Excel files also have the same header as the input text file. The structure and data for the source input file is given below:
Steps to Create Your First SSIS Package1. Open business intelligence development studio.
2. Click on File-> New -> Project.
3. Select Integration service project in new project window and give the appropriate name and location for project. And click ok.
- Tool Box on left side bar
- Solution Explorer on upper right bar
- Property Window on lower right bar
- Control flow, data flow, event Handlers, Package Explorer in tab windows
- Connection Manager Window in the bottom
- In General Tab, enter connection manager name and description
(optional). Select source file, file format and delimiter. If first row
of source file contains headers, then select the checkbox “Column names
in the first data row".
- Select Column tab and check whether all columns are properly mapped or not.
- Select advance tab. Here you can add, remove or modify columns as per output stream requirement.
- Select preview tab to check how your output will look like:
8. Now Drag Data Flow Task from the Toolbox into the Control Flow Container.
- Data flow sources - Source makes data from different external data sources available to the other components in the data flow.
- Data flow transformations - Transformations can perform tasks such as updating, summarizing, cleaning, merging, and distributing data.
- Data flow destinations - Destination writes the data from a data flow to a specific data store, or creates an in-memory dataset.
- Connection Manager - Here we will specify source connection manager which we created for source file. If source file contains
nullvalues, select “Retain null values from Source as null values in the data flow” checkbox.
- Columns -This tab allows the user to select required output columns and user can also change the output column names.
- Error Output - Using this tab, the user can decide the behavior of the component in case of failure. There are three options:
- Ignore Failure: Selecting this will ignore any failure while reading rows from source and the package will continue executing even any error occurred.
- Redirect Row: Selecting this will redirect the failed rows to other component which is connected with the error precedence constraints.
- Fail component: Selecting this will stop the execution of package in case of failure.
HigherSalary: [Salary] > 20000 (Redirect records if salary is greater than 20000)
LowerSalary: For rest of the records
HigherSalary” conditional output and click ok.
Records with Salary > 20000”
GREEN”, it means package has run successfully, if there is any error, the component which has failed to execute will be shown in “
RED” Color. We can see the package execution steps in the “Progress” tab.