SSIS processing multiple excel files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS processing multiple excel files

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-09-08 : 11:45:24

Hello
I need VB script help!!!!

I am receiving multiple excel file to process into a folder:

C:\Epi_download\Wafer_uploads\OB200907N1418.xls
C:\Epi_download\Wafer_uploads\OB200907N1426.xls

this wouldn't be bad except sheet1$ on all the file are the file name:

OB200907N1418$
OB200907N1426$

I have a foreach loop storing each file path in @[User::WaferFiles]
and have edited the connection string of excel connector to handle each new file connection in the loop.
BUT
I need to also give the sheet name to a variable workSheetName for the excel connector
SO
I figured I could use a vb srcipt to get my worksheet names from files variable.
The problem is I don't have the VB skills to extract the worksheet (OB200907N1418$) from each file path (C:\Epi_download\Wafer_uploads\OB200907N1418.xls)

Dim sheetName As String
Dim fileName As String = CStr(Dts.Variables("WaferFiles").Value)

?

Dts.Variables("WorksheetName").Value = sheetName

Dts.TaskResult = Dts.Results.Success

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-09-08 : 12:11:55

I was thinking something like this but it gets error

Public Sub Main()
'

Dim fileName As String = CStr(Dts.Variables("WaferFiles").Value)

' Returns "World!".
Dim subString As String = Microsoft.VisualBasic.Right(fileName, 17)
Dim subString2 As String = Microsoft.VisualBasic.Left(subString, 13)
Dim sheetName As String = subString2 + "$"

Dts.Variables("WorksheetName").Value = sheetName

Dts.TaskResult = Dts.Results.Success

End Sub

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-09-08 : 16:31:13

never mind I just copied my variables and did my convertions in a sql table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources