Data import / export with SQL Server Express using DTS Wizard

There are IMHO two major annoyances with SQL Server Express :
 - No Server Agent
 - No Import / Export function (called Data Transformation Services – DTS in SQL Server 2000)
 
Missing the Server Agent means you have to make your own backup (or other) scheduling, but luckily DTS is possible – it is just not installed as default with SQL Server Express (SSE). It’s very strange to me why Microsoft did not just include in the standard install so that it was available from SQL Server Studio, but I’ll settle for just being happy that it exists at all!

Check if you already have it by looking for this file: c:\%programfiles%\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe. If you don’t have a ‘DTS’ directory (or even a ‘90′ directory) it has not been installed.

Here is how you get it:
 - It is included in the SQL Server Express Edition Toolkit – and only that. It you have installed another version of SSE, it works fine to install this package afterwards without uninstalling the others. Get it here: http://go.microsoft.com/fwlink/?LinkId=65111
- The DTS Wizard is included in the option “Business Intelligence Development Studio” so be sure to select that for install 
- If you have installed another version of SSE, the installer might report that there is nothing to install. Override this by checking the checkbox that displays the version number (in the installer wizard)

After install has finished, the DTS Wizard is available at c:\<programfiles>\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe
you might want to make a shortcut, or even include it on the tools menu of SQL Studio.

Possibly related posts: (automatically generated)

This entry was posted on Wednesday, January 31st, 2007 at 9:42 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

70 Responses to “Data import / export with SQL Server Express using DTS Wizard”

  1. d Says:
    March 20, 2007 at 5:03 pm

    thanks for posting this. saved me a headache!

  2. Yasser Says:
    March 29, 2007 at 12:41 pm

    I followed the mentioned procedure and it worked quite well. Thanks and this information has helped me successfully transfer an entire database (structure+data) from an MS SQL Server 2005 Express database (on my local machine) to MS SQL Server 2005 (hosted online at our ISP).

  3. Data import/export on SQL Server Express (SSE) « Golbing Says:
    April 3, 2007 at 10:44 pm

    [...] before now? It is a major short-coming of SSE. A little search on the internet just now found me an entry on the Mobile Developer blog by T R Nilse with the answer. You have to install the SQL Server [...]

  4. Petter Says:
    May 25, 2007 at 11:48 am

    Thanks a lot for this great info!

  5. Henrique Andrade Says:
    August 31, 2007 at 8:30 pm

    Excelent information!
    Tk´s!

  6. jarrod brown Says:
    September 1, 2007 at 4:19 pm

    You rock!!!

  7. Scott Gardner Says:
    September 21, 2007 at 8:19 am

    Thanx dude…

  8. Dirk Pilniok Says:
    September 25, 2007 at 11:02 am

    COOOOOOOOOL!!!!! You made my day!

  9. Eddy Says:
    September 25, 2007 at 12:41 pm

    busy downloading the toolkit – hope it works…

    :P

    if this works u will hv made my month =)

  10. Real Brain Says:
    October 2, 2007 at 5:10 pm

    This was of great help. I wasted several months in fuguring out how to export data from SQL Server 2005 Express to MS Access. You made my day. But it is surprising how MS has not included this feature in Management Studio. Its stupid Microsoft.

  11. Bron Says:
    November 28, 2007 at 7:56 am

    This was exactly what I was looking for! Thanks a lot

  12. Todd Says:
    December 19, 2007 at 8:04 pm

    Thanks for this writeup. This was the exact solution i was hoping to find :)

  13. Anna Says:
    March 11, 2008 at 4:11 pm

    This is very appreciated. Thank you.

  14. Ron Says:
    March 15, 2008 at 1:58 pm

    Thanks for writing this down, perfect.

  15. omar Says:
    April 2, 2008 at 10:34 am

    thanks very much man , it helps me alot

  16. Tafadzwa Says:
    April 8, 2008 at 7:53 am

    Thanks a lot. You made my day!

  17. Nondumiso Says:
    April 9, 2008 at 6:22 am

    Thanks very much. Really appreciate

  18. chris Says:
    April 10, 2008 at 11:47 am

    Like all other comments, muchas grass… I was wondering why things were inconsistent between my XP Pro/Std Server/Vista platforms when it came to tools I KNEW were there, even for SQL Server 2005 Express. Definitely appreciated.

  19. holly Says:
    April 14, 2008 at 11:31 pm

    cool

  20. JD Says:
    April 15, 2008 at 6:19 pm

    Excellent , thanks for sharing. It worked great.

  21. OTINsiano Says:
    April 22, 2008 at 1:51 am

    thanks dude, keep it up…

  22. Dimos Says:
    May 3, 2008 at 10:07 am

    Hi,

    I cannot connect to a SQL 2000 server which is hosted online through my local SQL server 2005 express using the DTS Wizard. How I will do that?

    However I can connect to this server using the Object Explorer and see the data.

  23. Raju Visshwanath Says:
    May 7, 2008 at 11:44 am

    Excellent piece of information. Thanks a lot.

  24. Kevin Says:
    May 15, 2008 at 2:25 pm

    Thank you for posting a link to actually download the tools! I knew what I needed, but I could not find a simple link to download the tools – just several guides explaining what they do and how to use them. I didn’t know they were packaged in the toolkit, so I couldn’t even find it on any Microsoft pages. I was really starting to get frustrated until I found your page, so thanks!

  25. Steve Says:
    May 24, 2008 at 5:21 am

    Thanks. Install went well. Would have liked link on how to use it. I’m a novice as you can guess.

    Thanks Again.

  26. George Says:
    May 31, 2008 at 7:28 am

    Very usefull and comprehensive, thanks.

  27. Gagan Says:
    June 1, 2008 at 1:41 pm

    Thanks, the info is great!! Just like to correct the syntax ..

    c:\%programfiles%\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe

    You don’t need a “C:\” before “%ProgramFiles%”

    %ProgramFiles% is an Environment Variable!

  28. berney Says:
    June 19, 2008 at 2:27 pm

    superb man, it saved my time, thanks for sharing. It worked great

  29. Jaco Says:
    June 23, 2008 at 4:53 pm

    Thx, this has definitely made my day!

  30. Oz Says:
    June 27, 2008 at 8:57 pm

    Thanks!

  31. minhtuan Says:
    June 28, 2008 at 8:53 am

    many Thanks…., :)

  32. Mat Says:
    June 30, 2008 at 6:18 pm

    It works for Access 2003 to SQL Express Migration!! Thank you very much.

  33. prashant Says:
    July 15, 2008 at 6:49 am

    dear still i try to finging the “IMPORT OPTION” when right click on the sql server
    explorer in VISTA….?

  34. Sameer Says:
    August 6, 2008 at 10:09 am

    Thanks a lot man….It made my work easy.

  35. Tommaso Says:
    August 7, 2008 at 3:46 pm

    Thanks a lot! It saved my day!

  36. Big Country Says:
    August 12, 2008 at 8:10 pm

    Thanks a bunch, it has helped me much!

  37. Kooth Says:
    August 12, 2008 at 9:05 pm

    Awesome! This was a great help! You are da MAN!

  38. bob Says:
    August 23, 2008 at 7:22 pm

    well it seems this works for everyone except me….
    in the installer of toolkit the check box is grayed and the details button says ‘only maintain allowed, use add remove programs’
    anyone help?
    thx very much

  39. yohai Says:
    September 11, 2008 at 4:06 pm

    Merci from Paris/France

  40. xarax Says:
    September 20, 2008 at 8:40 pm

    GURU!!! :D

  41. John Says:
    October 9, 2008 at 1:42 am

    I can’t believe such a core feature is so buried. This is the first MS product I’ve used in a few years – and it seems they haven’t changed. I feel like it’s always “bait and switch” – here’s the free tool – but if you want easy access to core features, you’ll need to buy the enterprise edition! Too bad this approach wastes everyone’s time – time that could be spent solving real problems.

  42. Chad Says:
    October 9, 2008 at 3:06 am

    This saved my life.

  43. Fredrik Says:
    October 15, 2008 at 9:30 am

    Thanks a lot!

  44. oraras Says:
    October 15, 2008 at 4:28 pm

    Hi
    How can I transfer data to SQL Server Mobile , I have table with some rows in SQL Server but I would like to import or move or transfer to SQL Server Mobil.

    Thanks

  45. Carlos Says:
    November 16, 2008 at 10:24 pm

    Thank you; I´ve been struggling for 3 days now to find out (after trying many different workarounds) to find this up straight solution. I was really annoyed to have the tool in SQL Srv 2000 Dev ed. and not to in 2005 Express. Thanks again for sharing with all of us.

    Carlos, from Mexico

  46. KingNothing Says:
    December 3, 2008 at 10:36 am

    hey man… you rock! Thanks a lot ;)

  47. Laura Says:
    December 12, 2008 at 11:42 pm

    After hours of searching and attempting different solutions, your explanation walked me through the process as quickly as I could download and install. Thank you!

  48. Data import / export with SQL Server Express Says:
    January 6, 2009 at 8:18 am

    [...] There is a good guide here: Data import / export with SQL Server Express using DTS Wizard [...]

  49. Aleem Says:
    January 8, 2009 at 11:13 pm

    Good stuff …….. exported data from SQL 2005 Express to MS Access … but it changes the structure of fields in the tables … like text -> memo etc.

  50. The Mysterious Case of the Missing SQL Server 2005 Management Studio Express “Export Data” Functionality « Hell in a Handbasket Says:
    February 2, 2009 at 1:45 am

    [...] This chap addressed the missing DTSWizard issue, and suggests downloading the SQL Server Express Edition Toolkit which should include the wizard. [...]

  51. itay amato Says:
    February 9, 2009 at 6:37 pm

    WOW!
    i think that every person who use the Express Edition, and want to export\import (just f***ing copy) the db from one server to an other is looking for this exact article you wrote!
    thank you!!

  52. zsolt Says:
    March 2, 2009 at 11:03 am

    thanks for this article!!!

  53. kebabo Says:
    March 5, 2009 at 4:09 pm

    if such ppl woul work in MiserableSoft i would buy legit Trashdows

  54. kebabo Says:
    March 5, 2009 at 4:11 pm

    lied

  55. Konda Says:
    March 11, 2009 at 2:28 pm

    Cool article!!!

  56. Rodney Says:
    March 19, 2009 at 9:09 am

    Only one small problem… the DTS does not seem to allow one to transform data – this is allowed in SQL Server 7.0 DTS

  57. Sachin bhosale Says:
    March 30, 2009 at 7:24 am

    It is halpful for us.

  58. Jim Robert Says:
    April 1, 2009 at 2:31 pm

    You just saved me one hell of a headache! thanks :)

  59. David Woakes Says:
    May 16, 2009 at 8:38 am

    I’m trying to import into a sql server compact edition database, I’ve got the wizard (with VS2008) but cannot work out how to connect to the compact edition database.

    Any ideas?

  60. FreedomIsntFree Says:
    May 21, 2009 at 3:15 am

    I’m also trying to export from sql server CE into SQL Express db… any idea how? I don’t see a Data source that is compatible.

    HELP!

  61. Defiantclass1 Says:
    July 17, 2009 at 6:00 am

    After 2 years, this post is still helpful! Thanks for the DTS/“Business Intelligence Development Studio” tip. It worked great!

  62. africanprince Says:
    July 28, 2009 at 5:35 am

    Thanks Dude, I appreciate this post !!!

  63. Juanita Says:
    August 27, 2009 at 11:04 pm

    thank you so much, exactly what I needed. Only question I have is where does it store the import/export package after you create it? I can’t seem to locate it.

  64. Milind K Says:
    September 20, 2009 at 12:11 pm

    Was looking for the DTS wizard. Your article proved to be of great help. Many thanks !

  65. Mario Humbolth Says:
    October 1, 2009 at 5:03 pm

    Thanks Dude, i’ll be working on it.

  66. waleed Says:
    October 8, 2009 at 11:22 am

    thanks…it’s great

  67. DougFiasco Says:
    October 17, 2009 at 5:49 am

    Thanks a billion. It was the ‘checking the box’ to reinstall components that I had been missing.

  68. Andreas E. Mueller Says:
    October 22, 2009 at 4:44 pm

    Thanks a lot.

    Had to transfer a huge database from MSSQL 2000 to 2005.

    This saved my day. ;)

  69. Lony Says:
    December 10, 2009 at 9:51 pm

    This article saved my buns. Every tool I used that was the lite version dropped the export function after thirty days if it had it at all. Thanks!!!!

  70. Sujie Says:
    December 21, 2009 at 3:30 pm

    Thanks a lot. Great information. This is exactly what i was looking for. It really saved me lot of effort and time..highly appreciated.

Leave a Reply