Click here to Skip to main content
Click here to Skip to main content

Steps to Create your First SSIS Package

By , 30 Jun 2011
Rate this:
Please Sign up or sign in to vote.

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

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

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)

About the Author

No Biography provided

Comments and Discussions

 
GeneralGreat tutorial, found a couple of errors, but works ! PinmemberEfrain Plaza21-Apr-14 10:38 
QuestionGood Pinmembersathish52527-Apr-14 9:43 
GeneralGood Artical PinmemberKumar Hi3-Feb-14 7:54 
GeneralNice Explanation PinmemberKatareRaju12-Jan-14 18:50 
GeneralSuper explnation about SSIS for beginners Pinmemberdvnetnl29-Aug-13 20:12 
QuestionThanks you so much PinmemberAruna Gaddam31-Jul-13 20:39 
GeneralNice post Pinmemberjakhotiasandeep22-Jul-13 3:44 
GeneralMy vote of 3 PinmemberRajanand Ilangovan15-Jul-13 9:16 
You could have explained it a little bit briefly..
GeneralRe: My vote of 3 PinmemberGregoryW12-Sep-13 20:31 
QuestionFound few Error while running Sql Server 2008 Pinmemberdsfgdsfgdsfg26-Jun-13 23:43 
QuestionThank you PinmemberUsmanHameed25-Jun-13 0:39 
Questionsome suggestions PinmemberGitase24-Jun-13 18:42 
QuestionThanks Pinmemberyugo gautomo7-Jun-13 0:09 
GeneralGreat acticle PinmemberMarcR.15-May-13 7:50 
QuestionSteps to Create your First SSIS Package Pinmembersambaba14-May-13 20:37 
QuestionThanku for article Pinmembersagar000010-May-13 1:05 
GeneralVery Well Explained PinmemberNEOPOTTER4-Apr-13 9:00 
GeneralMy vote of 3 PinmemberJaswinder Saini3-Apr-13 2:10 
QuestionVery Nice PinmemberGaurav Yadav051-Mar-13 1:43 
AnswerRe: Very Nice PinmemberMember 878888619-Mar-13 1:33 
GeneralMy vote of 4 PinmemberSouvick Mukherjee6-Jan-13 23:43 
QuestionThanks Deepti PinmemberMember 969156716-Dec-12 23:47 
GeneralMy vote of 5 Pinmembersunkumar.sunil4-Dec-12 1:12 
GeneralMy vote of 5 PinmemberMember 924870312-Oct-12 0:39 
GeneralMy vote of 4 Pinmemberrevathybala19-Aug-12 22:54 
QuestionError at Data Flow Task Pinmemberhhill8-Aug-12 2:14 
GeneralRe: Error at Data Flow Task Pinmemberhhill9-Aug-12 0:26 
GeneralRe: Error at Data Flow Task PinmemberMember 98841054-Mar-13 21:01 
AnswerRe: Error at Data Flow Task PinmemberGitase24-Jun-13 18:56 
GeneralRe: Error at Data Flow Task Pinmemberpravishrams15-Jul-13 18:05 
GeneralMy vote of 5 PinmemberVivek.Net1-Jul-11 1:28 
GeneralMy vote of 5 Pinmembercanozurdo30-Jun-11 10:50 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140421.2 | Last Updated 30 Jun 2011
Article Copyright 2011 by Deepti Saxena
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid