I would like to share something new I have learned, how to import data from an Excel file in SQL Server using the SSIS Package.
BackgroundI want to import one thousand records into a database. Much time can be wasted by entering the records one by one. Many applications exist to import and export the data into the database. So I used SQL Server 2008. In that I found a good application, the Import and Export SQL Server wizard. This application has many options, like Flat files, Excel files, Access files and so on. I have done a Flat file import into a database, you can get more details about how to Import a Flat CSV file's data here.Excel File dataMy Excel file has about 350 records. I think that manually entering each record into the database would be highly inefficient.Fig: 1 Excel DataSo I decided to import the data using the SQL Import and Export application. The first step is open the SQL Server Import and Export Wizard.Select Microsoft Excel in the Data Source option. When you select Excel the design is automatically changed as in the following picture. Fig: 2 change the data source hereSelect your Excel file path with the browse button and one more thing is changed, the Excel version. I am socked because in my system the Office 2003 version is there. I don't care about that this version. If you move down a bit you will see a check box for making the default column name in the table. As you wish you can check or uncheck it.Fig: 3 Preview your Excel dataWe have an option for previewing the Excel data. If everything is cool then proceed.Fig: 4 Change the table name hereAn Excel file has by default three sheets. All sheets are seen in the preceding image. In my Excel file only Sheet 1 has data so the Destination only shows the first file.Note: You can change the table name as needrd. Fig: 5 Column mapping
You can seen all the details about the Excel and table. The column name source, destination, type and nullable column that allow the column that will accept a null value and additional things change the size of the column. Fig: 6 Create SQL StatementYou can create the table depending on your needs. This query is generated by default. Fig: 7 OutputAfter completing that process check your database. I hope your data is successfurlly imported into the respective table. Final words: I hope you enjoyed this small article.
Basics of SQL Server