Solved

Get Sheetname from Excel Source using SSIS

Posted on 2016-11-03
3
35 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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…
SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now