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.

Advertisements

102 Responses to Data import / export with SQL Server Express using DTS Wizard

  1. d says:

    thanks for posting this. saved me a headache!

  2. Yasser says:

    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. […] 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:

    Thanks a lot for this great info!

  5. Henrique Andrade says:

    Excelent information!
    Tk´s!

  6. Scott Gardner says:

    Thanx dude…

  7. Dirk Pilniok says:

    COOOOOOOOOL!!!!! You made my day!

  8. Eddy says:

    busy downloading the toolkit – hope it works…

    😛

    if this works u will hv made my month =)

  9. Real Brain says:

    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.

  10. Bron says:

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

  11. Todd says:

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

  12. Anna says:

    This is very appreciated. Thank you.

  13. Ron says:

    Thanks for writing this down, perfect.

  14. omar says:

    thanks very much man , it helps me alot

  15. Tafadzwa says:

    Thanks a lot. You made my day!

  16. Nondumiso says:

    Thanks very much. Really appreciate

  17. chris says:

    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.

  18. JD says:

    Excellent , thanks for sharing. It worked great.

  19. OTINsiano says:

    thanks dude, keep it up…

  20. Dimos says:

    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.

  21. Excellent piece of information. Thanks a lot.

  22. Kevin says:

    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!

  23. Steve says:

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

    Thanks Again.

  24. George says:

    Very usefull and comprehensive, thanks.

  25. Gagan says:

    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!

  26. berney says:

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

  27. Jaco says:

    Thx, this has definitely made my day!

  28. minhtuan says:

    many Thanks…., 🙂

  29. Mat says:

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

  30. prashant says:

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

  31. Sameer says:

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

  32. Tommaso says:

    Thanks a lot! It saved my day!

  33. Big Country says:

    Thanks a bunch, it has helped me much!

  34. Kooth says:

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

  35. bob says:

    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

  36. yohai says:

    Merci from Paris/France

  37. xarax says:

    GURU!!! 😀

  38. John says:

    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.

  39. Chad says:

    This saved my life.

  40. oraras says:

    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

  41. Carlos says:

    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

  42. KingNothing says:

    hey man… you rock! Thanks a lot 😉

  43. Laura says:

    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!

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

  45. Aleem says:

    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.

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

  47. itay amato says:

    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!!

  48. zsolt says:

    thanks for this article!!!

  49. kebabo says:

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

  50. kebabo says:

    lied

  51. Konda says:

    Cool article!!!

  52. Rodney says:

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

  53. Sachin bhosale says:

    It is halpful for us.

  54. Jim Robert says:

    You just saved me one hell of a headache! thanks 🙂

  55. David Woakes says:

    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?

  56. FreedomIsntFree says:

    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!

  57. Defiantclass1 says:

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

  58. africanprince says:

    Thanks Dude, I appreciate this post !!!

  59. Juanita says:

    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.

  60. Milind K says:

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

  61. Mario Humbolth says:

    Thanks Dude, i’ll be working on it.

  62. waleed says:

    thanks…it’s great

  63. DougFiasco says:

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

  64. Thanks a lot.

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

    This saved my day. 😉

  65. Lony says:

    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!!!!

  66. Sujie says:

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

  67. tregi says:

    ..post of the year!!!…
    thanks a lot

  68. Preeti says:

    This was very helpful. Thankx!

  69. Steve says:

    This is one of the things I love best about Microsoft. They are like the pizza parlor near my house. Great products, but everything is half-baked.

  70. nirdesh says:

    When I import image(long binary data)from ms access to sql server 2008, color image is not importing..only black n white images are comming…color images row comming blanks,
    please help how to solve this problem.

  71. Derek says:

    This was a great article. I keep getting an error though. Cannot create a task with the name “DTS.Pipeline”. Verify that the name is correct.

    I uninstalled SQL Server and started from scratch, but I keep getting the error when I try to access the wizard. Any thoughts?

  72. Mohammad Imran says:

    Thanx for help.

  73. Farghana says:

    Thanks a lot.It worked exactly the way i needed!! Thanks once again! 🙂

  74. Zeeshan says:

    Thanks Dear it was very helping and solved one of my problems.. thanks again.

  75. Annie says:

    Your post is still helping people now. I keep asking myself why this important feature is excluded from SQL 2005. Thanks for your valuable information.

  76. andy says:

    Thank you, thank you, thank you. Even the “pros” at microsoft don’t seem to know this. I ordered 2005 dev. but don’t need afterall.

    Anyone know how to add a button for import export wizard and better yet on right click on tables as in sql2000.

    Thank you again!!!

  77. Charles Miller III says:

    Does anyone know where the SQL server toolkit exists for the 2008 express version? Looks like this one is for 2005. Thanks.

  78. TK says:

    Thank you somuch! I was looking into /90/ directory but couldnot find the DTS folder after the instllation but found it in program file (x86) directory. It saved me a lot!

  79. Kelli Norman says:

    you are AWESOME! thanks for taking the time to post this.

  80. Ed Eaglehouse says:

    Thanks for the info. Unfortunately, I’m running Vista and already installed SQL Server Express. The Toolkit fails installation.

    FWIW, I also tried the Microsoft Database Publishing Wizard to export my tables. It’s broken, too.

    • Ed Eaglehouse says:

      Update: the problem was a bug in the Microsoft SQL Server Native Client installer.

      If you get the error “An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package ‘sqlncli.msi’.”, uninstall e Native Client before installing the toolkit.

      The toolkit installer will reinstall the Native Client as part of its prerequisite components without complaining.

  81. Kamil says:

    Thanks for keeping this post live. I could not find the DTS installer. Your link helped me find it.

  82. Rilleh says:

    I did install the toolkit but did not select the “Business Intelligence Development Studio” in the installation process (really bad choice) and now I’m facing the same problem as bob does.

    How can I fix this? Is it safe to completly uninstall the 2005 Management Studio Express without losing any data?

  83. trnilse says:

    Did you try to run the maintenance option? (Control Panel->Add/Remove programs)

    It should be safe to uninstall Management Studio, but if you have valuable data you should have backups anyway, right?
    (Right-click the DB in management studio and select Tasks->Back Up…)

  84. cherif says:

    this is very helpul. many thanks. God bless you.

  85. Hqcurious says:

    你太好了

  86. syed says:

    what to do next /// when we opend DTS .exe

  87. Anonymous says:

    tried to install the toolkit but it errors out that all component are already installed? anyway to get around that?

  88. Anonymous says:

    This is exactly what I’ve been looking for – getting tired of copy/paste from Excel!

  89. Shashi Singh says:

    This is strange. I ran my DTSMigrationWizard.exe wizrad once and the when I tried to run it again it gave me this error
    TITLE: DTS Migration Wizard Error
    ——————————

    Cannot generate SSPI context (Microsoft OLE DB Provider for SQL Server)

    —————————-

    Any idea why?

  90. Webshopkeeper|Witlox Internet Service…

    […]Data import / export with SQL Server Express using DTS Wizard « Mobiledeveloper[…]…

  91. Thanks, i used it and works for me……….

  92. This is certainly the second posting, of urs I really read through.
    Still I like this one, “Data import / export with SQL Server Express using DTS Wizard Mobiledeveloper”
    the best. Take care -Etta

  93. camaroqqq says:

    Did anyone figure out what to do if the checkbox is grayed out? Do I have to uninstall server management studio completely, then install this file?

  94. Firstly, I want to thank you for the very interesting information you
    blogged here about back pain. I work as a professional fitness and pilates coach| and I have met people with
    low back pain. I myself had some issues with my back previously and I can tell for sure that fitness will definitely help if you know methodologies or work with professionals

  95. Anonymous says:

    Thank you

  96. Bryan says:

    Hi Guys,

    Is there anyone know how to see this wizard under task??so when i create a report in excel i simply right click the database and go to task and click it to export import?anyone?

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: