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

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:

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 (AT) no-spam (DOT) sqldts.com> wrote

Hi Drew,

"Drew" wrote:
I thought I explained this before.
You should loop with one transform task from Excel to SQL Server and import
each file one after the other.
The second example form SQLDTS I referenced before describes this for an
text file connection, which is similar.
You just have to write the path for the excel connection derived from your
rowset at the beginning of each loop, nothing more ;-)

The only decission you must make is, wether you can/will insert the data
directly into your destination table, or you will collect the data in an
working/staging table and then push them do the productiv tables.
The latter is usefull if you want to minimize the locking time of the
tables to prevent performance problems for the normal application, and if
you have to decide if you must insert, update or discard data.

Helge

I understood your first explanation, just wanted to throw this out there
(since you didn't address it)... I know there are many ways to skin a cat,
so that is why I asked.

Thanks for pointing out the second part. The application is just used for
reporting, and there is going to be a schedule implemented, therefore I
won't need the staging tables.

Thanks for your help!
Drew


"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote