How do I connect to an Excel spread sheet using a Script Component?Mr Shaw

It would not be different than just using the OLEDB Jet from within say a VB app. How about this example: http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm ? The same code can be incorporated inside the Script Task, you merely need to reference the proper components. Besides, I am wondering why a DFT is not used to connect using the Excel Source?Arthur My Blog

It would not be different than just using the OLEDB Jet from within say a VB app. How about this example: http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm ? The same code can be incorporated inside the Script Task, you merely need to reference the proper components. Besides, I am wondering why a DFT is not used to connect using the Excel Source? Arthur My Blog Do I need: DataGridView1.DataSource = DtSet.Tables(0)Mr Shaw

You do not need because you are processing the data in a not visual way. Besides, you did not answer my question. I have a feeling you are on the wrong path concept-wise.Arthur My Blog

I can workout how to output the data.Mr Shaw

The table mapping is confusing me: TableMappings.Add() Mr Shaw

If have no idea what I am doing. Here is my code: Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Data.OleDb Imports System.Xml Public Class ScriptMain Private connMgr As IDTSConnectionManager90 Private cnnXls As OleDb.OleDbConnection Private tablesInFile As DataTable Dim DtSet As System.Data.DataSet Public Overrides Sub PreExecute() Dim strXLS As String = Me.Connections.excelConn.ConnectionString() Dim MyCommand As System.Data.OleDb.OleDbDataAdapter MyCommand = New System.Data.OleDb.OleDbDataAdapter _ ( "select * from [Sheet1$]", strXLS) MyCommand.TableMappings.Add( "ShopName", "ShopName") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) cnnXls = New OleDb.OleDbConnection(strXLS) cnnXls.Open() tablesInFile = cnnXls.GetSchema( "Tables") End Sub Public Overrides Sub CreateNewOutputRows() Dim rows As DataRow For Each rows In DtSet.Tables Me.myOutPutBuffer.AddRow() Me.myOutPutBuffer.HotelName = rows.Table.Columns(1).ToString() Next End Sub Public Overrides Sub ReleaseConnections() cnnXls.Close() End Sub End Class Mr Shaw Inherits UserComponent '>> ADDEDD

My code give me an error reading: Unable to cast object of type 'System.Data.DataTable' to type 'System.Data.DataRow'.Mr Shaw

To just load an Excel spreadsheet, why do you need a script task? Arthur My Blog

It is hard to read what you posted but on the surface it looks like the issue is with this statement: For Each rows In DtSet.Tables in needs to be For Each rows In DtSet.Tables<strong>.Rows</strong> Arthur My Blog

Thanks for the help.. I am not a programmer... so I am not sure what you mean by <strong>Mr Shaw

Some HTML was applied, now corrected For Each rows In DtSet.Tables.RowsArthur My Blog