If you really want to import data from an excle file with T-SQL only, you can do this:

1. Open a passthrough query. This can be done by first creating a linked server, and then querying against it or using OPENROWSET() (there are more wyas to do it, but these two are good enough as an example)

2. remove linked server (if you used the linked server method.

All in all, you're looking at 2-3 lines of SQL code.

As to the statement that you can't do with DTS anything that you can't do with regular SQl, I don't agree with it...but it's just MHO.

regards, Mark.

quote:

---

If you really want to use DTS, you can set anything you'd like in code. For the SQL, check the SourceSQLStatement of the DataPumpTask object. For the text file name and location, set the datasource property of the connection object. With the exception of file format translations, there is little you can do with DTS that cannot be done with TSQL. If anyone knows how to take an excel file and import it using only TSQL, I'd like to hear about it!

Now for some stupid questions...

Why not use bcp and xp_cmdshell?

If you're going to involve VB, why write all those property let statements instead of passing those values to your procedure? i.e.

sqltoText(ByVal server as string, ByVal database as string, ByVal sql as string, ByVal filename as string, ByVal ConString as string)

Finally, why not;

txt.Write rs.GetString

Please advise.

---