Uploading Data from Excel File into SQL tables Using SSIS

Hello,

I am a novice in SSIS and would like to achieve the following.

I will be given an Excel file as a data source which will have data. I will need to use SSIS packages to
extract this data from Excel into SQL tables.

I think I will be using SQL Server 2008 for Development.

I would like some useful links and articles on how to manipulate data from Excel using SSIS packages.

One particular problem I would be concerned about is that the tables being inserted into may already
contain the same data, so in cases where the same data already exists the package should update
else insert.

Also any articles where I can get information on Joining in SSIS packages will be really helpful.

If I can get a sample project that would be ideal.

Thanks
Ashwin


Ashwin_shastryAsked:
Who is Participating?
 
SThayaConnect With a Mentor Technical MAnagerCommented:
Hi ,

For inserting and updating existing Rows you can use  the below logics.

1. use Slowly changing dimension.

http://blogs.msdn.com/b/mattm/archive/2009/11/13/handling-slowly-changing-dimensions-in-ssis.aspx

http://www.mssqltips.com/sqlservertip/1442/handle-slowly-changing-dimensions-in-sql-server-integration-services/

2. you can use look up component .... based on the input value you can redirect the matching and non matching rows .

for no matching rows-- like insertion
For matching rows ,  ... use the execute sql task to update the exiting values

http://msdn.microsoft.com/en-us/library/ms141821.aspx
0
 
Christopher GordonConnect With a Mentor Senior Developer AnalystCommented:
One "High Level" recommendation,

Use a staging table to load data from Excel to SQL Server.  The staging table will be a one to one record "mirror image" of your Microsoft Excel Spreadsheet.  I'll even create a schema called "staging" for these tables (just to organize in ssms).

Once the data is in the staging table, use T-SQL to do manipulation, apply business rules, etc.

If you're using SQL 2008, use the MERGE statement to move the data from the staging table to the main table.  The main table will contain all of the data ever loaded from the staging table.  Be sure to include DateTimeStamps on this main table.

When the merge is complete, truncate your staging table

http://technet.microsoft.com/en-us/library/bb510625.aspx

Loading data from Excel can be a challenge.  Always make sure your spreadsheet doesn't contain special characters like CrLF and LF or SSIS will run into errors.  
0
 
Ashwin_shastryConnect With a Mentor Author Commented:
Thanks for quick replies guys ..  I will take a look at these links and get back to you.

I hope you guys can help me out as I mentioned before I don't have much knowledge in SSIS.

Thanks,
Ashwin
0
 
Ashwin_shastryAuthor Commented:
These were just links/leads to the solution but not the solution itself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.