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"
- Data source: - Select the source data from where we want to get the data like sql server or flat file,
- 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.
- Server Authentication:- select which mode of server Authentication is required either windows or SQL Server.
- DataBase:- Select the database name.
Click on next button
Select the destination:
- Now its time to select to where we need the data so select the destination.
- We are getting the data from sql sever table to flat file .. so select destination as flat file ….
- Select the file name where we need the need the data …
- Locale :is where we need to select the format type ..
- Code page: type is for what type of format u need …
- Format: we will have 3 types EX: abc| acd . How u want u r data need to beeeee ..
- Click on next button for continue.
Specify table copy or query:
- Copy data from one or more tables or views : this we want if we need to get complete table data in the out
- 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:
- Configure flat file destination : its time to map how we need output in flat file ..
- 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.
- Row delimiter : Click on the drop down box and select the how u need to delimit the row ..
- Column delimiter : Click on the drop down box and select the how u need to delimit the column
- Preview : Click on the preview button and see how we will get data into flat file
- 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:
- 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 ..
- Save SSIS Package : if we checked on Save SSIS Package where we can execute when every we required ..
- Click on Next Button to continue.
Complete the wizard:
- Server location and source provider
- We can see from where to where data copied ( Copy rows from [dbo].[Student] to D:\SSIS_Demo\test.txt )
- Provider mapping where we can see patch for mapping file ..
- Click on finish Button to complete this task.
- Total 9 rows are transferred.
- Click on close button.