Re: DTS Transform Loop

You have it right. I just come from an ASP background and I wasn't thinking
clearly when I asked the question. In ASP it is more efficient to insert
the data from the RS to the array, then manipulate the array (display data,
etc) than it is to open an RS and use the MoveNext commands. The reason is
because it keeps the trips to the SQL Server down.

I just wanted to ask before I worked on this, just so I knew what would be
the better way.

Thanks,
Drew


"Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx> wrote in message
news:2AC703DF-C1FD-4930-8698-6B6112E813EF@xxxxxxxxxxxxxxxx
> Just so I understand
>
> You have a table which contains filenames to Excel files. You want to
> pick
> up those files and enter data into a destination. You would like to know
> what is more efficient.
>
> Grabbing all the rows in each spreadheet into an array and then insert the
> array OR
> Loop over the files and insert 1 at a time.
>
> I would go for the second option. I do not know the perf difference but
> if
> you have 5000 spreadsheets say then you will have to hold 5000 * n rows in
> memory until the end of the loop before inserting whereas doing it on each
> file would be just the rows in the file right?
>
> Excuse me if I have interpreted the requirements badly.
>
> Allan
>
> "Drew" wrote:
>
>> I understand how to loop through a RS and extract data (using Active-X
>> controls and SQL Task), but now I need to add a data transform in there
>> (from Excel to SQL). Here is the lowdown,
>>
>> The SQL Task just uses a simple query,
>>
>> SELECT ExcelLink
>> FROM Cardholder
>>
>> This returns about 7 records (maybe up to 10 max)
>>
>> Then this goes into the Active-X loop and the ExcelLink is loaded into
>> the ExcelLink global variable, and is looped (using the tutorial here,
>> http://www.sqldts.com/default.aspx?298). Then the ExcelLink variable is
>> shown in a messagebox. The data that needs to be inserted is as follows,
>>
>> CREATE TABLE [dbo].[Cardholder] (
>> [CardholderID] [varchar] (20) -- NT username
>> [CardholderName] [varchar] (50) -- Name of cardholder
>> [Department] [varchar] (50) -- Department of Cardholder
>> [CardNumber] [varchar] (15) -- Credit Card Number (not full #, i.e,
>> ***********1234)
>> [CardTypeID] [int] -- Type of card
>> [RuntimeUserID] [varchar] (20) -- If the Cardholder doesn't do his own
>> data entry (i.e. secretaries, etc), this is the username of the data
>> entry person.
>> [ExcelLink] [varchar] (50) -- Link to Excel file on server (same
>> server as SQL Server)
>> [DiffRuntimeUser] [bit] -- Flag to tell if there is a different user
>> that does the data entry
>> [Admin] [bit] -- Flag to tell if user is admin
>> ) ON [PRIMARY]
>> GO
>>
>> Now I have a question about the most efficient way to get the data in
>> there. Should I,
>>
>> A. Execute the transform after each loop step?
>>
>> Or should I,
>>
>> B. Extract all the data into an array, then feed the array (all data for
>> all cardholders) to the transform at the end of the loop?
>>
>> I may be going overboard on this one, since of course there is only 7 -
>> 10 cardholders and each log has a max of 200 records. I just want to
>> make sure that whatever I build is scalable, so I can use it in the
>> future for other (bigger) projects.
>>
>> Thanks for all your help. This group has been a great place for me to
>> learn about a very powerful asset to my arsenal.
>>
>> Thanks,
>> Drew
>>


.

Loading