What are some Best Practices to ETL from Excel to SQL Server 2008?

View previous topic View next topic Go down

What are some Best Practices to ETL from Excel to SQL Server 2008?

Post  BI Consultant on Sat Aug 20, 2011 8:54 am

I'm new to using SSIS and would appreciate any help.
I'm creating an ETL process to transfer data from flat files (csv) into SQL Server. These are 2 files that will update two fact tables on a weekly basis.

What are some best practices for this process?
1. Do I need to drop the fact tables and recreate them each week? Or can I just append the newly added csv file rows to my fact tables. And how?
2. Can I use one csv workbook with two sheets for my source data instead of using two separate csv files?
3. What could be some error handling techniques that I can incorporate in my package?
3. Any other thoughts?

Thanks in advance!

BI Consultant

Posts : 18
Join date : 2011-08-09

View user profile

Back to top Go down

Re: What are some Best Practices to ETL from Excel to SQL Server 2008?

Post  Tootia on Tue Aug 30, 2011 3:26 am

From what you've written, it seems that data in the Excel files are not static and each week can be different. We don't use SSIS in our workplace, but please find below my suggestions:

1. Do I need to drop the fact tables and recreate them each week? Or can I just append the newly added csv file rows to my fact tables. And how?
* It depends on your requirement, if you need to keep the old records in your fact table, you have to keep the fact table and insert the new records to it. If not, possibly it should be ok to truncate the table and reload it based on the new flat file (your business users can answer it).

2. Can I use one csv workbook with two sheets for my source data instead of using two separate csv files?
* You can import the Excel file into your job under two names (e.g: MyExcel1 and MyExcel2) and use them as the source of your fact table in a sequence. First use MyExcel1 with the first sheet as you source and load data, then add another data flow to load from MyExcel2 importing from another sheet of it.

3. What could be some error handling techniques that I can incorporate in my package?
You need to check whether the Excel file is in place and ready for load.

I hope it had helped. Good luck!

Tootia

Posts : 7
Join date : 2011-08-30
Location : Australia

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum