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.GetStringPlease advise.
quote:Thanks for the reply. I've never created a linked server in code before. I'll look into that. Sounds like a good option.With regards to the DTS vs TSQL comment, you lost me somewhere in the double negative. I was trying to say, if it can be done with a dts package, it can be done in tsql script.
quote:I don't know what kind of vbscript transformations you're doing. Most I've seen are string manipulations combined with if then logic. This can all be done in SQL. I'd be interested to see how and why you're building dts packages from within dts packages... I have written stored procedures that build and execute sql, but never packages that build and execute packages. Got an example to share?