When your question has been sufficiently answered, please be sure to mark your question as answered. Also, please share your solution if you had to figure it out on your own!
I had originally posted some resources, but I can't get the DTS to appear anymore using either of the methods. I wonder if MS took DTS away from the tools in the latest service pack?
When your question has been sufficiently answered, please be sure to mark your question as answered. Also, please share your solution if you had to figure it out on your own!
Based on my understanding, SQL Express doesnot have DTS/SSIS shipped with. We can only use
BCP, Bulk Insert, Openrowset, Code..etc to do export/export (based on the datatype you want to deal with).
Take a look at the following article I have found, i hope it can help us better understand this export/import feature.
So onto SQL Express, SQl Express does not include DTS or its replacement SSIS........
Ok there is no DTS and no SSIS, so why did this page
state that import/export is a feature of express? Well there are more ways to get data into and out of SQL Server and in fact its one of those generational things, if you were brought up on SQL 7 or later than you likely think
of DTS/SSIS for import export, if you were brought up a little earlier than that then you probably think of BCP and OpenRowset, more about these further down. We did actually go back and forth a fair amount on the descriptions used in the matrix for the SKUs
as to whether we should talk about import export, in the end we decided to do it but it looks like we created some confusion, so the page has been flipped back.
Lets talk about different types of data;
Text
If you want to import text there are several options,
BCP is a command line tool that can be used to import text files, it runs as an external process but has been around for ages and is stable and predictable, it also has some nice touches like format
files.
Bulk Insert is a T-SQL statement that runs inside the SQL Server process space(so there are perf advantages, it handles much the same as BCP and also supports format files including a new easier to
read XML format in SQL2005, however its future is less certain.
OpenRowset, this is the new recomended T-SQL way, I have not played with it much but it seems fast, stable and easy to use.
Code, you can always use the text file classes in NetFx (especially those that come with the VB package) and then just use the SqlBulkCopy object(this will accept a DataSet a DataTable or a DataReader).
XML
Again several options,
OpenRowset, work just as easily with XML as it does with Text
Code(1) you can use System.Xml and System.Data to generate a dataset, datatable
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites
and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Thanks for the reply. Prior to the latest SQL Server service pack, I was able to download and install the SQL Server 2005 DTS wizard from the Microsoft site (or install the full Express Toolkit) and it worked with SQL Express. The latest service pack wiped
out my DTS installation and now it prevents me from re-installing DTS. DTS was probably something MS never intended for the Express version, but somehow slipped in with the Toolkit. If that is the case I can understand why they removed it.
In the oiginal blog entry you pointed to it seemed like the biggest reason for leaving DTS/SSIS out was that it made the size bigger. Well, I have several GB of HDD and plenty of RAM just waiting to be filled. After having the original Express Toolkit
DTS working just fine, having it wiped out by the latest SP, and being unable to re-install it leads me to beleive that it is less about size and efficiency and more about pushing people towards SQL Server Standard.
No worries though! I had already started moving most of my new development work to MySQL. I like the SQL Server GUI better, the Membership/Role/Profile provider stuff is nice, and I like the native GUID support; but the MySQL .Net Connector is catching
up pretty rapidly, their tools are easy to use, it has a smaller memory footprint, it is fast, and (my favorite!!!) I like using FabForce's free DbDesigner to quickly build my database schema. Most importantly, customers feel they are getting an enterprise
level RDB for free.
That said, I feed Data Import/Export is necessary for most people. I can do without the enterprise features such as multi-processors, 64-bit, mirroring, clustering, log shipping, notification services, etc. But import/export is essential. Yes, I can use
BCP, OpenRowSet, etc. Why do that when DTS worked just fine? Maybe MS should consider selling seperate licenses for the DTS wizard for like $50 or $100? Even I can afford that!
Best wishes,
Sam
When your question has been sufficiently answered, please be sure to mark your question as answered. Also, please share your solution if you had to figure it out on your own!
[OLE/DB provider returned message: 无效的授权说明]
[OLE/DB provider returned message: 连接字符串属性无效]
OLE DB 错误跟踪［OLE/DB Provider 'sqloledb' IDBInitialize::Initialize returned 0x80040e4d: 验证失败。］。
Msg 7399, Level 16, State 1, Line 1
OLE DB 提供程序 'sqloledb' 报错。验证失败。
We should be friends all over the world,then we can explore those worlds we have never been!
cheetoz
Participant
794 Points
184 Posts
how to import/export data with sql express?
Jul 28, 2007 03:24 PM|LINK
how to import/export data with sql express?
Sweeperq
Contributor
2691 Points
585 Posts
Re: how to import/export data with sql express?
Jul 28, 2007 03:41 PM|LINK
You need SQL Server Express w/ Advanced Services installed.
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
Sweeperq
Contributor
2691 Points
585 Posts
Re: how to import/export data with sql express?
Jul 28, 2007 03:53 PM|LINK
Bo Chen – MS...
All-Star
17706 Points
1389 Posts
Re: how to import/export data with sql express?
Aug 01, 2007 06:31 AM|LINK
Hi sweepperq,
Based on my understanding, SQL Express doesnot have DTS/SSIS shipped with. We can only use BCP, Bulk Insert, Openrowset, Code..etc to do export/export (based on the datatype you want to deal with). Take a look at the following article I have found, i hope it can help us better understand this export/import feature.
See origional post at: http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx
Hope my suggestion helps
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
Sweeperq
Contributor
2691 Points
585 Posts
Re: how to import/export data with sql express?
Aug 01, 2007 07:24 PM|LINK
Bo Chen,
Thanks for the reply. Prior to the latest SQL Server service pack, I was able to download and install the SQL Server 2005 DTS wizard from the Microsoft site (or install the full Express Toolkit) and it worked with SQL Express. The latest service pack wiped out my DTS installation and now it prevents me from re-installing DTS. DTS was probably something MS never intended for the Express version, but somehow slipped in with the Toolkit. If that is the case I can understand why they removed it.
In the oiginal blog entry you pointed to it seemed like the biggest reason for leaving DTS/SSIS out was that it made the size bigger. Well, I have several GB of HDD and plenty of RAM just waiting to be filled. After having the original Express Toolkit DTS working just fine, having it wiped out by the latest SP, and being unable to re-install it leads me to beleive that it is less about size and efficiency and more about pushing people towards SQL Server Standard.
No worries though! I had already started moving most of my new development work to MySQL. I like the SQL Server GUI better, the Membership/Role/Profile provider stuff is nice, and I like the native GUID support; but the MySQL .Net Connector is catching up pretty rapidly, their tools are easy to use, it has a smaller memory footprint, it is fast, and (my favorite!!!) I like using FabForce's free DbDesigner to quickly build my database schema. Most importantly, customers feel they are getting an enterprise level RDB for free.
That said, I feed Data Import/Export is necessary for most people. I can do without the enterprise features such as multi-processors, 64-bit, mirroring, clustering, log shipping, notification services, etc. But import/export is essential. Yes, I can use BCP, OpenRowSet, etc. Why do that when DTS worked just fine? Maybe MS should consider selling seperate licenses for the DTS wizard for like $50 or $100? Even I can afford that!
Best wishes,
Sam
demoniac
Member
2 Points
1 Post
Re: how to import/export data with sql express?
Dec 21, 2007 07:57 AM|LINK
Hi, you can use the tool described in this article
http://www.aspnetcafe.com/post/2007/12/HOWTO-Get-Complete-SQL-dump-of-SQL-Express-2005-Database.aspx
Hope this helps.
sql express Export Import Dump
wmamani
Member
2 Points
1 Post
Re: how to import/export data with sql express?
Apr 17, 2008 06:03 PM|LINK
Para importar archivos de texto a SQL Express 2005
Usar BULK INSERT
Ejemplo:
BULK INSERT Trabajador FROM 'c:\Prueba_data.txt' WITH ( FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' )
El archivo de texto debera estar separado por comas. That's all
samova
Member
19 Points
39 Posts
Re: how to import/export data with sql express?
Jul 11, 2009 01:11 PM|LINK
I think openrowset will not work.
here is my query:
select * from openrowset('sqloledb','data source=172.16.1.147\sqlexpress;user id=gtmc;password=123456','set fmtonly off; exec eqcas.dbo.getouts ''2009-7-10'',''2009-7-11''')
here is error message:
[OLE/DB provider returned message: 无效的授权说明]
[OLE/DB provider returned message: 连接字符串属性无效]
OLE DB 错误跟踪［OLE/DB Provider 'sqloledb' IDBInitialize::Initialize returned 0x80040e4d: 验证失败。］。
Msg 7399, Level 16, State 1, Line 1
OLE DB 提供程序 'sqloledb' 报错。验证失败。