To continue from the last posting, a project that I am associated with needs to deal with four non-SQL Server data sources. I'll start with the easy one first...
The first data source type is text files. We can get the data in these files into SQL Server with BULK INSERT (which seems deprecated in SQL Server 2005, but seems to still be in SQL Server 2008) or OPENQUERY using the bulk option. This is all native to SQL Server, so I'm not counting this as a major issue.
Exporting to text is a problem, but there are only a few very simple examples of that in the codebase. We are trying to avoid use of xp_cmdshell for all of the usual reasons. For most of our issues, we can probably use SSIS or even a custom vbscript in a job. The worst-case scenario is a small CLR procedure.
(Why-o-why doesn't SQL Server have a BULK EXPORT?)
I'd prefer to keep these OPENQUERY statements in place because they are mostly in views and procedures stored in user databases. It's much easier to manage database objects than it is to manage objects like DTS or SSIS packages. Packages are stored at a server level rather than a database level. In other words, if I backup a database and give it to someone else, I have to remember to export the packages and give those as well. The other thing is that it's not feasible to run a package when a client program runs a select statement and return some sort of result set from the package. Not all of our code is nicely bound up into stored procedures (which is hardly uncommon), so there is no way to 'get in there" with some sort of shim and run something before a client expects results.
The data amounts in play here are small, usually no more than a few thousands of rows, so performance should never be a problem. So, to sum up, this isn't that much of a problem. The first real problem will be detailed in the next posting.