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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher GordonSenior 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
SThayaTechnical 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ashwin_shastryAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.