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.

Newboe: table containing XML schemas in database

I just created a schema in the AdventureWorks DB (the place for newbies). Where are these schemas stored and what t-sql statement can list all XML Schemas defined in a database\table\column?

TIA,

Barkingdog

select x.name, t.name, c.name
from sys.column_xml_schema_collection_usages cx,
sys.tables t,
sys.columns c,
sys.xml_schema_collections x
where x.xml_collection_id = cx.xml_collection_id
and t.object_id = cx.object_id
and c.column_id = cx.column_id
and c.object_id = t.object_id

The best way to learn this (catalog views) is thru SQL Server Management Studio -> Object Explorer -> Database -> master -> Views -> System views. Just do select * from every one of them.

Newbie: XML as datasource

Hi everyone,
I have a Web Form with data fields. What I want to do is to save these
fields into an XML file (dynamically), then hook this XML (as a data source)
to a SQL Report so that I don't have to save these data into a SQL Server
database. Is it possible? If it is then can someone show me how.
Any suggestion is greatly appreciated.
Many thanks in advance.There is currently no support for accessing an XML data source. You either
have to write your own custom XML data source extension or look on the market
for an XML OLEDB provider. Or search MSDN for sample code that implements an
XML data source extension. My current understanding is that it will be
included with the SQL Server 2005 release of Reporting Services.
HTH
Charles Kangai, MCT, MCDBA
"Calvin KD" wrote:
> Hi everyone,
> I have a Web Form with data fields. What I want to do is to save these
> fields into an XML file (dynamically), then hook this XML (as a data source)
> to a SQL Report so that I don't have to save these data into a SQL Server
> database. Is it possible? If it is then can someone show me how.
> Any suggestion is greatly appreciated.
> Many thanks in advance.|||Hi
I used the "Microsoft OLE DB Simple Provider"
1.) Create new Shared Data Source
General: "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6"
Credentials: "Use Windows Authentication (Integrated Security)"
2.) Create new Report using this Shared Data Source
Query: path to the xml-file "c:\temp\test.xml"
Kind Regards
Daniel
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:4A0288C1-9C16-4CDF-981A-88B89475616C@.microsoft.com...
> There is currently no support for accessing an XML data source. You either
> have to write your own custom XML data source extension or look on the
> market
> for an XML OLEDB provider. Or search MSDN for sample code that implements
> an
> XML data source extension. My current understanding is that it will be
> included with the SQL Server 2005 release of Reporting Services.
> HTH
> Charles Kangai, MCT, MCDBA
> "Calvin KD" wrote:
>> Hi everyone,
>> I have a Web Form with data fields. What I want to do is to save these
>> fields into an XML file (dynamically), then hook this XML (as a data
>> source)
>> to a SQL Report so that I don't have to save these data into a SQL Server
>> database. Is it possible? If it is then can someone show me how.
>> Any suggestion is greatly appreciated.
>> Many thanks in advance.