How to migrate data from Excel Sheet to any type of database using sqlserver2008 integration services


My Issue

  • When I try to make the packages responsible for migrating the lookups tables from the source database to my data warehouse  for my bi-solution I found that some lookups tables are not  located in database but in the code as ENUMS

Solution

  1. I fill the lookups tables in Excel sheets and make package responsible for migrating data from the excel to my data warehouse database

Steps

1. I fill the lookups tables from the ENUMS  file in to Excel sheet every sheet in excel file present  one table

2. Sample for ENUMS  file

namespace REMS.Business

{

public enum UnitBookingResult

{

CanBeBooked = 0,

BookingExists = 1,

ContractExists = 2,

SalesBookingExists = 3,

SalesContractExists = 4,

NotMarkedForSale = 5,

IsVirtual,

NotEnoughArea,

SubMergedUnit,

InActiveUnit

}

2. The Excel Sheet Screenshot I Create

6-19-2009 12-53-17 AM

3. Open new integration services project from SQL Server Business Intelligence Development Studio

present

4. Select Excel source

1

5. Select Excel File Path

browse-for

6. Select Table or View and choose the table you want it as your source from excel file

2

7. Add OLEDB Destination and connect it with the Excel source

3

8. Create  A New Connection

4

9. Select the database you want to transfer  the data to it

5

10. Choose the destination table

7

11. Map the columns from source table to the destination table

8

12. Make previous steps for the rest of the lookups table

6


Finally Run the Package

This entry was posted on Tuesday, June 30th, 2009 at 1:44 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply