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

Answer 1

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

Answer 2

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

Answer 3

 

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

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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

 

Answer 8

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.

Answer 9

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.

Answer 10

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

Answer 11

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

Answer 12

Hello,
Try:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
Worked for me!

Answer 13

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?

 

Answer 14

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

Answer 15

I've posted this article, hope you find it helpful.

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

 

Answer 16

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.

Answer 17

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.

Answer 18

Thanks to janesim on 05-21-2006 !  This is EXACTLY what I needed.

Answer 19

I am switching back to MySQL...

Answer 20

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

 

 

Answer 21

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

Answer 22

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

Answer 23

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!

Answer 24

I did not find this subdirectory, I was good up to \90 then the rest is not present.

Answer 25

Thanks, that fixed my problem.

---
FREEDOM!

Answer 26

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

Answer 27

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.

Answer 28

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?

Answer 29

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

[e-mail]

Answer 30

I have SQL Server 2005 Express, SP1, and the DTS subdirectory doesn't exist.

Answer 31

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?

Answer 32

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?

Answer 33

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.

Answer 34

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

Answer 35

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

Answer 36

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

Answer 37

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.

Answer 38

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)

Answer 39

I had the same problem until I downloaded the tools from http://msdn.microsoft.com/vstudio/express/sql/download/

 

Good luck

Answer 40

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

 

Answer 41

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

Answer 42

I agree. importing and exporting are big item to not have!

 

Answer 43

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.".

Answer 44

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.

Answer 45

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.

Answer 46

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.

Answer 47

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.

Answer 48

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/

Answer 49

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

Answer 50

What type of data are you trying to import?

Answer 51

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.

Answer 52

SQL Server 2005 Express Edition Toolkit

 

You need number 4.

Answer 53

I have make a try and finds that it works for numeric and char data, but not work for date data

Answer 54

Same for me...was good upto \90..

Answer 55

Same here..

Answer 56

 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.

Answer 57

Beautifully spoken R2 DJ!

Answer 58

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?

Answer 59

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

Answer 60

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"

Answer 61

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

 

Answer 62

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.

Answer 63

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 ?

Answer 64

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.

Answer 65

Thanks fionajwc  and Janesim. You guys rock!!!. You saved me from a lot of headaches.

Answer 66

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.

Answer 67

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.

Answer 68

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!

Answer 69

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.

Answer 70

 

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

Answer 71

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.

Answer 72

this is superb.

it works perfect for import/export

Answer 73

Thanks a bunch.  I spent 2-days trying to find inport function.

 

Answer 74

 

I had trouble to use the 'Linked server' function.

Answer 75

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.

Answer 76

 

Thanks for this!!!!

Saved me a lot of time and frustration :0)

Answer 77

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

 

Answer 78

Thanks

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

 

Answer 79

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.

Answer 80

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.

 

Answer 81

Thanks for your comments.

 

Answer 82

 

THIS IS REALLY WORK GREAT.

USEFUL ONE

Answer 83

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

Answer 84

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.

Answer 85

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

 

Answer 86

 

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.

Answer 87

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

Answer 88

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.

Answer 89

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

Answer 90

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

Answer 91

Thanks Janesim. This is what I am looking for.

Answer 92

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

Answer 93

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.

 

Answer 94

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.

 

Answer 95

Sorry, should have mentioned that I opened a query window in SQL Server Management Studio Express.

Answer 96

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

 

Answer 97

 

Fentastic Information.

 

Thanks

 

Harish

Answer 98

YES!  This is what I was looking for.  Thanks janesim!

Answer 99

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] [[e-mail]]

[2] [Rob De Niro] [[e-mail]]

[3] [John Smith] [[e-mail]]

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!

Answer 100

Wolfey305

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

Answer 101

Thanks to Janesim on 05-21-2006 !

Exactly what I needed too and works like a charm !

 

 

Answer 102

cool. thanks.

---

P.K.Rao

Answer 103

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

Answer 104

 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.

Answer 105

If Connection Failed..

Try adding \sqlexpress to the end of your server name. Worked for me, hope this helps someone else!

Answer 106

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", false, false, 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");

}

}

Answer 107

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

Answer 108

thanks a lot for you. it is most useful for me. thank you.

Answer 109

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.

Answer 110

This works like a charm - thanks!

Answer 111

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.

Answer 112

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

Answer 113

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.

Answer 114

IJCalypso wrote:

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

WITH (FIELDTERMINATOR = ',');

GO

 

 

Thanks! This worked perfectly.

 

Answer 115

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

Answer 116

Following is the link for the preceding post:

SQL Server Migration Assistant 2005 for Access V4.0

SSIS Import/Export Wizard:

http://www.sqlusa.com/bestpractices/ssis-wizard/

---

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

Answer 117

thanks for the post, was useful...

Answer 118

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

Answer 119

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

Answer 120

Thanks Wolfey305. I could copy my huge access database into ms sql server 2005.

Answer 121

Hello Bill. I have downloaded the toolkit and selected all the components, but DTSWizard is nowhere to be found.

---

Darrell H Burns

Answer 122

Hello,
Try:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
Worked for me!

Thanks,

Work for me.

Answer 123

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