Excel to Sql server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Excel to Sql server

Author

Topic

kondap
Starting Member

6 Posts

Posted - 2009-01-29 : 14:51:25

I have a requirement to import data frome excel to sql server.

The columns in Excel are

Summary,Resource1,Resource1Link,Resource2,Resouce2Link etc...

Sql server table strucutre is: 'SummaryResourceTable'
Columns: Summary, Resource, ResourceLink

What is the best way to load all the Resources(1,2,3,...) and corresponding ResourceLinks(1,2,3...) to 'Resource' and 'ResourceLInk' columns

I have many columns in this structure. Any help is greatly appreciated

I am using Sql Server 2005.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 15:31:45

Load data from Excel to Staging table with Excel Source and OLEDB destination

Use UNPIVOT TASK in SSIS to Massage the layout

Import back to Destination table

kondap
Starting Member

6 Posts

Posted - 2009-01-30 : 09:44:46

Do you have any link/blog on how to use UNPIVOT transformation. Iam new to SSIS.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 10:21:36

quote:
Originally posted by kondap

Do you have any link/blog on how to use UNPIVOT transformation. Iam new to SSIS.

Watch the video and learn from Brian Knights:
http://www.jumpstarttv.com/ssis-stumpers-v1-unpivot-example_268.aspx

kondap
Starting Member

6 Posts

Posted - 2009-01-30 : 14:36:29

This is great link. thanks Sodeep.

Now I am able to load Resources and ResourceLinks without any issues.

Table: ResourceID, Resources, ResourceLinks

1)
But if I have to import data for another column 'ResourceID' into the same table, I am not able to accomplish.

Error: PivotKeyvalue is not valid. In an UnPivot transform with more than one unpivoted destination column, the set of pivot key values must match exactly. I tried unique names but still the same error.

2)To load another set of data (Events, EventLinks) into the same table, do I need to use another 'UNPIVOT' transformation?

Appreciate your help

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 15:20:17

You need to use UNPIVOT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources