Unable to read excel file using ssis Script Source

Question

I have a case where I need to read an excel file with filtered rows using SSIS.

I've started testing the process but all I get when I look in my table is "System.__ComObject"

I'm sure I doing something stupid.

Thanks

Public Overrides Sub CreateNewOutputRows()

Dim xlApp = New Excel.Application

Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim rw As Excel.Range
xlApp.DisplayAlerts = False

wb = xlApp.Workbooks.Open("C:\PosData\test.xlsx")


Dim visible As Excel.Range = wb.Sheets("Data").UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing)
For Each rw In visible.Rows
    Output0Buffer.AddRow()
    Output0Buffer.Column = rw.Cells(1, 1).ToString
Next

Output0Buffer.SetEndOfRowset()
End Sub

A practice I find helpful when debugging SSIS Script Tasks is to take the core logic and copy it into a .NET Console App so that I can step through and inspect the values. SSIS 2012 allows for setting breakpoints in dataflow scripts but in my mind it's still not as efficient as a full blown project. That said, shouldn't ToString be ToString()? — billinkc
– billinkc, Commented May 8, 2013 at 15:42

Daniel Möller · Accepted Answer · 2013-05-08 15:40:07Z

1

That happens sometimes when using the Interop. All objects from Excel, in this case, are indeed COM Objects.

Use Cells(1,1).Value or Cells(1,1).Value2 or Cells(1,1).Text. Wich fits best for you. (Maybe you need to Cast or Convert the Cells to Range first)

answered May 8, 2013 at 15:40

Daniel Möller

86.8k24 gold badges202 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dayton Brown Over a year ago

That was it. I was frustrated by the lack of properties appearing on the end of the cells.

Collectives™ on Stack Overflow

Unable to read excel file using ssis Script Source

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related