Hi,

I'm trying to use DTS to solve the following mess:

1) I have 120 excel spreadsheets/day
2) I run 120 macros on these excel spreadsheets
- the macro does the following:
1) Inserts three columns
2) Enters in data: server, date, time. These are all the same
for a given spreadsheet.
3) Then I use BCP to import into SQL tables.

I was shown DTS. And it looks great. Except I cannot figure how to do
some preprocessingon my csv. I'd love to have something add three
columns, insert the data. Could this be done like my excel macros?
Prompt for the server, date, time for each file, then away it goes? It
would save me hours.

Thanks in advance!

I would load the files using DTS and then add the columns
usin the data recently loaded. For instance:

1-. Load the file to a table call temp
2-. Populate the destination table with:

insert into destination table
select *, [server], [date], [time]
from temp

Well, unless I misunderstood the original situation, some
macros are adding the 3 extra columns and BCP is
performing the upload to the final table.

My suggestion was aiming to use DTS for the load and add
the columns without using the macros. A truncate log task
can be added as a part of the DTS, if increasing of the
log is an issue.

Thanks, I did this and it works great.

Wow, this is cool stuff. If you don't mind one more question...

Is there any easy way to have an ActiveX script populate the text file
that is transformed? It doesn't seem to let me use a global variable,
as it wants to parse the file immediately or at least check that it
can.

Ideally, I'd love the ActiveX script to fire and prompt: What file?
I'd then enter a file, it would then figure out the server name and
time from the file name and away it would go.

Thanks again!

Ok, I posted a follow up that I got this working. And now I can
dynamically change the file name. Very fun.

Now the icing on the cake would be:

Scroll through this directory and for every file named X, run this
package.

I'm going to do a little research, it shouldn't be too hard, right?

Thanks everyone!

In article <9488fec2.0307021150.6809f788 (AT) posting (DOT) google.com>, Neal
Myerson <nmyerson (AT) hotmail (DOT) com> writes
Perhaps this article may help. You can use the file system object to
enumerate files and drive loop of this-

Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?6,103,246,0,1
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com