swathi chinni
asked on
Load multi tab Excel data into SQL Server table using SSIS
I have a requirement that I need to load an Excel file with multiple tabs into the SQL Server table.
The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.
Truncate and reload raw tables for current tab and last month tab.
How do we do dynamic tab variables for identifying ingestion in SSIS?
FYI.. TABS in the Excel file are named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.
These tabs are named at random - not in a sequence..
Can anyone please guide me?
FYI..I wanted to load just 2 months data at the same time not all months. For Instance,
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.
Appreciate your response.
Here is the example how tabs are arranged.
Jan, Feb, Mar, Apr, May, Jun, Aug,Jul, Sep, Oct, Nov, Dec.
The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.
Truncate and reload raw tables for current tab and last month tab.
How do we do dynamic tab variables for identifying ingestion in SSIS?
FYI.. TABS in the Excel file are named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.
These tabs are named at random - not in a sequence..
Can anyone please guide me?
FYI..I wanted to load just 2 months data at the same time not all months. For Instance,
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.
Appreciate your response.
Here is the example how tabs are arranged.
Jan, Feb, Mar, Apr, May, Jun, Aug,Jul, Sep, Oct, Nov, Dec.
Hi think you need to following steps.
1. Two variable one for tabcurrentmonth and one for tabnextmonth
2. set those variable by execuitng script task or execute esql task
3 While importing data flow task you need to first truncate data from table based on those two variable.
4. then after use excel query like select * from [ tabcurrentmonth ] where F1 is not null or F1 !=''.
union
select * from [ tabnextmonth] where F1 is not null or F1 !=''.
5. Mapping data thoguh destination source or use rowset .
1. Two variable one for tabcurrentmonth and one for tabnextmonth
2. set those variable by execuitng script task or execute esql task
3 While importing data flow task you need to first truncate data from table based on those two variable.
4. then after use excel query like select * from [ tabcurrentmonth ] where F1 is not null or F1 !=''.
union
select * from [ tabnextmonth] where F1 is not null or F1 !=''.
5. Mapping data thoguh destination source or use rowset .
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
afaik there is no 'dynamic' means of pulling this off without a really big Script Task that edits connection properties, and of course setting Delay Validation on everything that would use these connections to True.
>These tabs are named at random - not in a sequence..
Extremely bad idea. SSIS requires a well-defined 'contract' between source and target, and does not 'do' randomness unless you want to write a really large script task to predict every nuance of random.