Getting started with SSIS - My First SSIS Project

Now that we have opened the 'SQL Server Business Intelligence Development Studio' we can go ahead to create our first Integration Services project.
Steps:-

1.)  Open New Project dailog box:-
File -> New -> Project

2.) Select Project template:-

As Shown above:
2.a) From 'Project types' select 'Business Intelligence projects'.

2.b) From 'Templates' select 'Integration Services Project'.

2.c) In 'Name' textbox enter the project name, in this case 'My_First_SSIS_Project'.

2.d) In 'Location' textbox, specify the location where you want to save your project. You can select the location by clicking the 'Browse' button as well.

2.e) By default, value entered in Name is assigned to 'Solution Name', but if you want you can give it a different name too.

2.f) Click on 'OK' button.

Now that you are done with all the above mentioned steps, you can see your first SSIS porject been created.

3.) Creating SSIS Package:-

In this step, we will create a SSIS package which will read data from Database table and write it to a text file.

Steps:-

3.a) Get the Data Flow Task on Control Flow:-

As shown above, Data Flow Task(DFT) is available in the left pane of the developer window called 'Control Flow Items'. You can either double-click or drag-drop on the "Control Flow".

3.b) Edit the 'Data Flow task':-

To edit the 'Data Flow Task', either double-click on it or right-click and select 'Edit'.

Above screenshot shows the develpoer window, after you have opened your DFT for editing.

3.c) Get the Source:-

As shown in the above screenshot, drag 'OLE DB Source' from 'Data Flow Sources' present in the 'Toolbox' pane present on the Left-Side of the developer.

3.d) Set the Source:-

Double-click or right-click and select 'Edit', to open OLE DB source for editing.

Click on 'New...' button, it will open 'Configure OLE DB Connection Manager':-

Again, click on 'New...' button, it will open 'Connection Manager'

In 'Connection Manager Editor' enter Server Name, click on 'Use SQL Server Authentication' and enter User name and Password (If you are using Windows Authentication to Logon to database then click on “Use Windows Authentication”). Check 'Save my password' check box. From 'Select or enter a database name' dropdown select the database name. Now click on 'Test Connection', if all the above mentioned credentials are entered correctly then will show the below prompt:

If any of the credential is not mentioned correctly then it will give the appropriate Error prompt. Enter the correct credentials so that you can get the above shown Prompt. Once done with it, click on OK and return back to the 'OLE DB Source Editor'. As shown in the below screen shot select the table name:

In this case we have selected test1 table. To preview the data present in that table click on Preview button. It will display the table data in Preview Query Results window:-

Now click on Columns present in the Left pane of OLE DB Source Editor:-

From 'Available External Columns' you can check all those columns which you want. In this case we have selected all the columns present in the table. Click on OK.

Now the Developer window will be as shown in the below screen shot:

3.e) Get the Destination:-
First create a text file on your hard drive; in this case we have created a text file named “My_First_Project_Destination.txt”. Open the text file and enter all the column names that you selected from the source table (in this case column names will be Id and Value) column names will be comma separated .From Data Flow Destination present in the Toolbox pane drag 'Flat File Destination' on to Data Flow. As shown below:

Select “OLE DB Source”. Two links “Green and Red” will be shown drag the Green link and drop it on Flat File Destination

3.f) Set the Destination:-
Double click the Flat File Destination to open it for editing. It will open 'Flat File Destination Editor':

Click on 'New', it will open 'Flat File Format':
Select 'Delimited' and click on OK. It will take you to 'Flat File Connection Manager Editor':
Click on 'Browse' and select the destination text file which you have created. From Header row delimiter drop down select Comma. Check 'Column names in the first data row' check box. Click on Columns you will be able to see the column names in the Preview pane:
Click on OK.
In Flat File Destination Editor click on Mappings:
It will display Available Input columns and Available Destination Columns, map the input columns to its corresponding output columns. Click on OK.

4.) All set to Execute Package:
As shown in the above screen shot in Data Flow you will see the Source and destination been connected. In Connection Managers you will see two connections been created Flat File Connection Manager for the destination flat file and the other one for the source table. To execute the package in Solution Explorer right click the package and select Execute Package. After the execution is completed you will see the number of rows been read from Source table and written to the destination flat file.
In this case 5 rows are read and written to destination. Click on 'Stop Debugging'.

5.) View Target file:
Go to the Hard Drive location where you have placed the target Flat file and open it. You will be able to see all the records that are been written to the Target Flat file:
Congratssssss!!!! You have successfully created and executed your first SSIS package.