I have problem which I cant seem to solve.
Im writing a small vb.net script to use in a ssis package.
What I want the script to do, using standard components is the
following:
Open an excel file (or connect using ado or something)
Get the number of columns in sheet1
If the number of columns are less then 15
add new columns with the names Fx until the number of columns are
15
Save and close (or just close connection)
I've managed to get the number of columns by using this code:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Data.OleDb
Imports System.Xml
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections,
variables, events,
' and logging features are available as static members of the Dts
class.
' Before returning from this method, set the value of Dts.TaskResult
to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim connectionString As String
Dim excelConnection As OleDbConnection
Dim filen As String
Dim kolumner As DataTable
Dim antal As Integer
Dim antal2 As Integer
Dim excelKolumn As DataRow
Dim i As Integer
Dim excelCommand As OleDbCommand
filen = Dts.Variables("test").Value.ToString
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & filen & _
";Extended Properties=Excel 8.0;"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
kolumner = excelConnection.GetSchema("Columns")
For Each excelKolumn In kolumner.Rows
antal = antal + 1
Next