Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to perform this particular excel importing task, kindly help Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2014 6:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:19 PM
Points: 86, Visits: 220
Hi Friends,

I have one task assigned whereas I need to import one excel file into the sql server DB. I have completed the task in a complex way, below is the details.

what I did:

1) Excel has 5 tabs (sheet1-sheet5)
2) splited the tabs as seperate excel sheets with only one sheet (sheet1 only)
3) created seperate connection managers for each tab (5 files now) and seperate excel source for each.
4) loaded into the DB

what I need:
1) I need to do this in a automated way, so that if in future few more tabs added or few tabs removed in the source excel it can process only those number of tabs.
2) I know we may do this through "for each loop" container. but I am very new to SSIS, so I am struggling a lot to do that.
3) I have given the sample excel sheet and the sample of package as a screen shots.

Note: each tabs vary in its columns number and row numbers.

please find the attachment for reference.


  Post Attachments 
SampleExcel.xlsx (3 views, 17.00 KB)
SampleControlFlow.jpg (1 view, 143.95 KB)
SampleDataFlow.jpg (1 view, 168.50 KB)
Post #1599664
Posted Tuesday, August 5, 2014 6:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 13,277, Visits: 11,066
This article should get you started:

Loop through Excel Files and Tables by Using a Foreach Loop Container




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1599670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse