no available input columns

Hi ,

Im trying to build a package that will copy data from excel to SQL

in a program

unfortunately , when I open the package xml file and I drill into the

oledb destination I see that I have no available input columns

what could be the problem ?

thanks ahead

Eran

p.s

the script:

Dim p As Package = New Package()

Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As TaskHost = CType(e, TaskHost)

thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Create excel connection MANAGER

Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")

excelCon.Name = "ExcelSourceConn"

excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\try\try\try.XLS;Extended Properties=""Excel 8.0;HDR=YES"""

' Create sqldev connection Manager

Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")

sqlCon.Name = "sqldevConn"

sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEV\SQLDEV"

''create source component

Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

excelSource.Name = "ExcelSource"

excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"

Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()

excelInstance.ProvideComponentProperties()

excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))

excelInstance.SetComponentProperty("AccessMode", 0)

excelInstance.SetComponentProperty("OpenRowset", "business_codes$")

excelCon.AcquireConnection(Nothing)

'excelInstance.ReinitializeMetaData()

excelInstance.ReleaseConnections()

Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

sqldev.Name = "sqldev"

sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"

Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()

sqldevInstance.ProvideComponentProperties()

sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))

sqldevInstance.SetComponentProperty("AccessMode", 0)

sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")

sqldevInstance.AcquireConnections(Nothing)

sqldevInstance.ReinitializeMetaData()

sqldevInstance.ReleaseConnections()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))

MsgBox(excelSource.OutputCollection.Count)

'For Each input As IDTSInput90 In sqldev.InputCollection

' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' ' Call the SetUsageType method of the design time instance of the component.

' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

' Next

'Next

Dim app As Application = New Application()

app.SaveToXml("c:\myXMLPackage.dtsx", p, Nothing)

Hi,

I found your post and was wondering if you ever got a response to this? I'm having the same problem

Thanks

Cat

|||

When you open the package in the designer do you see any columns published on the source adapter?

Also to clarify: is the problem that the virtual column collection is empty in your program or the input column collection is empty when you look at your destination adapter afterwards?

The commented piece of code is supposed to add columns to the input column collection.

Thanks.