What are some Best Practices to ETL from Excel to SQL Server 2008?
2 posters
Page 1 of 1
What are some Best Practices to ETL from Excel to SQL Server 2008?
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!
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
Re: What are some Best Practices to ETL from Excel to SQL Server 2008?
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!
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
![-](https://2img.net/i/empty.gif)
» What are some Pros and Cons of ETL from Excel into my SQL Server DW?
» Nulls and SQL Server 2008
» 1 instance or 2 in SQL Server 2008
» Book sample for "DW Toolkit with SQL Server 2008 R2"
» Partitioning Discussion in SQL Server 2008 R2
» Nulls and SQL Server 2008
» 1 instance or 2 in SQL Server 2008
» Book sample for "DW Toolkit with SQL Server 2008 R2"
» Partitioning Discussion in SQL Server 2008 R2
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|