SSIS import

Kongta
Kongta used Ask the Experts™
on
I would like to Import following xls [http://www.imf.org/external/pubs/ft/weo/2015/02/weodata/download.aspx] into SQL by SSIS. My SQL table should look like

ISO            Code             year            rate
AFG         PCPIPCH       2008            26.41
AFG                PCPIPCH       2009          -6.81
AFG               PCPIPCH        2010           2.17

but the xls has a different listing like

ISO               Code                 2008           2009            2010        2011
AFG                   PCPIPCH           26.41         -6.81             2.17         ...

What would be in your view the most efficient way to Import by SSIS?

Shall I go for coding like
INSERT INTO ISO, Code , year (2008), rate
SELECT ISO, Code, 2008....

or something like Looping in SSIS with variables (year) +1?

thx for your Input
rgds
Kongta
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
You'll have to consume the file as it's written, which means either map it accordingly and do a PIVOT-INSERT in T-SQL, or I believe SSIS has a PIVOT transform you can add between source and destination.
Kongta

Author

Commented:
just googled the Pivot and got some results as YouTube-Videos, think that might be the way. let me try. thx a lot, rgds

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial