DTS to do SQL to MS Access

Okay guys, maybe I'm making this much more complex than it needs to be. I've got a SQL DB and want to export certain tables to Access. My first thought was DTS, right? So I started down the road of the DTS - build the DTS on my local SQL box, made the Access DB, and everything was good.

Then I thought about my deployment environment: there are several clients that will use this. Some will have their own environment, so no problem. Yet others will host at an ISP, which typically means that the SQL box only has TCP/IP access to the webserver, so where do you tell the DTS package to put the Access DB? Then I thought about using the FTP task, but this blasted thing only gives you a mapped network location to put the resulting file. DAMN! (Now why wouldn't you have the ability to put an FTP ADDRESS AS THE DESTINATION OF THE FREAKING FTP TASK - I suppose that would make too much f-ing sense!)

So here I am, at a loss for what to do. I thought about coding the transformation in .NET, but what a major pain in the ****! Then maybe thought about using DTS from within C#, but can't find any solid examples of doing this.

HELP!!!Hi Rob

Maybe this will get you on the right track. http://www.sqlteam.com/item.asp?ItemID=12408

If not, there are a couple of other FTP and DTS articles on SQLTeam|||Okay, I've worked up a little C# routine to actually execute the DTS packages and it gets so far, but then get an error that it doesn't like the userID because it's null (although I am passing the userID into the DTS execution). I've got to be missing something pretty simple here - any ideas?

Here's the C# code that calls the DTS package:

DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer("sqlmachinenamehere", "sa", "passwordhere", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection.DTSSQLStgFlag_Default, null, null, null, "Export_Association_Table", ref pVarPersistStgOfHost);

And the error that is produced:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.