SSIS Basics & Step by Step Explanation of Create your First SSIS Package on BIDS

Introduction

The 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:

EmpID	EmpName	DOB	DOJ	Salary
1	User1	1/1/1976	1/4/2000	20000
2	User2	1/2/1976	1/5/2000	20000
3	User3	1/3/1976	1/6/2000	20000
4	User4	1/4/1976	1/7/2000	30000
5	User5	1/5/1976	1/8/2000	20000
6	User6	1/6/1976	1/9/2000	40000
7	User7	1/7/1976	1/10/2000	20000
8	User8	1/8/1976	1/11/2000	35000
9	User9	1/9/1976	1/12/2000	20000
10	User10	1/10/1976	1/6/2000	20000

Steps to Create Your First SSIS Package

1. 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.

4. The new project screen contains the following:

1. Tool Box on left side bar
2. Solution Explorer on upper right bar
3. Property Window on lower right bar
4. Control flow, data flow, event Handlers, Package Explorer in tab windows
5. Connection Manager Window in the bottom

5. Right click on the Connection Manager Tab, click on new FLAT File Connection Menu Item.

6. Connection manager editor opens up which contains 4 tabs, General, Columns, Advanced and Preview.

1. 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".
   
2. Select Column tab and check whether all columns are properly mapped or not.
   
3. Select advance tab. Here you can add, remove or modify columns as per output stream requirement.
   
4. Select preview tab to check how your output will look like:
   

7. Click on OK. It will create a flat file connection manager for your source file.
8. Now Drag Data Flow Task from the Toolbox into the Control Flow Container.

9. Double Click on the Data Flow Task. It will show Data flow Container tab for selected Data Flow Task. You can see three item categories in Toolbox.

1. Data flow sources - Source makes data from different external data sources available to the other components in the data flow.
2. Data flow transformations - Transformations can perform tasks such as updating, summarizing, cleaning, merging, and distributing data.
3. Data flow destinations - Destination writes the data from a data flow to a specific data store, or creates an in-memory dataset.

10. Drag a Flat file Source Component from Data Flow Sources into Data Flow Container window.

11. Double Click on the Flat File Source Component, it will display flat File source Editor. The window contain three tabs:

1. Connection Manager - Here we will specify source connection manager which we created for source file. If source file contains null values, select “Retain null values from Source as null values in the data flow” checkbox.
   
2. Columns -This tab allows the user to select required output columns and user can also change the output column names.
   
3. Error Output - Using this tab, the user can decide the behavior of the component in case of failure. There are three options:
   1. Ignore Failure: Selecting this will ignore any failure while reading rows from source and the package will continue executing even any error occurred.
   2. Redirect Row: Selecting this will redirect the failed rows to other component which is connected with the error precedence constraints.
   3. Fail component: Selecting this will stop the execution of package in case of failure.

12. Drag and drop a Conditional Split Component from Data Flow Transformations Tab into Data flow Task Container window. Drag and Connect the success output (which is shown by Green arrow) of Flat File Source Component to Conditional Split Component.

13. Double click on the Conditional Split component, it will open Conditional Spilt Component editor window. Here user can specify the condition(s) as per the requirement and click ok. For example:

• HigherSalary: [Salary] > 20000 (Redirect records if salary is greater than 20000)
• LowerSalary: For rest of the records

14. Drag and drop Excel Destination component from Data Destinations tab into Data Flow Task Container. Connect the success arrow of the Conditional Split Component to Excel Destination, Input Output selection window will be popped up.

15. In the Input Output Selection popup window, select appropriate conditional output for example “HigherSalary” conditional output and click ok.

16. Double Click on the Excel Destination Component which will open Excel Destination Editor Window. Click on the new Ole DB Connection Manager Button.

17. Select Destination File location and the appropriate Excel Sheet Name where you want to insert the success output data with salary values higher than 20000.

18. Click on the mapping tab and map the appropriate input columns with output columns.

19. On click of OK, error icon is shown in the destination excel file component and it displays the message “Cannot Convert between Unicode and Non-Unicode string data types”.

20. To resolve this issue, we need to insert a Data Conversion Transformation Component between Conditional Split and Excel Destination Component.

21. Double Click on the Data Conversion Component, it will open Data Conversion Transformation editor. Using this component, convert input data types to required output data types.

22. Click OK and connect the success arrow of Data Conversion component into Excel Destination Component. Double click on Excel Data Conversion Component and click on the mapping tab and map the output of Data Conversion Component to input of Excel Destination Component and click ok.

23. Rename the Excel Destination Component as “Records with Salary > 20000”

24. Now add one more Data Conversion Transformation Component and connect the second success output of Conditional Split to it. Do the necessary data type conversions. Add one more Excel Destination Component and rename it as “Remaining Records”. Create a new connection manager and configure it to point to the second Output File. Connect the input of the newly added Data Conversion Component to it and do the mapping as required.

25. Now the package is ready to be executed. Go to the Solution Explorer and right click on the package and select “Execute Package". If all components turn “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.

26. Once you run the package, data will be saved in the destination output files as per the condition specified in the Conditional Split Component.

Conclusion