As a follow up to Tuesdays post, lets take a look at how this changes with SQL Express.

(as always a little history)

In MSDE for SQL Server 7/2000 the DTS runtime was included, this included the Package and Pump object models as well as the dtsrun tool, but there was no wizard and no designer. This decision goes back to the target for MSDE being ISVs, the idea being they could use DTS as an embedded component of their app(in fact thats what we did at my old company that I was describing here; we had our own DTS designer that used our terminology and abstraction to generate and execute packages.). One of the other reasons for none of the UI elements being included(and we did spend a lot of time looking into shipping the designer as a control and shipping the wizard) was actually cost, MSDE was localised into more languages than the rest of SQL Server because it was included in Office and the cost of doing those extra languages was VERY high in terms of test time etc as we had to special case everything and not use all the std infrastructure we had for the other languages.

So onto SQL Express, SQl Express does not include DTS or its replacement SSIS, we looked at just shipping the DTS2000 runtime but there were all sorts of challenges with that, we also looked into shipping the wizard, but that added a lot of extra space to Express and as I described here, we sweated blood to try and keep the size down, hence SSIS was not included, although I know the team are looking at this decision again based on forum feedback.

Ok there is no DTS and no SSIS, so why did this page state that import/export is a feature of express? Well there are more ways to get data into and out of SQL Server and in fact its one of those generational things, if you were brought up on SQL 7 or later than you likely think of DTS/SSIS for import export, if you were brought up a little earlier than that then you probably think of BCP and OpenRowset, more about these further down. We did actually go back and forth a fair amount on the descriptions used in the matrix for the SKUs as to whether we should talk about import export, in the end we decided to do it but it looks like we created some confusion, so the page has been flipped back.

Lets talk about different types of data;

Text

If you want to import text there are several options,

• BCP is a command line tool that can be used to import text files, it runs as an external process but has been around for ages and is stable and predictable, it also has some nice touches like format files.
• Bulk Insert is a T-SQL statement that runs inside the SQL Server process space(so there are perf advantages, it handles much the same as BCP and also supports format files including a new easier to read XML format in SQL2005, however its future is less certain.
• OpenRowset, this is the new recomended T-SQL way, I have not played with it much but it seems fast, stable and easy to use.
• Code, you can always use the text file classes in NetFx (especially those that come with the VB package) and then just use the SqlBulkCopy object(this will accept a DataSet a DataTable or a DataReader).

XML

Again several options,

• OpenRowset, work just as easily with XML as it does with Text
• Code(1) you can use System.Xml and System.Data to generate a dataset, datatable etc, there is actually some new capabilities in .Net 2.0 around the datatable being able to consume XML directly
• Code(2) XMLBulkLoad is a very cool COM object that allows you to load mutliple tables from a single XML document (under the covers it uses OLE DB and FastLoad which is what DTS and SSIS use).
• Exotic... couldn't think of a better word, you can use other products like Biztalk to get XML data into SQL Server

Excel/Access

• OpenRowset using the Jet OLE DB Provider
• Access includes a capability to copy data into SQL Server, earlier versions will not work with SQL Server 2005

Other Relational Sources

• OpenRowset with the right OLE DB Provider.

Of oourse there are lots of 3rd party options as well maybe the responses to this post wil include some of them.