Solved

Get Sheetname from Excel Source using SSIS

Posted on 2016-11-03
3
41 Views
Last Modified: 2016-11-07
Hi All,

I have a scenario where I am getting data from an excel source using excel connection manager. From that excel, I want to get the sheet name and load it into database table. Sheet name format is: ABCDEF - 12345678

From that sheet name, I want to get only "12345678" and want to load it into database table, please guide.

Thanks in advance.
0
Comment
Question by:hennanra3
  • 2
3 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41872624
Use Script task.

string nm = SheetName.Substring(SheetName.IndexOf(" - ") + " - ".Length);

Hope it helps.
0
 

Author Comment

by:hennanra3
ID: 41875946
I will really appreciate if you can please elaborate in detail about your provided solution as I am a newbie in SSIS.

Thanks
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41876090
Ok, .. Try below code in your Script task.

--

Dim excel As New Microsoft.Office.Interop. Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop. Excel.Workbook
Dim wSheet As Microsoft.Office.Interop. Excel.Worksheet

wBook = excel.Workbooks.Open
wSheet = wBook.ActiveSheet()

For Each wSheet In wBook.Sheets
	MsgBox(wSheet.Name)
Next

--

Open in new window


Src - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/499f6f6f-0717-48dc-881d-f384e24f85f7/get-the-sheetname-of-the-excel-sheet-using-script-task?forum=sqlintegrationservices
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package). The latter is certainly a headache for developers, particularly for me. We had to ma…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question