none
Import/Export data with SQL Server 2005 Express

    Question

  • Hello folks!
    I have installed SQL Server 2005 Express and SQL Server Management Studio Express. Everything looks ok and works fine, but I can't find the Data Import/Export Wizard.  Can anyone tell me how to Import/Export data?

    Thank you!!!
    Monday, December 05, 2005 3:26 AM

Answers

  • 1) Download the Microsoft SQL Server 2005 Express Edition Toolkit (223.9 MB) at:

               http://go.microsoft.com/fwlink/?LinkId=65111

               being sure to select all the components so the weird red X's go away

     

    2) Run  "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"

    Saturday, April 21, 2007 4:22 PM
  • If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

    Before you start exporting make sure you have a data base in SQL where you want to export to.

    Open your data base in access

    Right click on the table you want to export

    Select export

    A dialog box opens...

    at the bottom of this dialog box there is a "Save as Type" drop down box

    select it and scroll to the bottom you will find "ODBC Databases () "

    select it

    Give your table a new name, or leave as is, if it that suits you.... Click OK

    A Select Data Source dialog box opens..... Click the "New" button

    A create new data source dialog opens

    Scroll to the bottom of that box and select SQL Server

    click NEXT

    give your data source a name...... what ever you like.

    click NEXT.... click FINISH

    a 'Create New Data Source to SQL Sever' dialog box opens

    Give your connection a description

    select or type the name of your server in the Server drop down box

    Click NEXT...... Click NEXT......

     

    check the 'change the default data base to' check box

    select the data base where you want the export your table to.

    Click NEXT.... click Finish.

    That's all. Now you can select each table from you access data base then select

    Export.......

    ODBC databases ()

    now you can select the data source you created then click OK

    return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

     

    Hope this is helpful

    • Proposed as answer by MGSeggerman Tuesday, September 22, 2009 8:14 PM
    • Marked as answer by Kalman TothEditor Monday, August 02, 2010 9:29 PM
    Friday, May 19, 2006 2:08 AM
  • You can you other means to move data in and out of SQL Express that don't rely on SSIS. BCP is one such tool, you can find more information about BCP at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

    If you are working with data in Access, you can use the functionality of Access to directly export the data into SQL Server. Most other programs can generate data in a format that BCP can accept.

    Mike
    Monday, December 05, 2005 6:29 PM
  • There are some people here who do not understand what SQL Server Express is. First of all lets see what it is not:

    1. It is NOT a evaluation edition of SQL Server 2005. You can download the Evaluation Edition from MSDN and this comes with all the bells and whistles but will expire after 180 days of eval. It also cannot use more than 1Gb of RAM and can only use 1 processor.

    2. It is NOT a development edition. SQL Server 2005 Development Edition is cheap and comes with all the bells and whistles so you can develop a database solution for your clients. You may not use development edition in a production environment.

    3. It is NOT a training edition. Although you can learn something about SQL Server 2005 using Express you cannot learn everything.

    4. It is NOT a replacement for SQL 2000 and DTS, only for MSDE. SQL Server 2005 is a replacement for SQL Server 2000, SQL Server Integration Services is a replacement for DTS and SQL Server Express is a replacement for MSDE only.

    5. It is NOT a replacement for MS Access.

    SQL Server Express is a free to use & free to distribute database engine. Thats All.

    Development Senario

    You have written a nice little application using Visual Basic. The original design used an Access database file in the back end. You decide you much prefer the power, speed, security and functionality that a SQL Server database can provide. However your application will become too expensive for what its worth if your customers have to buy SQL Server with your app.

    Well your customers do not have to buy SQL Server. You can freely distribute SQL Server Express with your distribution and for no more cost, your app is running on a powerfull back end and your customers think you are a hero.

    In order to design and develop your distribution database you buy SQL Server 2005 Development Edition and you have all the power you need to develop your database for free distribution.

     

    Back to the question, how do you import data into SQL Server express. This depends on what software you have on your machine as there are a few options.

    1. Create a new ODBC Data Source which points to your SQL Express Instance. Default name is [MACHINE]\SQLEXPRESS

    Use BCP (Bulk Copy Program BCP.exe)
    or If you have MS Access and your source is Access, export from Access to your new ODBC Data Source.
    or If you have MS Access and your source is Excel, import from Access and export to ODBC.
    or If you have MS Access and your source is Text, import from Access and export to ODBC.
    or If you have SQL Server 2000, use DTS as usual.
    or If you have SQL Server 2005, use SSIS.
    or If you have Visual Basic, write an app to do it.
    or If you have the Internet and a Credit Card, buy a 3rd party app to do it.

    There are plenty of options.

    What Not To Do
    "I just got a brilliant Db engine for absolutely no money at all, but I am really upset that it doesn't have any fancy wizards for performing tasks I should know how to do myself."

    Don't moan. Learn how to use it. If you don't want to learn, but rather use fancy wizards, then buy SQL Server 2005.

    • Proposed as answer by M.Glenn Monday, October 05, 2009 1:59 AM
    • Marked as answer by Kalman TothEditor Tuesday, October 09, 2012 6:03 PM
    Monday, August 28, 2006 12:34 PM
  •  Wolfey305 wrote:

    If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

    Before you start exporting make sure you have a data base in SQL where you want to export to.
    Open your data base in access
    Right click on the table you want to export
    Select export
    A dialog box opens...
    at the bottom of this dialog box there is a "Save as Type" drop down box
    select it and scroll to the bottom you will find "ODBC Databases () "
    select it
    Give your table a new name, or leave as is, if it that suits you.... Click OK
    A Select Data Source dialog box opens..... Click the "New" button
    A create new data source dialog opens
    Scroll to the bottom of that box and select SQL Server
    click NEXT
    give your data source a name...... what ever you like.
    click NEXT.... click FINISH
    a 'Create New Data Source to SQL Sever' dialog box opens
    Give your connection a description
    select or type the name of your server in the Server drop down box
    Click NEXT...... Click NEXT......

    check the 'change the default data base to' check box
    select the data base where you want the export your table to.
    Click NEXT.... click Finish.

    That's all. Now you can select each table from you access data base then select
    Export.......
    ODBC databases ()
    now you can select the data source you created then click OK

    return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

    Hope this is helpful


    Worked a treat first time. Tried to find the DTS tool but couldn't be bothered to wait ages for a rather large download so followed these steps and worked.
    Only snag I hit is, when I used SQL Server ODBC connection my SQL Server then went and died & I had to restart the process using Management Tools. Recreated the ODBC connection with SQL Native Client instead & it was perfectly stable throughout the whole process, now I've migrated my entire website into SQL Server Express!
    Just need to tweak a few connection strings in my ASP code and I'm done!
    Should make an article on "How to Migrate to SQL Server from Access 2003 in an Evening" lol.

    Wednesday, February 21, 2007 7:48 PM
  • If it's of any use to you folks, I needed to import CSV data into SQL Server 2005 Express. I opened a query window and typed:

     

    BULK INSERT MyTableName FROM 'C:\Documents And Settings\Me\My Documents\MyFile.csv'

    WITH (FIELDTERMINATOR = ',');

    GO

     

    The field terminator defaults to a tab so I had to change it to a comma for the CSV file. It's probably not as neat as a wizard but it worked for me.

     

    Sunday, July 13, 2008 11:18 PM
  •  Bill Elicson wrote:

    1) Download the Microsoft SQL Server 2005 Express Edition Toolkit (223.9 MB) at:

               http://go.microsoft.com/fwlink/?LinkId=65111

               being sure to select all the components so the weird red X's go away

     

    2) Run  "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"



    I also downloaded the SQL Server Express "SP2"- just to cover all the bases...
    This is such a huge utility, I wonder why it is buried/obscured...

    Anyhow, it works like a champ for converting my xls files into SQL Server Express.


    THX!
    Sunday, August 05, 2007 8:39 PM
  • Hi!

    Check this link for DTS ... to be used to import/export data from MS Sql Server 2005 Express ...

    http://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-server-express-using-dts-wizard/

     

    Cheers

     

    Aleem Latif

     

    Thursday, August 07, 2008 2:13 AM
  •  It looks like I do have a solution for my task.

    1. I created a System Data Source using Adaptive Server Anywhere 9.0 as ODBC driver;
    2. I mapped this DSN as Linked Server in SQL Server 2005 Express;
    3. Now I can run pass-through queries to read any data from Sybase database (at least simple queries - probably I have to change some ASAProv.90 settings to run more complex ones).

    The hard part was mapping Sybase DSN into SQL Server, until I did find a link  http://www.databasejournal.com/features/mssql/article.php/10894_1756161_2/Linking-SQL-Server-to-Heterogeneous-Systems.htm - I had then only to make some minor changes to script provided there.
    Friday, February 13, 2009 9:55 AM
  • IJCalypso wrote:

    BULK INSERT MyTableName FROM 'C:\Documents And Settings\Me\My Documents\MyFile.csv'

    WITH (FIELDTERMINATOR = ',');

    GO

     

     

    Thanks! This worked perfectly.

     

    Saturday, March 27, 2010 11:16 AM

All replies

  • SQL Server 2005 Express Manager does not offer Import/Export functionality as it is dependent on SQL Server Integration Services (SSIS) and SQL Server Agent.  You will need to optain the full version of SQL Server Management Studio.

    Peter

    Monday, December 05, 2005 5:41 PM
  • You can you other means to move data in and out of SQL Express that don't rely on SSIS. BCP is one such tool, you can find more information about BCP at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

    If you are working with data in Access, you can use the functionality of Access to directly export the data into SQL Server. Most other programs can generate data in a format that BCP can accept.

    Mike
    Monday, December 05, 2005 6:29 PM
  •  

    I have to say, I have been developing for many years.  This time I am truely disappointed in MS.  I avoid say this kind of thing, and come to MS's defense almost always.  Never bit into the hand that feeds you (karma-ish).

    How am I supposed to learn this great technology between jobs, or when I work for a cheap start up company.  

    We (your users) can not easily import data into this new version of SQL Server.  If you are going to put it out there as education; at least make it useful.

    I just don't get it.  I appologize for sounding disenchanted; but I have become accustom to being able to depend on MS products atleast being usable.  For completeness sake maybe I am missing something.  And do not say bcp, command lines went out years ago, that iss what Microsoft is founded on.

    Enlighten me please; I fear I have just taken the first step to becoming one of them, the na sayers.  Prove me wrong.  After all that expression of opinion, I hope you find it heartfelt and consructive.

    I look forward to your response.  Please tell me there is a quick windows orienent approach, not some prehistoric utility.

    MKruz

    Monday, April 03, 2006 2:13 AM
  • Michael,

    Agree with you - what a big disappointment. I'm sure that there are many of us who have data that we would like to bring into a SQL Server Express table and work with - it's natural form being a text or Excel base that comes from a bank or some other source. All  the functionality in the world is of little use  if the basic data import is not there.

    Bill Sullivan

    Tuesday, April 18, 2006 9:09 PM
  • I totally agree, SQL 2005 Express is useless if you can't import/export data from a GUI. I'll move back to SQL2000 and DTS until it is fixed.
    Tuesday, April 25, 2006 6:19 PM
  • I really want to find out what the missing feature is, there was no GUI for importing excel files in MSDE and there is not one in Express, which is the equivalent SKU.

    You can use the eval version for free to eval features, or if it is just for development then the dev edition is < 50 USD.

    Friday, April 28, 2006 5:46 AM
  • Yeah, after all the hoopla surrounding the Visual Studio release man I'm bummed out about this. Guess there always has to be a catch. I was excited, I had a program idea mulling in my head for a few years and with Visual Studio I saw a chance to bring it to life.

    But for my idea to come to life I need access to Access databases! (Isn't that why it's called "Access"?..) Oh well, as this n00by knows, if there is a will there is a way! Hope I don't bump into my much of this 'fine print' on my travels...

    Luke

     

    Friday, May 05, 2006 10:23 PM
  • I disagree that command lines went out years ago.  They are still the most expedient way to accomplish a whole host of tasks, and I use them daily.

    That said, my 30-second review of the bcp link does suggest that importing data into a table for SQL Express may be more complicated than just getting it into CSV format.  I'll reserve judgment until I've actually given it a try.
    Tuesday, May 09, 2006 8:28 PM
  • There is a company that makes a whole bunch of db converters.  I just used one of them to convert from Access to MS SQL Express:
    http://www.convert-in.com/acc2mss.htm

    They have a demo that will convert the first five records from each of your tables if you'd like to try it out before you buy.
    Thursday, May 18, 2006 7:44 PM
  • If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

    Before you start exporting make sure you have a data base in SQL where you want to export to.

    Open your data base in access

    Right click on the table you want to export

    Select export

    A dialog box opens...

    at the bottom of this dialog box there is a "Save as Type" drop down box

    select it and scroll to the bottom you will find "ODBC Databases () "

    select it

    Give your table a new name, or leave as is, if it that suits you.... Click OK

    A Select Data Source dialog box opens..... Click the "New" button

    A create new data source dialog opens

    Scroll to the bottom of that box and select SQL Server

    click NEXT

    give your data source a name...... what ever you like.

    click NEXT.... click FINISH

    a 'Create New Data Source to SQL Sever' dialog box opens

    Give your connection a description

    select or type the name of your server in the Server drop down box

    Click NEXT...... Click NEXT......

     

    check the 'change the default data base to' check box

    select the data base where you want the export your table to.

    Click NEXT.... click Finish.

    That's all. Now you can select each table from you access data base then select

    Export.......

    ODBC databases ()

    now you can select the data source you created then click OK

    return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

     

    Hope this is helpful

    • Proposed as answer by MGSeggerman Tuesday, September 22, 2009 8:14 PM
    • Marked as answer by Kalman TothEditor Monday, August 02, 2010 9:29 PM
    Friday, May 19, 2006 2:08 AM
  • Hello Everyone,

         Just after reading this form, I tried out some import/ export operation like below

    SQL 2000 - SQL 2005 Express (Import/Export)

    Excel - SQL 2005 Express (Import/Export)

    It worked out very fine. I just used the DTS in SQL 2000 to do this. Any comments on this.

    Thanks

    Swami

    Friday, May 19, 2006 8:50 PM
  • Hello,
    Try:
    C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
    Worked for me!
    Sunday, May 21, 2006 5:24 PM
  •  Wolfey305 wrote:

    If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

    Before you start exporting make sure you have a data base in SQL where you want to export to.

    Open your data base in access

    Right click on the table you want to export

    Select export

    A dialog box opens...

    at the bottom of this dialog box there is a "Save as Type" drop down box

    select it and scroll to the bottom you will find "ODBC Databases () "

    select it

    Give your table a new name, or leave as is, if it that suits you.... Click OK

    A Select Data Source dialog box opens..... Click the "New" button

    A create new data source dialog opens

    Scroll to the bottom of that box and select SQL Server

    click NEXT

    give your data source a name...... what ever you like.

    click NEXT.... click FINISH

    a 'Create New Data Source to SQL Sever' dialog box opens

    Give your connection a description

    select or type the name of your server in the Server drop down box

    Click NEXT...... Click NEXT......

     

    check the 'change the default data base to' check box

    select the data base where you want the export your table to.

    Click NEXT.... click Finish.

    That's all. Now you can select each table from you access data base then select

    Export.......

    ODBC databases ()

    now you can select the data source you created then click OK

    return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

     

    Hope this is helpful

    If COnnection Failed..what I want to do?

     

    • Proposed as answer by Fordaus Monday, March 02, 2009 11:04 PM
    Thursday, May 25, 2006 10:26 AM
  • Hi swami,

    Could you pls give me more info as to how to run DTS.

    I have installed Sql server 2005 Ex Ed.
    Is it possible to install only the DTS from SQL 2000.

    Tnx

    Monday, May 29, 2006 4:48 PM
  • I've posted this article, hope you find it helpful.

    http://forums.microsoft.com/msdn/ShowPost.aspx?postid=448531&siteid=1

     

    Saturday, June 03, 2006 5:40 PM
  • I think Linked Server functionality is available in SQL Server Express.  I suggest you look in books online under OLE DB Provider for Jet. 

    EXEC sp_addlinkedserver EXCEL,
         'Jet 4.0',
         'Microsoft.Jet.OLEDB.4.0',
         'c:\data\MySheet.xls',
          NULL,
         'Excel 5.0;'

    GO

    You can then select from the Excel table using SQL code as is needed do an insert into a sql table.

    SELECT *
    FROM EXCEL...SalesData
    GO
    Use "sp_dropserver" to drop the link when done.
    Wednesday, June 14, 2006 5:57 PM
  • One thing everyone should know, this may not be helpful with many tables or a lot of data -- but it does let you open a table and cut and PASTE your data in -- as in from Excel.  Actually a tab-delimited text file works best.  I only have 5 tables with less than a thousand rows each which I why this worked for me in a pinch.
    Wednesday, June 14, 2006 9:08 PM
  • Thanks to janesim on 05-21-2006 !  This is EXACTLY what I needed.
    Monday, June 19, 2006 3:38 PM
  • I am switching back to MySQL...
    Thursday, July 13, 2006 3:12 AM
  • I tried the above from Access 2003, and I get as far as naming the table.  The new datasource dialog box never opens, it simply returns me to my main database screen.  Any ideas?

     

    Thanks

     

     

    Wednesday, July 26, 2006 2:09 PM
  • I read your article.  Does this solution depend on having SQL Server 2000?  I don't have a previous version.  There is no DTSWIZ.exe on my system.  Is there any other way to get this file?

     

    Thanks

    Wednesday, July 26, 2006 2:13 PM
  • You can use the export tool from Acces, SQL Server migration assistant

    You have first to create an ODBC link to your SQL database, go in administrative tools, ODBC Connections, new system connection, for the SQL server, type in yourServerName\SQLEXPRESS. Then follow the migration tool menus and it's done in one shot !

    Good luck

    Thursday, August 03, 2006 1:27 PM
  • Thanks JaneSim for the tip.

    Microsoft, why do you hide the DTS import/export functionality?  without a link to this easy to use wizard, SQL 2005 Express is not suitable as an evaluation tool.

    In my business, we are evaluating the feature set of SQL express in conjunction with the full SQL.  I was almost ready to recommend to my clients that MySQL with full a SQL Server 2005 hub would be a better alternative due to the limitations of SQL express (Lack of DTS, SQL Agent, Replication Subscription).

    Come on Microsoft, try to keep your supporters in the good books!

    Wednesday, August 09, 2006 5:14 AM
  • I did not find this subdirectory, I was good up to \90 then the rest is not present.
    Friday, August 11, 2006 1:46 PM
  • Thanks, that fixed my problem.

    ---
    FREEDOM!
    Wednesday, August 23, 2006 7:23 PM
  • I had the same problem until I installed SP1, from http://msdn.microsoft.com/vstudio/express/sql/download/

    The DTS Wizard works fine after this install

    Monday, August 28, 2006 4:56 AM
  • There are some people here who do not understand what SQL Server Express is. First of all lets see what it is not:

    1. It is NOT a evaluation edition of SQL Server 2005. You can download the Evaluation Edition from MSDN and this comes with all the bells and whistles but will expire after 180 days of eval. It also cannot use more than 1Gb of RAM and can only use 1 processor.

    2. It is NOT a development edition. SQL Server 2005 Development Edition is cheap and comes with all the bells and whistles so you can develop a database solution for your clients. You may not use development edition in a production environment.

    3. It is NOT a training edition. Although you can learn something about SQL Server 2005 using Express you cannot learn everything.

    4. It is NOT a replacement for SQL 2000 and DTS, only for MSDE. SQL Server 2005 is a replacement for SQL Server 2000, SQL Server Integration Services is a replacement for DTS and SQL Server Express is a replacement for MSDE only.

    5. It is NOT a replacement for MS Access.

    SQL Server Express is a free to use & free to distribute database engine. Thats All.

    Development Senario

    You have written a nice little application using Visual Basic. The original design used an Access database file in the back end. You decide you much prefer the power, speed, security and functionality that a SQL Server database can provide. However your application will become too expensive for what its worth if your customers have to buy SQL Server with your app.

    Well your customers do not have to buy SQL Server. You can freely distribute SQL Server Express with your distribution and for no more cost, your app is running on a powerfull back end and your customers think you are a hero.

    In order to design and develop your distribution database you buy SQL Server 2005 Development Edition and you have all the power you need to develop your database for free distribution.

     

    Back to the question, how do you import data into SQL Server express. This depends on what software you have on your machine as there are a few options.

    1. Create a new ODBC Data Source which points to your SQL Express Instance. Default name is [MACHINE]\SQLEXPRESS

    Use BCP (Bulk Copy Program BCP.exe)
    or If you have MS Access and your source is Access, export from Access to your new ODBC Data Source.
    or If you have MS Access and your source is Excel, import from Access and export to ODBC.
    or If you have MS Access and your source is Text, import from Access and export to ODBC.
    or If you have SQL Server 2000, use DTS as usual.
    or If you have SQL Server 2005, use SSIS.
    or If you have Visual Basic, write an app to do it.
    or If you have the Internet and a Credit Card, buy a 3rd party app to do it.

    There are plenty of options.

    What Not To Do
    "I just got a brilliant Db engine for absolutely no money at all, but I am really upset that it doesn't have any fancy wizards for performing tasks I should know how to do myself."

    Don't moan. Learn how to use it. If you don't want to learn, but rather use fancy wizards, then buy SQL Server 2005.

    • Proposed as answer by M.Glenn Monday, October 05, 2009 1:59 AM
    • Marked as answer by Kalman TothEditor Tuesday, October 09, 2012 6:03 PM
    Monday, August 28, 2006 12:34 PM
  • Great thread discussion! I also did not find the path C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe. Following the suggestion, I downloaded SP1 for the basic SQL Server 2005 Express. The problem, I suspect, is which SP1 was being referred to? There are 4 items listed:

    SQL Server 2005 Express Edition

    SQL Server Management Studio Express

    SQL Server 2005 Express Edition with Advanced Services

    SQL Server 2005 Express Edition Toolkit

    I have installed the first 2 because the descriptions for the last 2 did not mention (to my untrained eye) anything about DTS. So which one do I really need to get the DTSWizard?

    Thursday, September 14, 2006 4:25 PM
  • Hi Janesim

    i have same problem with me, i have sql server 2000, i downloaded Microsoft SQL Server Management Studio Express.

    and this do not have any import / export utility

    i read your post, and tried to find out the same file "C:\Program files\Microsoft SQL Server\90\......\DTS.....exe"

    but i could not found it on my system

    i have folder named 90 but this file does not exist.

    do you have any idea or solutions.

     

    thanks & regards

     

    ashok sharma

    anandcomputers@rediffmail.com

    Saturday, September 16, 2006 10:07 AM
  • I have SQL Server 2005 Express, SP1, and the DTS subdirectory doesn't exist.
    Monday, September 18, 2006 4:33 AM
  • I have SQL Server Express, not sure which SP, but the DTS subdirectory doesn't exist. I need a script version of my SQL Express database. How do I get one?
    Monday, September 18, 2006 4:36 AM
  • I have SQL Express as well, not sure what SP, and I can't find the DTS subdirectory. I need a script version of the database. How can I get one?
    Monday, September 18, 2006 4:41 AM
  • No need to get short.  Everyone's learning the new set-up.  Besides, if MS wants to compete with other free database solutions, moaning is something they'll have to get used to.  The import/export should really be offered immediately - it's ultra-basic DBMS functionality.

    If you don't have the DTS folder in your 90 folder, download and install the Toolkit SP1:

    http://msdn.microsoft.com/vstudio/express/sql/download/ (SQL Express download page)
    http://go.microsoft.com/fwlink/?LinkId=65111 (direct link)

    I installed Express 2005 from scratch (SP1) and the folder wasn't there.  Downloaded the toolkit, did complete installation and found the folder immediately afterwards.

    It would be nice to have a shortcut created in the Start Menu, but no complaints as long as the tool is there.
    Wednesday, September 20, 2006 6:53 PM
  • I think it has to something with the type of install.

    try installing SQL Server Express Advanced Edition (SQLEXPR_ADV.EXE)

    if this does not help


    try intalling SQLEXPR_TOOLKIT.EXE which is the tool kit that might contain DTSWizard.exe executable.. I cannot believe why it should be hidden, searching around internet (googling around: damn should I not say in Microsoft forum?) was easy.

    I installed both of them before scrambling around to find a way to import/export excel data to SQLExpress edition

    I still dont know clearly why the wizard should be hidden!!
    Friday, October 06, 2006 8:09 PM
  • If you have SQL Server 2000 installed, you can export from SQL Server 2005 using SQL Server 2000 DTS Import/Export wizard. The exe file is usually located at C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe.

    Regards,

    Your friendly neighbourhood Rastaman

    Tuesday, October 10, 2006 10:50 AM
  • I have solvd the problem during DTS and import/export that is missing

    Go to this link

    http://www.microsoft.com/downloads/info.aspx?na=46&p=16&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f4%2f4%2fd%2f44dbde61-b385-4fc2-a67d-48053b8f9fad%2fSQLServer2005_DTS.msi

    and install SQLServer2005_DTS.msi 5.0 MB


    I have tryed it to export excel file into SQL Server 2005 express

    Hope this is usefull.


    Best regards

    Ole

    Saturday, October 14, 2006 8:31 AM
  • I installed the evaluation copy of SQL Server 2005 on my desktop in order to evaluate the Management studio.  Our SQL Server Databases are all in SQL Server 2000 and current plans do not call for us to upgrade until late next year.  I really liked the way so much has been integrated into the one package and do enjoy using it.  But I also found that athere are features that seemed to have gotten dropped. 

    I have been using it now for over 120 of its 180 days.  During this time I have been flipping back to 2000 Enterprise Manager to do things that I needed to do fast.  One of these tasks was Import/Export data.  I was very disappointed that this feature was not delivered in Management Studio. 

    I also had problems because we had DTS packages that I also manage and there was no DTS Design tool.  I did search for this and found the tool to download.  Thanks to this thread I also now found out that there is an Import/Export wizard. 

    My question is like so many here on this thrtead - why is it so hard to maintain functionality that is so important to the users of these products.  A basic function like Import/Export should be included right there at the top of supported tasks.

    Monday, November 06, 2006 2:41 PM
  • Tks a lot save my day ... the new DTSWizard.exe is in fact in .. program files\Microsoft SQL Server\90\Binn i add to install SQLEXPR_TOOLKIT.EXE.

     

    Best Regards

    José Xavier (Portugal)

    Monday, November 20, 2006 1:37 PM
  • I had the same problem until I downloaded the tools from http://msdn.microsoft.com/vstudio/express/sql/download/

     

    Good luck

    Sunday, November 26, 2006 12:27 PM
  • Dude,

    You need to chill out.  How the heck you learn something when it is in complete freefall and no guidance anywhere indicating where the import/export feature is.  I have search hi and lo in the help section as well as googling to find where is the import/export feature.  For a good while I literally thought I miss something until I got to this thread.

    BTW, if you think people consider importing/exporting as a learning process, you need to get over your hi ground. 

    I work on SQL 2000/DTS in my everyday job which requires 50 hours a week to make things meet - you think I enjoy "searching" and losing 2-3 hours of my "off" time just to import data?!

     

    Friday, December 08, 2006 8:55 PM
  • I tried this solution and it worked like a charm!!!
    The online part missing is the following code:

    INSERT INTO [YourDataBase].[dbo].[yourTable]
    SELECT *
    FROM EXCEL...SalesData
    GO

    That's it !

    Thanks Spevy for the tip !!!



    Monday, December 11, 2006 6:50 PM
  • I agree. importing and exporting are big item to not have!

     

    Tuesday, December 19, 2006 3:09 PM
  • I get the following error when trying to run the select statement:

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Could not find installable ISAM.".

    Thursday, December 21, 2006 5:14 PM
  • If you go to the object explorer window, and right click on the name of the database.  It will give you the option for TASKS there is where you will be home free.  I just found it myself.  I don't understand why people here cannot understand simple issues.  I am new to SQL Server myself, but it is going.  Hey do you know of syntax/a query to import data to a table?  You are probably looking for the same kind of solution.
    Friday, January 05, 2007 2:14 PM
  • I have exactly the same problem wit Access 2003; more, I have the problem on 2 different machine with Access 2003 installed. I try to import (or connect) data from external datasources but, when I select ODBC datasource as import file type, NO select datasource dialog opens; it simply returns to main database screen.

    I can't found information on a problem like this elsewhere on Internet, so I'm here: i'm quite sure my problem has nothing to do with SQL server 2005 (not installede on one of the two machine) but I'm thinking it can arise from some Windows Update: I'm shure some month ago import dialogs worked fine!!

    Sorry for Posting this!

    I've found the solution just now: the problem derives from NORTON Antiviris 2007 Office Plug-in. If I disable this function Ms Access Odbc Imports will work fine anew. I hope this can be usefull for somebody. Remember that I never had problems before the upgrade of NAV to version 2007 but it seems other NAV Users have.

    Sunday, January 07, 2007 9:02 AM
  • I am able to find it after installing SQREXPRESS_TOOLKIT, and I am able to run the DTSWizard.   It said it was successful, but I can't find imported database under SQL EXPRESS. I have it look it under SQL Server Management Studio Express.
    Wednesday, January 31, 2007 4:23 PM
  •  Wolfey305 wrote:

    If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

    Before you start exporting make sure you have a data base in SQL where you want to export to.
    Open your data base in access
    Right click on the table you want to export
    Select export
    A dialog box opens...
    at the bottom of this dialog box there is a "Save as Type" drop down box
    select it and scroll to the bottom you will find "ODBC Databases () "
    select it
    Give your table a new name, or leave as is, if it that suits you.... Click OK
    A Select Data Source dialog box opens..... Click the "New" button
    A create new data source dialog opens
    Scroll to the bottom of that box and select SQL Server
    click NEXT
    give your data source a name...... what ever you like.
    click NEXT.... click FINISH
    a 'Create New Data Source to SQL Sever' dialog box opens
    Give your connection a description
    select or type the name of your server in the Server drop down box
    Click NEXT...... Click NEXT......

    check the 'change the default data base to' check box
    select the data base where you want the export your table to.
    Click NEXT.... click Finish.

    That's all. Now you can select each table from you access data base then select
    Export.......
    ODBC databases ()
    now you can select the data source you created then click OK

    return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

    Hope this is helpful


    Worked a treat first time. Tried to find the DTS tool but couldn't be bothered to wait ages for a rather large download so followed these steps and worked.
    Only snag I hit is, when I used SQL Server ODBC connection my SQL Server then went and died & I had to restart the process using Management Tools. Recreated the ODBC connection with SQL Native Client instead & it was perfectly stable throughout the whole process, now I've migrated my entire website into SQL Server Express!
    Just need to tweak a few connection strings in my ASP code and I'm done!
    Should make an article on "How to Migrate to SQL Server from Access 2003 in an Evening" lol.

    Wednesday, February 21, 2007 7:48 PM
  • I've used EMS SQL Manager for SQL Server and think that it's an interesting option.

    You can find it in: http://sqlmanager.net/products/mssql/manager/

    Monday, February 26, 2007 4:49 PM
  • I can't believe MS for not allowing i/e of data. It's ridiculous!!!

    How about you tell me this before I make a MS SQL DB with 56 tables with about 300 stored procedures and 20,000 records. Now I'm going to miss a deadline and look like a fucking ***!


    Wednesday, February 28, 2007 3:34 AM
  • What type of data are you trying to import?
    Sunday, March 04, 2007 2:11 AM
  • I'm also trying to use DTS or BCP with SQL Server express sp2. DTS worked for me until it failed in validation - it said something like can't convert unicode string...any troubleshooting tips?

    Otherwise, I'm not a programmer (I'm doing this for some academic research that requires opening and converting a file from the online server to either access/excel/stata) and I don't know how to write the code. What is something you suggest? I tried importing from Excel, but i can only do one table at a time. Access had an error in the middle and froze. I don't really know how to use BCP (is there an easy explanation for it?) Thank you very much.
    Saturday, March 17, 2007 5:40 AM
  • SQL Server 2005 Express Edition Toolkit

     

    You need number 4.

    Tuesday, March 27, 2007 5:49 PM
  • I have make a try and finds that it works for numeric and char data, but not work for date data
    Friday, April 13, 2007 9:35 PM
  • Same for me...was good upto \90..
    Thursday, April 19, 2007 8:20 AM
  • Same here..
    Thursday, April 19, 2007 8:50 AM
  •  This is the second time I am posting this. Listening to people *** and moan about a free tool, it is obvious they did not read this the first time.

     

    There are some people here who do not understand what SQL Server Express is. First of all lets see what it is not:

    1. It is NOT a evaluation edition of SQL Server 2005. You can download the Evaluation Edition from MSDN and this comes with all the bells and whistles but will expire after 180 days of eval. It also cannot use more than 1Gb of RAM and can only use 1 processor.

    2. It is NOT a development edition. SQL Server 2005 Development Edition is cheap and comes with all the bells and whistles so you can develop a database solution for your clients. You may not use development edition in a production environment.

    3. It is NOT a training edition. Although you can learn something about SQL Server 2005 using Express you cannot learn everything.

    4. It is NOT a replacement for SQL 2000 and DTS, only for MSDE. SQL Server 2005 is a replacement for SQL Server 2000, SQL Server Integration Services is a replacement for DTS and SQL Server Express is a replacement for MSDE only.

    5. It is NOT a replacement for MS Access.

    SQL Server Express is a free to use & free to distribute database engine. Thats All.

    Development Senario

    You have written a nice little application using Visual Basic. The original design used an Access database file in the back end. You decide you much prefer the power, speed, security and functionality that a SQL Server database can provide. However your application will become too expensive for what its worth if your customers have to buy SQL Server with your app.

    Well your customers do not have to buy SQL Server. You can freely distribute SQL Server Express with your distribution and for no more cost, your app is running on a powerfull back end and your customers think you are a hero.

    In order to design and develop your distribution database you buy SQL Server 2005 Development Edition and you have all the power you need to develop your database for free distribution.

     

    Back to the question, how do you import data into SQL Server express. This depends on what software you have on your machine as there are a few options.

    1. Create a new ODBC Data Source which points to your SQL Express Instance. Default name is [MACHINE]\SQLEXPRESS

    Use BCP (Bulk Copy Program BCP.exe)
    or If you have MS Access and your source is Access, export from Access to your new ODBC Data Source.
    or If you have MS Access and your source is Excel, import from Access and export to ODBC.
    or If you have MS Access and your source is Text, import from Access and export to ODBC.
    or If you have SQL Server 2000, use DTS as usual.
    or If you have SQL Server 2005, use SSIS.
    or If you have Visual Basic, write an app to do it.
    or If you have the Internet and a Credit Card, buy a 3rd party app to do it.

    There are plenty of options.

    What Not To Do
    "I just got a brilliant Db engine for absolutely no money at all, but I am really upset that it doesn't have any fancy wizards for performing tasks I should know how to do myself."

    Don't moan. Learn how to use it. If you don't want to learn, but rather use fancy wizards, then buy SQL Server 2005.

    Thursday, April 19, 2007 12:30 PM
  • Beautifully spoken R2 DJ!
    Thursday, April 19, 2007 7:04 PM
  • I have installed SQL Express toolkit sp2 as well as the MSI recommended in this post and I still don't have DTS in the 90 subdirectory?????

     

    What a nightmare.  Anyone figure this thing out for real?

    Friday, April 20, 2007 5:09 PM
  •  R2 DJ wrote:

     This is the second time I am posting this. Listening to people *** and moan about a free tool, it is obvious they did not read this the first time.

    2. It is NOT a development edition. SQL Server 2005 Development Edition is cheap and comes with all the bells and whistles so you

     

    So, in other words, now Microsoft is going to start charging us to do development on their platform....

    Friday, April 20, 2007 5:13 PM
  • 1) Download the Microsoft SQL Server 2005 Express Edition Toolkit (223.9 MB) at:

               http://go.microsoft.com/fwlink/?LinkId=65111

               being sure to select all the components so the weird red X's go away

     

    2) Run  "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"

    Saturday, April 21, 2007 4:22 PM
  • In addition to all the ideas previously posted.... you can also use Microsoft SSMA (SQL Server Migration Assistant) for Access...  there's an SSMA for Access, Oracle and Sybase as well.  I 've used the the Access version with no problems..

     

    http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20Migration&DisplayLang=en

     

    you'll need to download visual J# redist... no biggie...  tiny downloads as opposed to the toolkit

     

    Hope that helps

     

    Wednesday, April 25, 2007 8:17 PM
  • Bill,

    It would be nice if SQL Server were as easy to use as Ms Access too, but is not, however there is a very easy work around, just create an ODBC DSN to your SQL Server Database.  Once the connection is there you can use Access to import Excel worksheets, create tables and then export each table you want into SQL Express.  If I can do it anyone can.

    Wednesday, May 02, 2007 2:11 AM
  • I have installed the toolkit and no red x's were checked on components, so i pretty much installed everything.  I still do not see the DTS folder under C:\Program Files\Microsoft SQL Server\90.  Is there anything else I am missing here ?
    Wednesday, May 02, 2007 9:21 PM
  • I needed to copy my Database so I could use it elsewhere as well and all I did was:

    -Right click on your Database and go to "tasks".

     

    -Select "detatch".

     

    -Go to the folder that has your databases (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  by default I believe)

     

    -Simply copy the .mdf and .ldf files (Im not sure if the .ldf is really necessary so I copied it just in case) and paste them on another computer or wherever you need them.

     

    -Once you have pasted the Database wherever you need it, go into your SQL Express and right click on the Database folder and select "attach"

     

    Thats what I did in order to export my Database, hope this helps.

    Friday, May 25, 2007 7:47 AM
  • Thanks fionajwc  and Janesim. You guys rock!!!. You saved me from a lot of headaches.
    Tuesday, June 12, 2007 4:47 PM
  • Excellent thread!

     

    I downloaded the Toolkit and the import/export wizard then enabled me to convert my Access database to SQL. I can now convert my application database from Access and run a test-bed on my home PC using SQL Server Express. Marvellous.

    Wednesday, June 13, 2007 10:16 AM
  • Once the DTS Package is saved, any idea where it is on the hdd. All I get is that it is part of SSIS. SSIS  is not part of the SSE Edition. Tried searching *.dtsx on the HDD but nothing. Looked in the Management Studio but does not exist.
    Tuesday, July 24, 2007 10:00 PM
  •  Bill Elicson wrote:

    1) Download the Microsoft SQL Server 2005 Express Edition Toolkit (223.9 MB) at:

               http://go.microsoft.com/fwlink/?LinkId=65111

               being sure to select all the components so the weird red X's go away

     

    2) Run  "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"



    I also downloaded the SQL Server Express "SP2"- just to cover all the bases...
    This is such a huge utility, I wonder why it is buried/obscured...

    Anyhow, it works like a champ for converting my xls files into SQL Server Express.


    THX!
    Sunday, August 05, 2007 8:39 PM
  • Hi,

     

    Can any one tell me how to do bulk copy from a data file to the table partition. Please give me the syntax also.

    Wednesday, August 29, 2007 6:24 AM
  • Just do what janesim said. That worked like a charm. It was easy and everything. Just like the old days!

    C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

    I don't know why MS decided to hide this tool from its users.
    Tuesday, October 02, 2007 3:20 PM
  • this is superb.

    it works perfect for import/export

    Thursday, October 25, 2007 2:37 PM
  • Thanks a bunch.  I spent 2-days trying to find inport function.

     

    Thursday, October 25, 2007 7:43 PM
  •  

    I had trouble to use the 'Linked server' function.
    Thursday, November 01, 2007 12:28 PM
  • Like someone posted. The DTSWizard.exe is out there. I'm not sure if it installed with sql express or the tools. Just start it and your in.

    Was @: C:\Program Files\Microsoft SQL Server\90\DTS\Binn\

    You can probably register it as an external tool in one of the managers or tools.
    Tuesday, December 04, 2007 2:39 AM
  •  

    Thanks for this!!!!


    Saved me a lot of time and frustration :0)

    Friday, December 07, 2007 6:36 PM
  • Rubbish...

     

    This folder DOES NOT exist when you install Microsoft SQL Server Management Studio Express 9.00.3042.00  Where did you really find the DTSWizard.exe ????

     

    Tuesday, December 11, 2007 12:53 PM
  • Thanks

    Exporting MS Access tables to Sql 2005 Epress  using the ODBC Database connectiion is simple and works just fine

     

    Monday, December 17, 2007 5:07 PM
  • Not sure if you all still care but there is a super easy way to deal with the Export from Access to SQL Server issue.  I started trying to Import Access into SQL Server myself and found all the issues and difficulties stated above.

     

    Do it from the Access side.  Open you Access database, from the menu select Tools, then Database Utilities and then Upsize Wizard.  Just follow the wizard.  Done no pain.  It works great.

     

    Hope that helps.

    Tuesday, December 18, 2007 1:36 PM
  • IMHO, the missing feature is the ability to easily import data graphically from Excel. Ideally, we could grab a column or range of data from Excel and create or add to a SQL Server table. It seems starnge to have created such a range of relatively easy-to-use tools without the ability to wean users away from Excel based databases and into SQL Server.

     

    Tuesday, January 22, 2008 3:33 PM
  • Thanks for your comments.

     

    Tuesday, January 29, 2008 10:03 PM
  •  

    THIS IS REALLY WORK GREAT.

    USEFUL ONE

    Saturday, March 01, 2008 7:59 AM
  •  sc0tsman wrote:

    Rubbish...

     

    This folder DOES NOT exist when you install Microsoft SQL Server Management Studio Express 9.00.3042.00  Where did you really find the DTSWizard.exe ????

     




    i don't have it either, please help
    Friday, March 14, 2008 12:01 AM
  • For the past freaking four hours I've been trying to get a little CSV file imported into this damn SQL Express and I'm still not able to. I cannot efing believe that they would not include a simple importing and exporting functionality into this ***. I've downloaded about a gig of *** so far and still no DTS folder available. Now I'm downloading the Advanced edition to see if I get the simplest form of functionality out of this junk. absolutely FUC*ING disappointed.
    Wednesday, March 26, 2008 7:15 AM
  • GETTING CONNECTION FAILED!!! ERROR

    JUST make a table with similar column names in sql server 2005 management studio

    select all the contents of the table in access by ctrl+A

    copy them

    make sure your newly created sql table dosen't contains any row

    select the table and paste

    if any problems reply!!!!!!

     

    Thursday, March 27, 2008 12:02 PM
  •  

    the packages on this site are very good.  the conversions work well and you can convert from just about any database style format, to any other database style format.  Am using this to migrate information from both MS SQL server 2005 and Access to MySQL and it works great, with plenty of options for the transfer of information. 

     

    unlike most people here, I am a MicroSoft HATER, the strangle hold that MS SQL Express puts on you is just another example of MS wanting total control and all of your money.  OPEN SOURCE IS GREAT!!!!

     

    God Bless Everyone

    Hope this helps.

    Thursday, March 27, 2008 5:47 PM
  • Does this imply that it is O.K. to redistribute BCP.EXE for use with SQL Server Express?  We have need for such a solution for a few of our clients.

     

    Katy Lynn McCullough-Leonard

    Thursday, March 27, 2008 7:24 PM
  • Sahboo, how do you reconcile hating Microsoft with God Bless Everyone? You're not obliged to use SQL Express, it's free and even if you do use it, you can uninstall any time you like. I don't understand how that shows Microsoft to want total control and all of your money. I am currently employed by IBM, by the way.
    Friday, March 28, 2008 8:26 AM
  • Hi,

    I installed successfuly the Toolkit and the DTS wizard works fine.
    However I cannot see all the sql servers in the Server Name box. I see only the 2005 Express in my laptop and the Sql 2000 in my desktop and I cannot see the sql 2000 in my hosting company although I can connect to it successfuly. As a result I can use the DTS only between my laptop and my desktop and not with my hosting server....
    Any ideas??
    It's really strange..
    Friday, May 02, 2008 5:37 PM
  • That works!!!!!

     

    Insteat of importing, you just shove the data right up express's b***. Thank you. You have solved a big problem. The rest of you, stop whining and follow the instructions.

     

    Thanks again,

     

    Happy Trails

    Tuesday, May 06, 2008 1:04 AM
  • Thanks Janesim. This is what I am looking for.

    Wednesday, May 21, 2008 6:24 AM
  • For importing data in SQL 2005 Express from another instance of SQL server (on another machine) :

     

    Link the server (Server Object/Linked Servers) from wich you like to import data.

    Make an INSERT query to insert the data in your own SQL instance.

     

    EG.

    INSERT INTO [yourdbname].[dbo].[tablename]

    select * from linkedserver.externaldbname.dbo.tablename

    Tuesday, June 17, 2008 10:36 AM
  • Actually, this orked well. And what is such a drag is that I had discovered thos route once before, but with data updates coming only once every 3 or 4 months, you tend to forget how you did something.  Since the last time I have upgraded from Office 2003 to 2007, and there is a nice little feature in Access 07 that lets you save your steps.  But to make sure I have this, I copied it to a word doc and have it in my "things to know" folder!!

     

    Uh, but let me say, that as a MS based developer for the past 12 years, this is a drag having to jump through so many darn hoops to transform your data without having 2 major engines sitting on your machine.

     

    Tuesday, July 01, 2008 5:58 PM
  • If it's of any use to you folks, I needed to import CSV data into SQL Server 2005 Express. I opened a query window and typed:

     

    BULK INSERT MyTableName FROM 'C:\Documents And Settings\Me\My Documents\MyFile.csv'

    WITH (FIELDTERMINATOR = ',');

    GO

     

    The field terminator defaults to a tab so I had to change it to a comma for the CSV file. It's probably not as neat as a wizard but it worked for me.

     

    Sunday, July 13, 2008 11:18 PM
  • Sorry, should have mentioned that I opened a query window in SQL Server Management Studio Express.

    Monday, July 14, 2008 10:09 PM
  • Hi!

    Check this link for DTS ... to be used to import/export data from MS Sql Server 2005 Express ...

    http://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-server-express-using-dts-wizard/

     

    Cheers

     

    Aleem Latif

     

    Thursday, August 07, 2008 2:13 AM
  •  

    Fentastic Information.

     

    Thanks

     

    Harish

    Monday, August 25, 2008 7:05 AM
  • YES!  This is what I was looking for.  Thanks janesim!

    Friday, September 26, 2008 3:52 PM
  • I don't know if this has been said already (it's such a huge thread) but here is an option to import data from excel into a SQL Server table

    What you do is open your excel sheet with the data of ONE table.
    Example: Table customer: (sorry I'm lazy about screenshots atm)
    [CustomerId] [Name] [Email]
    [1] [Paul Johnson] [paul.johnson@mail.com]
    [2] [Rob De Niro] [rob.de.niro@mail.com]
    [3] [John Smith] [john.smit@mail.com]

    What you then do is copy ONLY the rows with the data and make sure that the layout of your excel sheet exactly matches the layout of the table in SQL Server. So in SQL Server you first have CustomerId, then Name and then Email

    Now you open the table in SQL Server Management Studio Express (free download)
    Then you select the whole 'new record' row in this 'Open Table' window.

    Make sure the entire row is selected else this won't work!!

    Then rightclick on the selected row and select paste.

    Let the magic do the work!


    Hope this helps anyone out!

    Wednesday, October 01, 2008 7:05 PM
  • Wolfey305

    I tried the proposed solution and works ok. Thank you master

    Friday, October 24, 2008 6:34 PM
  • Thanks to Janesim on 05-21-2006 !

    Exactly what I needed too and works like a charm !

     

     

    Tuesday, January 27, 2009 11:53 AM
  • cool. thanks.
    P.K.Rao
    Tuesday, January 27, 2009 2:41 PM
  • Hi

    My first posting here!
    I browsed through this thread, and it looks like the whole discussion is about EXPORTING some external data into SQL Server Express. My task will be somewhat different - I need periodically READ data from external data sources (p.e. some from dbf-tables on some network resource, or from ODBC datasource based on SyBase database and defined using SQL Anywhere), and UPDATE according tables in my SQL database (Add & Delete & Update). The goal is to create a general data source for some specified reports, or for selected data collections from otherwise restricted network resources. Is it possible at all with SQL Server Express? When not, then is it possible with SQL Server? And when either of them is possible, what components will I need for this?

    (With Access, I would use pass-through queries)


    Thanks in advance
    Arvi Laanemets
    Wednesday, February 11, 2009 10:58 AM
  •  It looks like I do have a solution for my task.

    1. I created a System Data Source using Adaptive Server Anywhere 9.0 as ODBC driver;
    2. I mapped this DSN as Linked Server in SQL Server 2005 Express;
    3. Now I can run pass-through queries to read any data from Sybase database (at least simple queries - probably I have to change some ASAProv.90 settings to run more complex ones).

    The hard part was mapping Sybase DSN into SQL Server, until I did find a link  http://www.databasejournal.com/features/mssql/article.php/10894_1756161_2/Linking-SQL-Server-to-Heterogeneous-Systems.htm - I had then only to make some minor changes to script provided there.
    Friday, February 13, 2009 9:55 AM
  • If Connection Failed..

    Try adding \sqlexpress to the end of your server name. Worked for me, hope this helps someone else!
    Monday, March 02, 2009 11:02 PM
  • This is my solution.

            private void button1_Click(object sender, EventArgs e)    
            {    
                //Data Base    
                //Define Database connection string    
                string ConStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=
                'C:\mydb.mdf';Integrated Security=True;User Instance=True"
    ;    
        
                SqlConnection SqlCon=new SqlConnection();    
                SqlCon.ConnectionString=ConStr;    
                SqlCommand InsertCom=new SqlCommand();    
                InsertCom.Connection=SqlCon;    
                SqlParameter SqlParam;    
        
                //Excel    
                //Define Excel file path    
                string Path = @"c:\myfile.xls";    
                //Define columns count    
                int ColCount = 33;    
        
                string Query = "INSERT INTO mytable VALUES(@p1";    
                for (int i = 2; i <= ColCount; i++)    
                {    
                    Query += ",@p" + Convert.ToString(i);    
                }    
                Query += ")";    
        
                Excel.ApplicationClass app = new Excel.ApplicationClass();    
                Excel.Workbook workBook = app.Workbooks.Open(Path, 0, true,
                5, 
    """"true, Excel.XlPlatform.xlWindows,
                
    "\t"falsefalse, 0, true, 1, 0);    
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;    
        
                try    
                {    
                    for (int i = 2; i < workSheet.Rows.Count; i++)    
                    {    
                        if (((Excel.Range)workSheet.Cells[i, 1]).Value2 != null)    
                        {    
                            InsertCom.Parameters.Clear();    
                            for (int j = 1; j <= ColCount; j++)    
                            {    
                                if (((Excel.Range)workSheet.Cells[i, j]).Value2 != null)    
                                {    
                                    SqlParam = new SqlParameter("@p" + j.ToString(),
                                     ((Excel.Range)workSheet.Cells[i, j]).Value2);    
                                    InsertCom.Parameters.Add(SqlParam);    
                                }    
                                else    
                                {    
                                    SqlParam = new SqlParameter("@p" + j.ToString(), 
                                     string.Empty);    
                                    InsertCom.Parameters.Add(SqlParam);    
                                }    
                            }    
                            InsertCom.CommandText = Query;    
                            SqlCon.Open();    
                            int nl = int.Parse(InsertCom.ExecuteNonQuery().ToString());    
                            SqlCon.Close();    
                        }    
                        else    
                            break;    
                    }    
                }    
                catch (Exception ex)    
                {    
                    app.Quit();    
                    MessageBox.Show(ex.Message, "Error");    
                }    
            }    
     
    • Proposed as answer by Muhammad Badr Thursday, March 12, 2009 11:47 PM
    Thursday, March 12, 2009 11:46 PM
  • Hello folks!
    I have installed SQL Server 2005 Express and SQL Server Management Studio Express. Everything looks ok and works fine, but I can't find the Data Import/Export Wizard.  Can anyone tell me how to Import/Export data?

    Thank you!!!

    Sunday, March 29, 2009 11:56 PM
  • thanks a lot for you. it is most useful for me. thank you.
    Tuesday, April 28, 2009 6:16 AM
  • This is the best response. You can link the dts wizard to sql server express by clicking Tools >>> External Tools and pasting
    C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe, (or wherever it's located on your pc), so that you have it as part of the app from then on.
    Saturday, September 12, 2009 7:36 PM
  • This works like a charm - thanks!
    Tuesday, September 22, 2009 8:15 PM
  • Many times you can import data from a comma separate or tab delineated file by copying and pasting the contents into an SQL Server Express table. You just have to make sure the number of columns matches that of the copied data and that the values are within the bounds of the data types listed in your table design.

    Friday, October 09, 2009 9:02 PM
  • Looking for clarification.

    I have seen many claims that if you download Microsoft SQL Server 2005 Express Edition Toolkit and checkmark all components for install (eliminate the X-s when installing), and you run (you can create a desktop shortcut for it by right clicking ) C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe, then you have a working version of the SQL Server Import/Export Wizard.  This is the usual location of the wizard in the non-express editions of SQL Server.

    Anybody ever followed the method above on a brand-new computer and had the SSIS import/export wizard working OK? 

    Anybody could not get it working?

    Thanks.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Saturday, November 21, 2009 11:11 PM
    Answerer
  • In addition to all the ideas previously posted.... you can also use Microsoft SSMA (SQL Server Migration Assistant) for Access...  there's an SSMA for Access, Oracle and Sybase as well.  I 've used the the Access version with no problems..

     

    http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20Migration&DisplayLang=en

     

    you'll need to download visual J# redist... no biggie...  tiny downloads as opposed to the toolkit

     

    Hope that helps

     


    This is looking like it'll work - you deserve some kind of medal or knighthood.

    Tried every other avenue - installed SP3 for SQL Express. Installed Advanced version. Installed toolkit. Installed more service pack. At no point is dtswizard.exe ever to be found, and 9 x out of 10 it'll refuse to install claiming there's no change. Excruciating - I'm an adult - *I'LL* decide if i want to install over the top... sheesh. Couldn't use the "pushing out from Access" approach as Access isn't / can't be easily installed on the server.

    Hopefully this'll now work.
    Tuesday, March 16, 2010 11:06 AM
  • IJCalypso wrote:

    BULK INSERT MyTableName FROM 'C:\Documents And Settings\Me\My Documents\MyFile.csv'

    WITH (FIELDTERMINATOR = ',');

    GO

     

     

    Thanks! This worked perfectly.

     

    Saturday, March 27, 2010 11:16 AM
  • I was looking for migration alternatives (I'm having problems with DTSwizard) and I found this great tool:

    SQL Server Migration Assistant for Access (SSMA)

    I read about it in this post:

    http://gerardoramosun.wordpress.com/2007/04/28/migracion-de-datos-desde-access-a-sql-server-2005/

    It's in Spanish but I think it's great, very clear.

    Hope it can help...

    Monday, August 02, 2010 8:42 PM
  • Monday, August 02, 2010 9:25 PM
    Answerer
  • thanks for the post, was useful...
    Monday, January 10, 2011 3:58 PM
  • HI Wolfey305 THANK YOU FOR PROVIDING SUCH A USEFUL DETAIL OF IMPORTING DATABASE TO SQL 2005 EXPRESS. I HAVE FOLLOWED THESE MENTIONED STEPS FOR TRANSFERRING MY HUGE ACCESS DATABASE TO SQL 2005 EXPRESS WITH LITTLE MODIFICATION THAT "IN CREATE NEW DATA SOURCE TO SQL SERVER DIALOGUE BOX, WHEN THEY ASK ' WHICH SQL SERVER DO YOU WANT TO CONNECT TO?' HAS MANY SERVER DROP DOWN LIST. BUT WHEN I SELECTED AMONG THEM IT WAS NOT CONNECTED. THAN I HAVE JUST PASTED MY NAME INTO THAT DROP DOWN LIST AND PRESS NEXT NEXT IT GOT CONNECTED." THIS WAY I HAVE TRANSFERRED HUGE AMOUNT OF DATABASE IN FEW SECONDS. !!!!!!!!!!!!!!!!!!!!!!!!!!!THANKS A LOT!!!!!!!!!!!!!!!!!!!!!!!! :)
    • Proposed as answer by KAVITA BHAGAT Thursday, January 20, 2011 5:15 PM
    Thursday, January 20, 2011 5:12 PM
  • HI Wolfey305 THANK YOU FOR PROVIDING SUCH A USEFUL DETAIL OF IMPORTING DATABASE TO SQL 2005 EXPRESS. I HAVE FOLLOWED THESE MENTIONED STEPS FOR TRANSFERRING MY HUGE ACCESS DATABASE TO SQL 2005 EXPRESS WITH LITTLE MODIFICATION THAT "IN CREATE NEW DATA SOURCE TO SQL SERVER DIALOGUE BOX, WHEN THEY ASK ' WHICH SQL SERVER DO YOU WANT TO CONNECT TO?' HAS MANY SERVER DROP DOWN LIST. BUT WHEN I SELECTED AMONG THEM IT WAS NOT CONNECTED. THAN I HAVE JUST PASTED MY NAME INTO THAT DROP DOWN LIST AND PRESS NEXT NEXT IT GOT CONNECTED." THIS WAY I HAVE TRANSFERRED HUGE AMOUNT OF DATABASE IN FEW SECONDS. !!!!!!!!!!!!!!!!!!!!!!!!!!!THANKS A LOT!!!!!!!!!!!!!!!!!!!!!!!! :)

    • Proposed as answer by KAVITA BHAGAT Thursday, January 20, 2011 5:15 PM
    Thursday, January 20, 2011 5:15 PM
  • Thanks Wolfey305. I could copy my huge access database into ms sql server 2005.
    Wednesday, April 06, 2011 9:29 AM
  • Hello Bill. I have downloaded the toolkit and selected all the components, but DTSWizard is nowhere to be found.
    Darrell H Burns
    Thursday, January 26, 2012 12:20 AM
  • Hello,
    Try:
    C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
    Worked for me!

    Thanks,

    Work for me.

    Thursday, June 14, 2012 2:48 PM
  • Hello Bill. I have downloaded the toolkit and selected all the components, but DTSWizard is nowhere to be found.

    Following article explains how to get the dtswizard:

    http://www.webcosmoforums.com/databases/4580-data-import-export-microsoft-sql-dts.html

    Also, for about $50 you can get SQL Server 2012 Developer Edition which has everything.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Monday, October 15, 2012 8:19 PM
    Answerer