Share via

FAQ: How do I import/export data with SQL Express

  • Article

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.

Comments

  • Anonymous
    July 21, 2006
    LogParser is a 1-exe way to load SQL.

  • Anonymous
    August 01, 2006
    Hi

    I has used MSDE2000 in former times. The Import-/Export-Feature is very important for me.
    Now i am using sql2005 express and i am very much unlucky because of the lack of SSIS. This fact makes Express nearly unusable, because you need a full sql2005 for import and export tasks.

    Such tasks are moving data from one server to another. Also migrating data from one sql2000/MSDE to sql2005/Express.

    All other aspects of sql2005 express are perfect.

    In former times with dts it was a job of 5 minutes to import/export databases. Now it is a very hairy job.

    I think, if MS includes the SSIS-Services into express, then express is the killerdatabase on the market.
    I think the SSIS-features is a base-feature needed for every database.

    Peter G.

  • Anonymous
    August 08, 2006
    Peter,
    So were you using DTS via the APIs and programming? There is no Wizard and no Designer in MSDE so what were you using?

  • Anonymous
    August 09, 2006
    Very useful content, but it needs to go through a sub-editor first.  I can't read it first time through owing to the large number of comma splices, run-on sentences and so on.

  • Anonymous
    August 14, 2006
    Hi,

    Our product SQL Admin Studio (www.simego.com) makes import and export with SQL Server 2000/2005 easy. You can easily import tables across servers/databases with a few simple clicks. You can even export tables from MySQL into DataSets and import them back to SQL Server 2005. Any Xml DataSet file can be imported into SQL Server/MySQL/Access and the Schema is created from the Xml Definition.

    Also any SQL Query can be exported into Excel/CSV/DataSet or SQL Script with a single click on the menu.

    Sean

  • Anonymous
    August 15, 2006
    I have been working with SQL DB's since 1987. Yes, BCP is a good answer, and XLMBulkLoad is also worth looking into, to the address the issue of lack of import/export support in SQL Express.

    However, SQL Express is positioned as the answer to MSDE. To ship it without built-in GUI-based tools for easy data import/export is, imho, a shameless travesty. Especially, in this day and age (2006), where most of the up and coming developers are wizard-based, integrated tool users. Even Access has more import/export options than SQL Express.

    I understand and appreciate the massive undertaking with shipping new products (i.e. VS .NET 2005 and SQL 2005 with all the new goodies), but to ship a free DB to developers that is hampered without a good toolset is a Microsoft goof. You (MS) just plain missed on this one. If developers find this harder to use than Access, or Excel, or whatever, then you will lose the developers to other easier technology choices. If MS is interested in improving developer productivity, this issue is not an example of that effort.

    Anyway, enough of the rant.

    One other solution I have come up with is to use Access to export the data to a SQL Express Table, and then use SQL code to insert the data from the new table into main table.

    If you got this far, thanks for listening...

  • Anonymous
    August 16, 2006
    I am definitely interested in trying the 3rd party SQL Admin Studio tool above. If I can import and export tables (with data) that would be fantastic.  It is cheap too!

  • Anonymous
    August 17, 2006
    The comment has been removed

  • Anonymous
    August 19, 2006
    Yes, I agree totally with David.  The decision to ship without import tools was flawed.  Of course, there are other ways, but how can I convince my user base that we can effectively take MS-Access from them and replace it with SqlExpress without the tools?  You are talking about a lot of development on the backs of downsized IT depts, and shops with shrinking budgets.  Do more with what?

    I can understand the reluctance to ship a full featured database with tools, as even MS-Access had some upfront costs, but it would be an easire sell to buy into newer technology with tools that allowed the value of the newer technologies to shine.  ROI is the name of the game and our dept. cannot move from using MS-Access until we can have the features embedded in all versions of SQL.  Otherwise, the move is on to go to other competitors and their Linux based O/S.

    Rethink this decision, and push an add-on quick.  Shipping size makes no difference to the end-users and developers, even a small cost is acceptable, but the current road is going to lead to mass exodus from your built-in market of users with MS-Access.  

    As it stands now, I've been directed to start using Linux, and other databases to develop our replacement apps for MS-Access users.  Push comes to shove, and when it comes to shove, it's the lack of tools, features, and support, along with increasing license costs that are pushing shops away.

  • Anonymous
    August 21, 2006
    Bill,
    Using the eval edition would be in violation of the license agreement if the task was not evaluation usage, ie production.

    All,
    The decision is being looked at again.

    Phil,
    I am confused by your final comment. I understand lack of Import/Export Tools being a blocker, however SQL Express is free so there are no licensing costs. What tools and features (apart from the Imp/Exp) is Express missing?

  • Anonymous
    August 31, 2006
    Guyz the solution is simple. Micosoft has given the solution in SQL Server Express Service Pack 1; use this:
    "C:Program FilesMicrosoft SQL Server90DTSBinnDTSWizard.exe"

    It is as powerfull as 2000 and nearly same interface

  • Anonymous
    September 01, 2006
    I finally accomplished this by copying from the results pane, pasting into Excel, and running DTS. What a disaster.

  • Anonymous
    September 03, 2006
    All,

    MS is hiding DTS wizard in SQL Server Express [why ? Go figure]
    Look for it in
    C:Program FilesMicrosoft SQL Server90DTSBinnDTSWizard


    cheers,

  • Anonymous
    September 15, 2006
    This DTS Wizard is pretty much the same as in SQL Server Developer/Standard, which is pretty good.

    However, though you're offered the opportunity to create SSIS packages, it appears that the packages are not saved in any way. I haven't looked at my SS Standard/Dev at work to see where/how SSIS packages are saved...

    Microsoft REALLY needs to expose SSIS in the Express version, if they are going to require everyone to switch out of MSDE, Express is really much better than MSDE, and better than Access because you can do a lot more with security.

    EXPOSE SSIS!!!!

    Thank you, Tom

  • Anonymous
    September 17, 2006
    I've just tried the Simgo admin interface which costs just $29 or about £18 and it seems to do what it says on the tin. Looks like a good investment and less hassle than moving to mySQL

  • Anonymous
    September 22, 2006
    I download and installed SQL Server Express Service Pack 1 but I still can not find
    "C:Program FilesMicrosoft SQL Server90DTSBinnDTSWizard.exe". Is there anything I missed.

    Thanks!

    Kan

  • Anonymous
    September 28, 2006
    I managed to import my ms access tables into sql server express 2005 using the dtswizard that came with sql server 2000.

    It didn't work for me initially because I didn't know I had to specify the computer name with the sql server name when the wizard asked me what server to use.  So instead of just "sql express"  you have to specify something like:  "computernamesql express".

  • Anonymous
    September 29, 2006
    Okay... Tell me where I went wrong? I have downloaded the "SQL Server 2005 Express Edition SP1". I tried going to the:"C:Program FilesMicrosoft SQL Server90DTSBinnDTSWizard.exe" - and I don't even have the:...DTS... - folder!!

  • Anonymous
    October 17, 2006
    Your dishonesty just cost my employer money. They have site licenses for all versions of Sql Server so I didn't have to install Express. I did because based on your statement that it included an Import/Export facility it would best fit my needs. Now I will spend time at company expense removing it and installing another version. And now I notice that claim has been removed. How shameful of you to have defended it.

  • Anonymous
    October 30, 2006
    Earle, Sorry to hear of the issues this has caused you but I have to disagree with you. As I stated above SQL Server Express does include Import/Export, unfortunately the user community had a different perception of what that meant in the DataSheet. Hence we took it down to save confusion. Interestingly Express provides the same functionality as MSDE in this case, ie no DTS/SSIS so I am not quite sure why folks assumed thats what it meant, but thats water under the bridge now. Out of interest, what was the data you needed to import and what was the scenario.

  • Anonymous
    November 01, 2006
    Hi, just for make another example; I have to import a xml file containing multiple subtable (invoices - details). With SSIS it's a work of ten minutes, because it perfectly understand the file structure and, moreover, it generate the foreign keys to link the different tables. Trying to find a different way (i have to deploy on sql express - access), i'm not able to do this without spending long time on vb scripting / sql query. Could you suggest me a easy way for doing this? Obviously i'm seeing for IBM DB2 / oracle....

  • Anonymous
    November 03, 2006
    The comment has been removed

  • Anonymous
    November 25, 2006
    you need to download the tools addon for sql express to access dtswizard through Program Files

  • Anonymous
    December 03, 2006
    Yes!  Finally!  Thank you Junaid.  I've been trying to move all my tables out of Access into SQL server for a week after trying to deal with Access's upsize wizard.  DTSWizard working like a charm.

  • Anonymous
    January 01, 2007
    Welcome to 2007! As seems to be the trend here is the list of most popular posts for the last year, I

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/449855-copying-tables-in-sqlexpress

  • Anonymous
    May 29, 2009
    PingBack from http://paidsurveyshub.info/story.php?title=euan-garden-s-blog-faq-how-do-i-import-export-data-with-sql-express