Click here to Skip to main content
15,867,568 members
Articles / SSIS

Steps to Create your First SSIS Package

Rate me:
Please Sign up or sign in to vote.
4.69/5 (42 votes)
30 Jun 2011CPOL6 min read 392.3K   7.4K   66   41
The purpose of this tutorial is to provide detailed steps on how to create a SSIS Package.

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.

image1

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

Image 2

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

Image 3

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

    Image 4

  2. Select Column tab and check whether all columns are properly mapped or not.

    Image 5

  3. Select advance tab. Here you can add, remove or modify columns as per output stream requirement.

    Image 6

  4. Select preview tab to check how your output will look like:

    Image 7

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.

Image 8

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.

Image 9

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

Image 10

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.

    Image 11

  2. Columns -This tab allows the user to select required output columns and user can also change the output column names.

    Image 12

  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.

Image 13

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.

Image 14

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

Image 15

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.

Image 16

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

Image 17

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

Image 18

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.

Image 19

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

Image 20

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

Image 21

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

Image 22

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.

Image 23

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.

Image 24

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

Image 25

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.

Image 26

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.

Image 27

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.

Image 28

Conclusion

I hope this might be helpful to all beginners.

History

  • 30th June, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Praisethanks Pin
Mohit_Gupta4-Dec-17 19:22
Mohit_Gupta4-Dec-17 19:22 
QuestionThanks Pin
Baris Yilmaz5-Oct-17 1:22
Baris Yilmaz5-Oct-17 1:22 
PraiseGreat Article. Pin
Member 131168079-Apr-17 7:46
Member 131168079-Apr-17 7:46 
GeneralFeedback Pin
Member 1199913120-Sep-15 20:41
Member 1199913120-Sep-15 20:41 
GeneralA Great Little Tutorial Pin
Eukaryote5-May-15 3:10
Eukaryote5-May-15 3:10 
Questionsolution if there is a excel connection manager error Pin
Member 110583865-Sep-14 0:14
Member 110583865-Sep-14 0:14 
GeneralGOOD Pin
Member 109976077-Aug-14 21:10
Member 109976077-Aug-14 21:10 
GeneralMy vote of 5 Pin
Moumit Mondal9-Jun-14 3:51
Moumit Mondal9-Jun-14 3:51 
GeneralGreat tutorial, found a couple of errors, but works ! Pin
Efrain Plaza21-Apr-14 10:38
Efrain Plaza21-Apr-14 10:38 
GeneralRe: Great tutorial, found a couple of errors, but works ! Pin
Member 1438972719-May-19 23:54
Member 1438972719-May-19 23:54 
QuestionGood Pin
sathish52527-Apr-14 9:43
sathish52527-Apr-14 9:43 
GeneralGood Artical Pin
Kumar Hi3-Feb-14 7:54
Kumar Hi3-Feb-14 7:54 
GeneralNice Explanation Pin
Raju Katare12-Jan-14 18:50
Raju Katare12-Jan-14 18:50 
Thank you. Smile | :)
Desire creates the power.

GeneralSuper explnation about SSIS for beginners Pin
dvnetnl29-Aug-13 20:12
dvnetnl29-Aug-13 20:12 
QuestionThanks you so much Pin
Aruna Gaddam31-Jul-13 20:39
Aruna Gaddam31-Jul-13 20:39 
GeneralNice post Pin
jakhotiasandeep22-Jul-13 3:44
jakhotiasandeep22-Jul-13 3:44 
GeneralMy vote of 3 Pin
Rajanand Ilangovan15-Jul-13 9:16
Rajanand Ilangovan15-Jul-13 9:16 
GeneralRe: My vote of 3 Pin
GregoryW12-Sep-13 20:31
GregoryW12-Sep-13 20:31 
QuestionFound few Error while running Sql Server 2008 Pin
AmitVilasThakur26-Jun-13 23:43
AmitVilasThakur26-Jun-13 23:43 
QuestionThank you Pin
UsmanHameed25-Jun-13 0:39
UsmanHameed25-Jun-13 0:39 
Questionsome suggestions Pin
Gitase24-Jun-13 18:42
Gitase24-Jun-13 18:42 
QuestionThanks Pin
yugo gautomo7-Jun-13 0:09
yugo gautomo7-Jun-13 0:09 
GeneralGreat acticle Pin
MarcR.15-May-13 7:50
professionalMarcR.15-May-13 7:50 
QuestionSteps to Create your First SSIS Package Pin
sambaba14-May-13 20:37
sambaba14-May-13 20:37 
QuestionThanku for article Pin
sagar000010-May-13 1:05
sagar000010-May-13 1:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.