This is part 4 of the series of article on SSIS. In this article we shall see how to import data from SQL Server using the wizard.
Introduction
In this article we will see on how to Import data from SQL
server using the wizard which is provide with the SSMS (SQL server Management
Studio). Using SSMS we can perform many tasks like copying data from one server
to the other or from one data source to the other in variety of formats. Here
our task is to do Import data from SQL server to Excel using the Wizard.You can look into Part 1, Part 2 and Part 3 of my SSIS articles at the below urls
Part 1
http://www.dotnetfunda.com/articles/article1164-sql-server-integration-services-ssis-part-1-basics-of-ssis-and-creati-.aspx
Part 2
http://www.dotnetfunda.com/articles/article1189-sql-server-integration-services-ssis-part-2-transforming-sql-data-to-.aspx
Part 3
http://www.dotnetfunda.com/articles/article1190-sql-server-integration-services-ssis-part-3-export-data-using-wizard-.aspx
Steps:
Step 1: Go to Programs > Microsoft SQL Server 2005 > SQL Server Management Studio and connect to the list of server db’s which we
have to perform the task as shown in the below screen
Step 2: Once you
locate the Database where we need to perform the transformation then Right
click on the database and go to Tasks then select Import Data. It will open a
welcome screen Click Next and move to the Datasource tab.
Step 3: Choose a Datasource tab helps to select the source
of the data transformation selected at the initial stage itself. Once the required information’s are selected
click on next and it will ensure to select the destination source. Here we will
do the transformation from Excel to SQL DB. So select Microsoft Excel from the
drop down list as shown below.
Step 4: Now the destination data source window will open,
here we need to specify the destination (In our example SQL Server DB) so
select SQL Native Client from the drop down and Connections details to
authenticate the connection as shown in the below screen
Step 5: Now we need to specify from which table we need to
transform the data or we can write our own query based on which the data need
to be transformed. Here we can select the table so mark that option and click
on next as shown in the below screen
Step 6: Once we click on next button it will show the list
of tables to be selected (from the excel sheet). Select the table which we need
to do the transformation and click on preview to double check the output as
shown in the below screen and click on Next button.
Step 7: Once we are done with the source and destination it
will ask to save and execute the package. Click Next and Finish to complete the
transformation as shown in the below screen.
Step 8: Since we are given the option to save the SSIS
package it will ask for the configuration on which server we need to save the
SSIS. Or you can give a path to save the SSIS as show in the below diagram
Step 9: Once we are done it will show the process on the how
the task is carried over and shows the final result on the tasks completed as
shown in the below screen. If it’s completed without any error it will Copy the
data to the SQL DB table.
Conclusion
This article we have seen on how to use the Import Wizard to
make a transformation and to execute the package using the wizard.