"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OL7KscjvFHA.2348@TK2MSFTNGP15.phx.gbl...
> Try
>
> set tsk = pkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
>
>
>
>
> "Snake" <Snake@discussions.microsoft.com> wrote in message
> news:A4355741-52BF-4C82-8700-6C7D47CC2655@microsoft.com:
>
>> I am trying to change the SourceObjectName of a datapump which reads an
>> Excel file. It poops on: tsk.SourceObjectName = "Sheet2$" saying this
>> method is not supported. OK. I need to read an Excel file whose sheet
>> name
>> changes each time. DTS defaults to "Sheet1$" which will not work in
>> this
>> case.
>>
>> Any ideas? Thanks
>>
>>
>> Function Main()
>> Dim pkg, tsk
>> set pkg = DTSGlobalVariables.Parent
>> set tsk = pkg.Tasks("DTSTask_DTSDataPumpTask_1")
>> tsk.SourceObjectName = "Sheet2$"
>> Main = DTSTaskExecResult_Success
>> End Function
>

"Darren Green" wrote:

> Full example-
>
> Changing the DataPump Source and Destination Tables
> (http://www.sqldts.com/default.aspx?213)
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:OL7KscjvFHA.2348@TK2MSFTNGP15.phx.gbl...
> > Try
> >
> > set tsk = pkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
> >
> >
> >
> >
> > "Snake" <Snake@discussions.microsoft.com> wrote in message
> > news:A4355741-52BF-4C82-8700-6C7D47CC2655@microsoft.com:
> >
> >> I am trying to change the SourceObjectName of a datapump which reads an
> >> Excel file. It poops on: tsk.SourceObjectName = "Sheet2$" saying this
> >> method is not supported. OK. I need to read an Excel file whose sheet
> >> name
> >> changes each time. DTS defaults to "Sheet1$" which will not work in
> >> this
> >> case.
> >>
> >> Any ideas? Thanks
> >>
> >>
> >> Function Main()
> >> Dim pkg, tsk
> >> set pkg = DTSGlobalVariables.Parent
> >> set tsk = pkg.Tasks("DTSTask_DTSDataPumpTask_1")
> >> tsk.SourceObjectName = "Sheet2$"
> >> Main = DTSTaskExecResult_Success
> >> End Function
> >
>
>