Download to read offline
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic ZDNet Asia SmartPlanet TechRepublic Log In Join TechRepublic FAQ Go Pro! Blogs Downloads Newsletters Galleries Q&A Discussions News Research Library IT Management Development IT Support Data Center Networks Security Home / Blogs / The Enterprise Cloud Follow this blog: The Enterprise Cloud How to import an Excel file into SQL Server 2005 using Integration Services By Tim Chapman October 9, 2007, 5:34 AM PDT Integration Services, which replaces Data Transformation Services (DTS) in SQL Server 2005, is a wonderful tool for extracting, transforming, and loading data. Common uses for Integration Services include: loading data into the database; changing data into to or out from your relational database structures; loading your data warehouse data; and taking data out of your database and moving it to other databases or types of storage. This article describes how you can use the new features of SQL Server 2005 Integration Services (SSIS) to load an Excel file into your database. Note: There are several wizards that come with SQL Server Management Studio to aid you in the import and export of data into and out of your database. I will not look at those wizards; I will focus on how you can build a package from scratch so that you don’t have to rely on the wizards. To begin the process, I open SQL Server Business Intelligence (BI) Development Studio, a front- end tool that is installed when you install SQL Server 2005. The BI Development Studio is a scaled down version of Visual Studio. Then I select New Integration Services Project and give the project a name. See Figure A. Figure A http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-1-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic When the project opens, you will see an environment that may look familiar to you if you have used SQL Server DTS; some of the items of the toolbox are the same. For the purposes of this project, I am interested in dragging the Data Flow task item from the toolbar into the Control Flow tab. (The idea of a Data Flow task is one of the major differences between DTS and SSIS packages. In an SSIS package, you can control the manner in which your package logic flows inside of the Control Flow tab. When you need to manage the data aspects of your project, you will use the Data Flow task. You can have several different Data Flow tasks in your project — all of which will reside inside the Control Flow tab.) See Figure B. Figure B Double-click the Data Flow task that you have dragged onto the Control Flow tab. The available options in the Toolbar have changed; I now have available Data Flow Sources, Data Flow Destinations, and Data Flow Transformations. Since I am going to import an Excel file into the database, I will drag the Excel Source item from the Toolbar onto the screen. See Figure C. Figure C http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-2-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic The Excel Source item represents an Excel file that I will import from somewhere on my network. Now I need somewhere to put the data. Since my plan is to put the data into the database, I will need a Data Flow Destination. For the purposes of this example, I will choose SQL Server Destination from the Data Flow Destination portion of the toolbar and drag it onto my Data Flow tab. See Figure D. Figure D To designate which Excel file I want to import, I double-click the Excel Source item that I moved onto the screen. From there, I find the Excel file on the network that I want to import. See Figure E. Figure E http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-3-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic I also need to designate the sheet from the Excel file that I want to import, along with the columns from the sheet that I want to use. Figures F and G depict these options. Figure F Figure G http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-4-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic Now that I have defined my Excel source, I need to define my SQL Server destination. Before doing that, I need to indicate the Data Flow Path from the Excel file to the SQL Server destination; this will allow me to use the structure of the data defined in the Excel Source to model my SQL Server table that I will import the data into. To do this, I click the Excel Source item and drag the green arrow onto the SQL Server Destination item. See Figure H. Figure H To define the database server and database to import the data, double-click the SQL Server Destination item. I will define the server in which I will import the data, along with the database that the data will reside. See Figure I. Figure I http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-5-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic I also need to define the table that I will insert the Excel data into. I will create a new table named SalesHistoryExcelData. See Figure J. Figure J Under the Mappings section, I define the relationship between the Input Columns (the Excel data) and the Destination Columns (my new SQL Server table). See Figure K. Figure K http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-6-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic Once I successfully define the inputs and outputs, my screen will look like the one below. All I need to do now is run the package and import the data into the new table by clicking the green arrow in the top-middle of the screen, which executes my package. See Figure L. Figure L Figure M shows that my package has successfully executed and that 30,000 records from my Excel Source item have been transferred to my SQL Server destination. Figure M http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-7-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic You can download the Excel file I used for this article. Tasks in SSIS packages Importing and exporting data are some of the simplest, most useful tasks to accomplish in SQL Server. However, there are literally hundreds of other tasks that can easily be accomplished in SSIS packages that will take a significant amount of time to do by a different means. I plan to take a look at several more of these tasks in future articles. Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com. —————————————————————————————– Get SQL tips in your inbox TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today! Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free newsletters. About Tim Chapman Full Bio Contact Prevent users from storing Five new developments in .MP3 files on your Windows storage infrastructure Server 2003 solutions 42 Join the conversation! Add Your Opinion Comments Follow via: http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-8-2048.jpg)
![How to import an Excel file into SQL Server 2005 using Integration Services | TechRepublic Staff Picks Top Rated Most Recent My Contacts See All Comments Import excel files into remote server 0 runa1 14th Sep Votes Thanks!! This is really useful. However I want to import excel file into a database on a remote server whereas this allows me to do it only on a local computer. Any ideas for that? Thanks. View in thread Import of some Excel files into SQL Server 2005 0 olugbenja@... 9th Aug 2011 Votes Simple and very clear, Tim thank you very much. View in thread :) 0 sarah hh 16th Jul 2011 Votes so useful !!! thanks ! I can solve my problem by your article. be happy. View in thread See all comments Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion. Join Login http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]](https://image.slidesharecdn.com/howtoimportanexcelfileintosqlserver2005usingintegrationservices-121023051519-phpapp02/75/How-to-import-an-excel-file-into-sql-server-2005-using-integration-services-9-2048.jpg)
Uploaded byKaing Menglieng
How to import an excel file into sql server 2005 using integration services
The document describes how to import an Excel file into SQL Server 2005 using Integration Services. It involves: 1) Creating an Integration Services project and adding a Data Flow task 2) Dragging an Excel source and SQL Server destination onto the Data Flow tab 3) Mapping the columns from the Excel file to the columns in a new database table 4) Executing the package to import the Excel data into the SQL Server table.
How to import an excel file into sql server 2005 using integration services
- 1. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic ZDNet Asia SmartPlanet TechRepublic Log In Join TechRepublic FAQ Go Pro! Blogs Downloads Newsletters Galleries Q&A Discussions News Research Library IT Management Development IT Support Data Center Networks Security Home / Blogs / The Enterprise Cloud Follow this blog: The Enterprise Cloud How to import an Excel file into SQL Server 2005 using Integration Services By Tim Chapman October 9, 2007, 5:34 AM PDT Integration Services, which replaces Data Transformation Services (DTS) in SQL Server 2005, is a wonderful tool for extracting, transforming, and loading data. Common uses for Integration Services include: loading data into the database; changing data into to or out from your relational database structures; loading your data warehouse data; and taking data out of your database and moving it to other databases or types of storage. This article describes how you can use the new features of SQL Server 2005 Integration Services (SSIS) to load an Excel file into your database. Note: There are several wizards that come with SQL Server Management Studio to aid you in the import and export of data into and out of your database. I will not look at those wizards; I will focus on how you can build a package from scratch so that you don’t have to rely on the wizards. To begin the process, I open SQL Server Business Intelligence (BI) Development Studio, a front- end tool that is installed when you install SQL Server 2005. The BI Development Studio is a scaled down version of Visual Studio. Then I select New Integration Services Project and give the project a name. See Figure A. Figure A http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 2. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic When the project opens, you will see an environment that may look familiar to you if you have used SQL Server DTS; some of the items of the toolbox are the same. For the purposes of this project, I am interested in dragging the Data Flow task item from the toolbar into the Control Flow tab. (The idea of a Data Flow task is one of the major differences between DTS and SSIS packages. In an SSIS package, you can control the manner in which your package logic flows inside of the Control Flow tab. When you need to manage the data aspects of your project, you will use the Data Flow task. You can have several different Data Flow tasks in your project — all of which will reside inside the Control Flow tab.) See Figure B. Figure B Double-click the Data Flow task that you have dragged onto the Control Flow tab. The available options in the Toolbar have changed; I now have available Data Flow Sources, Data Flow Destinations, and Data Flow Transformations. Since I am going to import an Excel file into the database, I will drag the Excel Source item from the Toolbar onto the screen. See Figure C. Figure C http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 3. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic The Excel Source item represents an Excel file that I will import from somewhere on my network. Now I need somewhere to put the data. Since my plan is to put the data into the database, I will need a Data Flow Destination. For the purposes of this example, I will choose SQL Server Destination from the Data Flow Destination portion of the toolbar and drag it onto my Data Flow tab. See Figure D. Figure D To designate which Excel file I want to import, I double-click the Excel Source item that I moved onto the screen. From there, I find the Excel file on the network that I want to import. See Figure E. Figure E http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 4. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic I also need to designate the sheet from the Excel file that I want to import, along with the columns from the sheet that I want to use. Figures F and G depict these options. Figure F Figure G http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 5. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic Now that I have defined my Excel source, I need to define my SQL Server destination. Before doing that, I need to indicate the Data Flow Path from the Excel file to the SQL Server destination; this will allow me to use the structure of the data defined in the Excel Source to model my SQL Server table that I will import the data into. To do this, I click the Excel Source item and drag the green arrow onto the SQL Server Destination item. See Figure H. Figure H To define the database server and database to import the data, double-click the SQL Server Destination item. I will define the server in which I will import the data, along with the database that the data will reside. See Figure I. Figure I http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 6. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic I also need to define the table that I will insert the Excel data into. I will create a new table named SalesHistoryExcelData. See Figure J. Figure J Under the Mappings section, I define the relationship between the Input Columns (the Excel data) and the Destination Columns (my new SQL Server table). See Figure K. Figure K http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 7. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic Once I successfully define the inputs and outputs, my screen will look like the one below. All I need to do now is run the package and import the data into the new table by clicking the green arrow in the top-middle of the screen, which executes my package. See Figure L. Figure L Figure M shows that my package has successfully executed and that 30,000 records from my Excel Source item have been transferred to my SQL Server destination. Figure M http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 8. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic You can download the Excel file I used for this article. Tasks in SSIS packages Importing and exporting data are some of the simplest, most useful tasks to accomplish in SQL Server. However, there are literally hundreds of other tasks that can easily be accomplished in SSIS packages that will take a significant amount of time to do by a different means. I plan to take a look at several more of these tasks in future articles. Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com. —————————————————————————————– Get SQL tips in your inbox TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today! Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free newsletters. About Tim Chapman Full Bio Contact Prevent users from storing Five new developments in .MP3 files on your Windows storage infrastructure Server 2003 solutions 42 Join the conversation! Add Your Opinion Comments Follow via: http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]
- 9. How to importan Excel file into SQL Server 2005 using Integration Services | TechRepublic Staff Picks Top Rated Most Recent My Contacts See All Comments Import excel files into remote server 0 runa1 14th Sep Votes Thanks!! This is really useful. However I want to import excel file into a database on a remote server whereas this allows me to do it only on a local computer. Any ideas for that? Thanks. View in thread Import of some Excel files into SQL Server 2005 0 olugbenja@... 9th Aug 2011 Votes Simple and very clear, Tim thank you very much. View in thread :) 0 sarah hh 16th Jul 2011 Votes so useful !!! thanks ! I can solve my problem by your article. be happy. View in thread See all comments Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion. Join Login http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205[08/29/2012 3:16:54 PM]