Using import and Export Data tool how to send data from database to flat file

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4722 red flag
Rating: 5 out of 5  
 1 vote(s)

In SSIS we using the import and Export Data we can send data from data base to flat file and same way flat file to database.

Below are few steps where can achieve 

Data from Database to flat file:

Click on next button if you don’t want see every time while opening this import and export. click on "Do not show this starting page again"

  1. Data source: - Select the source data from where we want to get the data like sql server or flat file,
    Microsoft Excel.
  2. Based on the data source selection 2nd option will be changed EX: if we select the SQL Server Native Client
    then it will ask for server name. If we selected Flat file source we need to give file name with path.
  3. Server Authentication:- select which mode of server Authentication is required either windows or SQL Server.
  4. DataBase:- Select the database name.
Click on next button

Select the destination:

  1. Now its time to select to where we need the data so select the destination.
  2. We are getting the data from sql sever table to flat file .. so select destination as flat file ….
  3. Select the file name where we need the need the data …
  4. Locale :is where we need to select the format type ..
  5. Code page: type is for what type of format u need …
  6. Format: we will have 3 types EX: abc| acd . How u want u r data need to beeeee ..
  7. Click on next button for continue.
Specify table copy or query:

  1. Copy data from one or more tables or views : this we want if we need to get complete table data in the out
    file ..
  2. Write a query to specify the data to transfer: We need data from tables based on some conditions like
    Ex : select * from table where column_name = ‘XXXXXXX ’.
Configure flat file destination:

  1. Configure flat file destination : its time to map how we need output in flat file .. 
  2. Source table or view : As we know that we need the database from the database server now its time to select the table or view. 
  3. Row delimiter : Click on the drop down box and select the how u need to delimit the row .. 
  4. Column delimiter :  Click on the drop down box and select the how u need to delimit the column  
  5. Preview : Click on the preview button and see how we will get data into flat file  
  6. Edit mapping : Click on the Edit Mapping button from where we can edit mapping like append rows to destination file etc.
Save and Run Package:

  1. Run immediately : If we check Run immediately and click on next button we will get the data from sql table to
    flat file which we was selected ..
  2. Save SSIS Package : if we checked on Save SSIS Package where we can execute when every we required ..
  3. Click on Next Button to continue.

Complete the wizard:

  1. Server location and source provider
  2. We can see from where to where data copied ( Copy rows from [dbo].[Student] to D:\SSIS_Demo\test.txt )
  3. Provider mapping where we can see patch for mapping file ..
  4. Click on finish Button to complete this task.

  1. Total 9 rows are transferred.
  2. Click on close button.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Pardha Saradhi
Member Level:
Member Status: Member
Member Since: 3/19/2013 2:21:22 PM
Country: India

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)