Mike Bosch's Blog on .NET

Brilliant, I had the same problem with skipping over a few rows in the beginning. This helped a lot.

Great tip! It would have helped me a lot if I had found it by the time I was dealing with the same scenario... I've spend a lot of time learning how to read Excel files via Script Component, and I thinks that setting OpenRowSet property would be enough for almost all files. Well, at least it worth the learning.

BTW, I've mentioned your post in my blog (it's in Portuguese).

In the script task ReadOnlyVariables excelfile

Readwritevariables-excel tables

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Imports System.Xml

Imports ADODB

Imports System.Data.OleDb

Imports System.Data.SqlClient

Public Class ScriptMain

   Public Sub Main()

       Dim excelFile As String

       Dim connectionString1 As String

       Dim connectionString2 As String

       Dim excelConnection As OleDbConnection

       Dim dbconnection As SqlConnection

       Dim tablesInFile As DataTable

       Dim tablesindb As DataTable

       Dim tableExcel As DataTable

       Dim tableCount As Integer = 0

       Dim tableDBcount As Integer = 0

       Dim tableInFile As DataRow

       Dim tableInDB As DataRow

       Dim currentTable As String

       Dim currentDBTable As String

       Dim tableIndex As Integer = 0

       Dim excelRecordSet As ADODB.Recordset

       Dim excelDataReader As OleDbDataReader

       Dim intSuccess As Integer

       Dim strTableName As DataTable

       Dim excelTables As String()

       excelFile = Dts.Variables("excelfile").Value.ToString

       connectionString1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

           "Data Source=" & excelFile & _

           ";Extended Properties=Excel 8.0"

       excelConnection = New OleDbConnection(connectionString1)

       excelConnection.Open()

       connectionString2 = "Data Source=MEDLINK64BIT;Initial Catalog=RCMS_VALIDATION; Integrated Security=True;"

       dbconnection = New SqlConnection(connectionString2)

       dbconnection.Open()

       tablesInFile = excelConnection.GetSchema("Tables")

       tableCount = tablesInFile.Rows.Count

       ReDim excelTables(tableCount - 1)

       For Each tableInFile In tablesInFile.Rows

           currentTable = tableInFile.Item("TABLE_NAME").ToString

           excelTables(tableIndex) = currentTable

           tableIndex += 1

       Next

       Dts.Variables("exceltables").Value = excelTables

       Dim i As Integer

       For i = 0 To excelTables.Length - 1

           If excelTables(i).Substring(0, 6) = "SOURCE" And excelTables(i).Substring(excelTables(i).Length - 1, 1) = "$" Then

               Dim command As New OleDbCommand("select * from [" & excelTables(i).ToString() & "A:AL]", excelConnection)

               Dim rdr As OleDbDataReader = command.ExecuteReader

               Dim BulkCopy As New SqlBulkCopy(dbconnection)

               BulkCopy.DestinationTableName = "Op_PaymentMatch_SOURCE"

               BulkCopy.WriteToServer(rdr)

           End If

       Next

       Dts.TaskResult = Dts.Results.Success

   End Sub

   Private Function ReadVariable(ByVal varName As String) As Object

       Dim result As Object

       Try

           Dim vars As Variables

           Dts.VariableDispenser.LockForRead(varName)

           Dts.VariableDispenser.GetVariables(vars)

           Try

               result = vars(varName).Value

           Catch ex As Exception

               Throw ex

           Finally

               vars.Unlock()

           End Try

       Catch ex As Exception

           Throw ex

       End Try

       Return result

   End Function

   Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)

       Try

           Dim vars As Variables

           Dts.VariableDispenser.LockForWrite(varName)

           Dts.VariableDispenser.GetVariables(vars)

           Try

               vars(varName).Value = varValue

           Catch ex As Exception

               Throw ex

           Finally

               vars.Unlock()

           End Try

       Catch ex As Exception

           Throw ex

       End Try

   End Sub

End Class

I have a similar problem. I have a large text dataset of 220,000 lines and I want to import every 10th line into excel. How is this done?

Are you using Studio 2005 or 8? 5 dont't take 'sheet1@A9:Q21'

What about if the sheet name has a space in it (e.g. [New Sheet1]$. Ive tried every combination of brackets ([]) and single-quotes('') that I can think of and SSIS cannot recognize the table (worksheet) with the range with a space in the name.

Thanks, this helped a lot