Jun 12, 2013

Simple demo project in SSIS

Getting started with a very easy example project of SSIS for beginner

We are going to create a simple package in SSIS (Sql server integration services) which will import the data from text file into the sql server.

Step 1: First of all we will create a text file which will keep the information of student. First line of text file keeps the columns names delimited by comma and second line on word it will keep the student data delimited by comma.
Suppose name of the text file is student.txt which is at C:\SSIS\


Step 2: Create a new Integration services project.

Step 3: Click on the Control Flow tab and from toolbox drag and drop the Data Flow Task

Step 4: Double click on Data Flow Task. We will come to the Data Flow tab.

Step 5: In the Data flow tab drag and drop the Flat File Source from toolbox



Step 6: Double click on Flat File Source. In connection manager tab click on New button. We will get the new window. In the general tab:

Connection manager name: We can write any name to identify this connection

Description: We can write any description to describe this connection.

File name: By clicking browse button we will write source data file name from where we have to import the data. In this example our source file name is: C:\SSIS\Student.txt

Format: Select format as Delimited. Since our data in the student.txt is delimited by comma.

Header row delimiter: Since in Student.txt first row is header row which is delimited by comma. So select Comma{,}

Column names in first data row: Select the checkbox since first rows of student.txt keeps the column name.



Step 7: Now click on Columns Tab. We will get the following screen:



Now click on OK button. Click on Ok button in Flat file source editor window.

Step 8: Drag drop the ADO NET Destination from toolbox and connect the Flat File Source to ADO NET Destination by green arrow.


Step 9: Double click on ADO NET Destination. To choose the Connection Manager click on New button.


Step 10: We will get the Configure ADO.NET connection manager. Click on New button there.  

Server name: Name of the database server. In this case our database server is at local machine. So we will write single dot there.

Choose the authentication type.

Select or enter database name: Select any database name where we want to import the data from Student.txt
In this case database name is SSIS


Click on OK button. Again click on OK button in the Configure ADO.NET connection manager window.

Step 11: In the ADO.NET Destination Editor Click on the lower new button to create a new table.


In Create Table window we can edit the table name data type etc. In this case replace it by:
CREATE TABLE [dbo].[Student](
    [Roll_No] [int] NOT NULL,
    [Name] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [Age] [int] NULL
)

Click on OK button. Select the Student in the Use a table or view combo box. 

Step 12: Select the Mapping Tab. We will get following screen:


Click on Ok button.

Step 13: Now we have created the package. To run it press F5 button from keyboard. If everything is fine we will get following screen:




Now stop the debugging.

To check the data into student table open the sql server and write the query:

2 comments:

  1. Anonymous9/12/2013

    Thank a lot......Please keep sharing your important knowledge with us....

    ReplyDelete
  2. Good work my fiend, verry good explain method,keep continue

    ReplyDelete