Using the Import and Export Wizard
With SQL Server 2008, you use the Import and Export Wizard to copy data without going through the process of creating an SSIS Project. Use the following considerations to determine which part of the wizard to use:
• Importing data with the wizard lets you bring data from various sources like flat files, data in Microsoft Office Excel spreadsheets or Microsoft Office Access databases or data in Oracle databases into a SQL Server tables.
• Exporting data with the wizard lets you send data from SQL Server tables, views or custom queries to flat files or another database.
To import data from Excel and stored into Sql server by using Import and Export Wizard in SQL Server 2008, we have to do the following steps.
Steps:
1. Start --> Programs --> Microsoft SQL Server 2008R2 --> SQL Server Management Studio (SSMS)
2. Open SQL Server Management Studio
3. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)
4. Click Connect to connect to the server from SSMS
5. Right-click on the name of the database instance you wish to use and select “Import Data” from the Tasks menu
6. Click Next to advance past the wizard’s opening screen
7. Choose Microsoft Excel as your data source
8. Click the Browse button, locate the abc.xls file on your computer, and click Open
9. Verify that the “First row has column names” box is checked
10. Click Next to advance past the Choose a Data Source screen
11. On the Choose a Destination screen, select SQL Server Native Client as the data source
12. Choose the name of the server that you want to import data into from the Server Name drop-down box.
13. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode.
14. Choose the name of the specific database you want to import data into from the Database drop-down box
15. Click Next to continue
16. Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
17. In the Destination drop-down box, choose the name of an existing table to import the data into a table that already exists in your destination database or type the name of a new table that you wish to create. In our example, we will use this Excel spreadsheet to create a new table called “Sheet1”
18. Click Next to move past the Select Source Tables and Views screen is displays by click on Preview Button.
19. Select both Check boxes of Run Immediately & Save SSIS Package then Choose File System and select “Do not save sensitive data” in Drop down list
20. Click Next to save package in Specific location with specific name.
21. Click the Finish button to skip ahead to the verification screen
22. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.
23. It shows Execution status screen, click the Close button.
24. Finally to check data in SSMS (Sql Server Management Studio) as shown below.
In general, the import and Export Wizard provides a quick way to move data from one Source to Destination for one time use, but there are some limitations:
• You can specify only one Source and one Destination in the wizard.
• Advanced workflow precedence is not available through the wizard.
• The wizard does not share data sources with other packages.
By overcome above limitations you need to develop a new package from scratch in BIDS (Business Integration Development Studio)
With SQL Server 2008, you use the Import and Export Wizard to copy data without going through the process of creating an SSIS Project. Use the following considerations to determine which part of the wizard to use:
• Importing data with the wizard lets you bring data from various sources like flat files, data in Microsoft Office Excel spreadsheets or Microsoft Office Access databases or data in Oracle databases into a SQL Server tables.
• Exporting data with the wizard lets you send data from SQL Server tables, views or custom queries to flat files or another database.
To import data from Excel and stored into Sql server by using Import and Export Wizard in SQL Server 2008, we have to do the following steps.
Steps:
1. Start --> Programs --> Microsoft SQL Server 2008R2 --> SQL Server Management Studio (SSMS)
2. Open SQL Server Management Studio
3. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)
5. Right-click on the name of the database instance you wish to use and select “Import Data” from the Tasks menu
8. Click the Browse button, locate the abc.xls file on your computer, and click Open
11. On the Choose a Destination screen, select SQL Server Native Client as the data source
12. Choose the name of the server that you want to import data into from the Server Name drop-down box.
13. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode.
15. Click Next to continue
16. Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
22. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.
In general, the import and Export Wizard provides a quick way to move data from one Source to Destination for one time use, but there are some limitations:
• You can specify only one Source and one Destination in the wizard.
• Advanced workflow precedence is not available through the wizard.
• The wizard does not share data sources with other packages.
By overcome above limitations you need to develop a new package from scratch in BIDS (Business Integration Development Studio)
No comments:
Post a Comment