SQL Server 2005 DTS Alternative

Posted By : todd sharp Posted At : December 4, 2006 8:58 PM Posted In: SQL

10

I'm pretty sure this is my final SQL post today. Like all good free things, the new SQL Server Management Studio Express does not include any DTS-ish import/export support. Here's a quick workaround for importing data. I have an Access database on my machine that I want to copy some tables into my recently created SQL Server 2005 database. To do this without DTS, just create a new ODBC Datasource on your local machine and configure it to point to your SQL db. Then go into Access, pick your table and Export to your new ODBC datasource. Go over to your SQL db, refresh and you'll see the table. Of course you lose some of the DT (data transformation) parts of DTS - but at least you can get your tables over from other sources.

9181 Views Send Print Comments (10)

Related Blog Entries

Comments (10)

Subscribe to Comments Add Comment

psybonix's Gravatar I think you can just add the DTS import export wizard into you tools menu.

Can't remember exactly how but somewhere in the options I have got it to work. I found that if you still have SQL 2K enterprise manager installed use the DTS tool from this install as it worked a little better.

The DTS wizard is probably in the bin directory of the server.

Sorry I am not a little more useful..... my memory sucks.

Posted by: psybonix on: 12/4/06 10:14 PM

Philippe Maegerman's Gravatar In Management Studio, you can right click on your database / tasks / import|export :)

Posted by: Philippe Maegerman on: 12/5/06 1:38 AM

Josen Ruiseco's Gravatar Sql Server 2005 Express does not have the import / export option (I just checked) which is why he is posting the alternative option.

Posted by: Josen Ruiseco on: 12/5/06 4:21 AM

Philippe Maegerman's Gravatar Right, sorry ... didn't take in account the 'express' part :((

Posted by: Philippe Maegerman on: 12/5/06 5:10 AM

DuyenDTM's Gravatar I want to import data from excel file into database of SQL Server 2005.
I had a module. It was written from VB6.It's OK with SQL 2000.
Now, I can't use this module to import.
Do you have any solution to help me?

Thanks

Posted by: DuyenDTM on: 1/3/07 10:07 PM

todd sharp's Gravatar You might want to try a bulk insert.

Posted by: todd sharp on: 1/4/07 3:03 PM

Dmitriy's Gravatar Hi, you should try this http://www.aspnetcafe.com/post/2007/12/HOWTO-Get-C... as alternative for DTS. Helped me a lot!

Posted by: Dmitriy on: 12/21/07 5:01 AM

bornmw's Gravatar Please don't try the DTSMigrationWizard.exe with Express - it will not work.
To export data from MS SQL Server Express use Microsoft SQL Server Database Publishing Wizard:
http://www.microsoft.com/downloads/details.aspx?Fa...

Posted by: bornmw on: 8/12/08 11:11 AM

Philip Wong's Gravatar You can also use the Upsizing Wizard in Access 2000 (upto Access 2003).
It will display the list of all table inside the access database for you to choose which ones (or ALL) to upload into the SQL server.

Note:
It is easier to set up the Data Source entry to the SQL server database first.

Posted by: Philip Wong on: 12/8/08 12:35 AM

David's Gravatar Thanks for pointing me to a wonderful workaround solution.

I open my MS Access database, open up the table and choose Export from File memu. Then I just follow through with two important inputs for the SQL Server Driver that I select in one of the steps: Server= MySQLserver2005-which-I connect-to, Database=Mydatabase-that-I-created-before-hand-in-SQLServer2005. Then I choose a Datasource name (whatever name).

That is all about it. My large MS Access table show in new SQL Server database table perfectly.

Thanks again Todd.

David

Posted by: David on: 4/7/09 3:03 AM
Add Comment

  • Calendar

    << March 2010 >>
    Sun Mon Tue Wed Thu Fri Sat
     123456
    7 8 9 10 11 12 13
    14 15 16 17 18 19 20
    21 22 23 24 25 26 27
    28 29 30 31    

  • Subscribe

    • Enter your email address to subscribe to this blog.