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.

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.

Newbie:Why this Select doesn't work ?

On ASP.NET 2.0, VB, I try to let user input data on TxtBox1.Text. Then I
would like to go to SQL database named PUBS and compare the "TxtBox1.Text"
against the table "job_id". If the results match, I return on the screen
"Information matched. Your password will be reset".
First, how can I establish the connection to the "pubs" and retrieve such
information ? The code below return error "job_id" not declared. Do I really
have to declare the "job_id" or something else I did is not working ?
rtial Class Default_aspx
Sub btnDefault_Click(ByVal sender As Object, ByVal e As
System.EventArgs)
Dim sConnect As String = ConfigurationSettings.ConnectionStrings_
("pubs").ConnectionString
Dim sSQL As String = "SELECT Employee, " _
& "WHERE job_id = TxtBox1.Text"
End Sub
End Class> Dim sSQL As String = "SELECT Employee, " _
> & "WHERE job_id = TxtBox1.Text"
Your query string should be "Select * from Employee where job_id = '" &
TxtBox1.Text & "'"
"Mr. Magoo" <maggo@.nospasm.com> wrote in message
news:ub3B3mUJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> On ASP.NET 2.0, VB, I try to let user input data on TxtBox1.Text. Then I
> would like to go to SQL database named PUBS and compare the
> "TxtBox1.Text" against the table "job_id". If the results match, I return
> on the screen "Information matched. Your password will be reset".
> First, how can I establish the connection to the "pubs" and retrieve such
> information ? The code below return error "job_id" not declared. Do I
> really have to declare the "job_id" or something else I did is not working
> ?
> rtial Class Default_aspx
> Sub btnDefault_Click(ByVal sender As Object, ByVal e As
> System.EventArgs)
> Dim sConnect As String = ConfigurationSettings.ConnectionStrings_
> ("pubs").ConnectionString
> Dim sSQL As String = "SELECT Employee, " _
> & "WHERE job_id = TxtBox1.Text"
> End Sub
> End Class
>|||There is no From clause in this select statement. You need to specify that.
Dim sSQL As String = "SELECT Employee From Pubs " _
& "WHERE job_id = " & TxtBox1.Text
Thanks
Baiju
"Mr. Magoo" <maggo@.nospasm.com> wrote in message
news:ub3B3mUJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> On ASP.NET 2.0, VB, I try to let user input data on TxtBox1.Text. Then I
> would like to go to SQL database named PUBS and compare the
"TxtBox1.Text"
> against the table "job_id". If the results match, I return on the screen
> "Information matched. Your password will be reset".
> First, how can I establish the connection to the "pubs" and retrieve such
> information ? The code below return error "job_id" not declared. Do I
really
> have to declare the "job_id" or something else I did is not working ?
> rtial Class Default_aspx
> Sub btnDefault_Click(ByVal sender As Object, ByVal e As
> System.EventArgs)
> Dim sConnect As String = ConfigurationSettings.ConnectionStrings_
> ("pubs").ConnectionString
> Dim sSQL As String = "SELECT Employee, " _
> & "WHERE job_id = TxtBox1.Text"
> End Sub
> End Class
>|||You guys are rocking !!
"Baiju" <baiju@.indus-systems.com> wrote in message
news:eBE94EVJFHA.2640@.TK2MSFTNGP09.phx.gbl...
> There is no From clause in this select statement. You need to specify
> that.
> Dim sSQL As String = "SELECT Employee From Pubs " _
> & "WHERE job_id = " & TxtBox1.Text
> Thanks
> Baiju
> "Mr. Magoo" <maggo@.nospasm.com> wrote in message
> news:ub3B3mUJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> "TxtBox1.Text"
> really
>