Solved

Get Sheetname from Excel Source using SSIS

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

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 24

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

910 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

22 Experts available now in Live!

Get 1:1 Help Now