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.

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:

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.

Data flow sources - Source makes data from different external data sources available to the other components in the data flow.

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.

Great step by step tutorial !It is missing a conversion as commented in previous entry (see below) for salary, so it needs to have a conversion before you try to set salary as < 20000, because salary is not a number when imported.I also had an error trying to use the same files from the download, remember to save them with a different name (they are downloaded as read only and SSIS will fail) then reasign in the Excel connection Manager.It is also important to highlight that, in case you get an error, you need to stop the debuger by going to debug/stop debuging, then you can fix the package again.

It was wonderfull thank you.some suggestions: 1- Changing [Salary]>20000 To (DT_I4)[Salary]>20000 in ConditionalSplit2- Check Salary in Data Conversion3- To execute the package in 64bit machine go to the Debugging page and change Run64BitRunTime to false.