Create Simple ETL Project in SSIS – Filter Records

In Previous article, we have seen the basics of SSIS. In this article, we will create our first basic application of filtering data from csv file.

As stated, Business intelligence tool is installed with SQL Server 2005 and best part is that it is integrated with Visual Studio 2005. So developer get the benefit of SSIS feature in Visual Studio power to work in disconnected environment.

In this article, we will read the csv file and get the required records and write back into another csv file.  csv file is attached in the source code in this article.

To start the project, click new Project in Visual Studio and select “Business Intelligence Projects” and then “Integration Services Project” as shown in below figure.

After creating the BI (Business Intelligent) project, “package.dtsx” is created by default. “Package” is the term used in BI tool of SQL Server to represent the DTS project. one package can run many other packages as we have done in our project. the extension of the package is “dtsx“.

Every package have four Tabs as shown in below image:

1. Control Flow : Build and modify the control flow in Package.
2. Data Flow : Build and modify the data flow for the selected “Data Flow Task”.
3. Event Handlers : Build and modify the control flow foe the selected “Event Handler”.
4. Package Explorer : Displays the content of package in tree view.

In this project we will create only Data Flow Task.

Select tab “Control Flow” in package and from toolbox, select “Data Flow Task“.

Double click on Data Flow Task, Data Flow tab will open.

Now Drag the “Flat File Source” onto the “Data Flow” Panel.

Double click on “Flat File Source” and in “Connection Manager” option, select on “New” flat file connection Manager as shown in below image.

Select the source file from which we have to extract the record and click on the OK button. Now we have made the source connection successfully. You may also have noticed that one connection is created in connection manager panel located at the bottom of the Visual studio environment. Select the checkbox saying that “Column names in the first data row” if the first row is column name in csv file.

Now we have the source file, to extract the required records, select “Conditional Split” from the toolbox.

Select “Flat File Source” and drag the Green arrow to the “Conditional Split” tool. Green arrow represents the next step to be executed after successful execution of current block and Red arrow represents the next step to be executed after operation fail or error occurs (Can be used as exception handling).

Now, Double click on “Conditional Split”, “Transformation editor” will open. Select Column from columns list when cursor is in Condition textbox  and write College == “UCOE”. This means get only those rows in which the college columns value is “UCOE”.

Now select the “Flat File Destination” from the toolbox and drag the green arrow from “Condition Split” to this control. When you will drag the arrow from “Conditional Split” to “Flat File Destination” one popup will open to ask for output and input as shown in below image:

As shown in image, we have selected the Output named “UCOEStudents” which we had specified in above step and input is provided to the “Flat File Destination”

Now double click on “Flat File Destination” and in “File Connection Manager”, click on New.

It will ask for the flat file format, select delimited and provide the path where file should be saved. In current project, i have provided the file name as “Output”.

We can also add the “Data Viewer” after Conditional split, (Right click on Green arrow and select the Data Viewers) which is useful while debugging the application.

Press F5 or click on run, one new file will be created which contains all the records in which college name is “UCOE”. In case you have provided any “Data Viewer”, then popup will appear displaying the results.

Download source code from here