Solved

Get Sheetname from Excel Source using SSIS

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

Expert Comment

by:Pawan Kumar Khowal
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 18

Accepted Solution

by:
Pawan Kumar Khowal 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Expressions are in essence, code. If you had an expression 2 + 2 it would evaluate to 4. A string expression of “Hello” + “ “ + “World” would evaluate to “Hello World”. Expressions are an excellent choice for working with dynamic variables, espec…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

13 Experts available now in Live!

Get 1:1 Help Now