Solved

Get Sheetname from Excel Source using SSIS

Posted on 2016-11-03
3
53 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

From time to time, for debugging and troubleshooting your flow at run time you’ll need to check if the variable has the correct value or not, there’re several ways to check for the value of the variables inside the flow, You can add Break Points, a …
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

742 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