N/APosted on - 06/01/2017
I am preparing for SQL Server Interviews and to enhance my grip on the concept and get a basic idea about SQL Server database management systems I want to learn about importing and exporting data using Microsoft SQL server integration services. Please help me out.
How To Use Microsoft SQL Server Integration Services To Import And Export Data
SQL Server Integration Services (SSIS) is a segment of SQL Server 2005 and later forms. SSIS is a venture scale ETL (Extraction, Transformation and Load) device which permits you to create information incorporation and work process arrangements.
Aside from information joining, SSIS can be utilized to characterize work processes to robotize upgrading multi-dimensional 3D shapes and computerizing upkeep errands for SQL Server databases. It helps in automating Data movements with the help of packages that composed of tasks enabling the data to move and alter it on the way.
The import and export wizard is an essential component of SSIS that protects us from the complexity of SSIS while enabling easy movement of data between various sources data such as SQL Server Databases, Flat files, Microsoft Excel worksheets etc.
TO launch the Import and Export wizard go to tasks entry in the Object explorer windows of SQL Management Studio.
Follow these steps now:
1. Begin your SQL Server Management Studio and then log in to your test server.
2. Now you will have to open a new query window.
3. See the Available Databases combo box on the toolbar and select master database.
4. Now you will have to enter this text into the query window: CREATE DATABASE Chapter 7. Chapter 7 is basically the name of the database.
5. Press the Execute toolbar button for creating a new database.
6. In the object explorer, expand Databases node by clicking that plus icon.
7. Right-click on the Chapter 7 database and select the Tasks => Import Data.
8. Then Import and Export Wizard will open. You are required to read that page thoroughly and after that click Next.
9. Now Select SQL Native Client for the data source and fill it with proper login information so as to access your test server.
10. Now for selecting the source of data select the AdventureWorks2008.
11. Press Next tab.
12. Now the connections information regarding chapter 7 database will be opened because we are importing the information. Press Next.
13. Next step include copying data from More Tables or One or Views and snap Next. Take note of that on the off chance that you just need to import some portion of a table you can utilize a question as the information source.
14. Select the HumanResources.Department, hello
HumanResources.Shift tables. As you select tables, you will see that the wizard will be automatically assigning names for the target tables.
15. Now, HumanResources.Shift table> go to and select Edit Mappings button.
16. Then dialog box named Column Mappingswill be opened and it enables us to change the name, data type, and various other characteristics of the destination table columns. When you have completed inspecting the options you can click on cancel button.
17. Now you have to press Next.
18. Click and check Execute Immediately and then click Next tab to move to next window.
19. Now in order to finish the import you are required to click on the Finish button. The import will thus begin.
20. Press Cross button to finish the report.
21. Explore the Tables node of the Chapter 7 database to verify that the import succeeded.
Another usage of Import and Export wizard is that it is used to save a package for later execution