DTS in SQL 2005

Hello,

This is kind of a stupid problem. I just installed SQL 2005 Standard and I am trying to use DTS to transform a Access DB to SQL. I am having a tough time finding where that feature is. Is it by default insatlled or is it an add-on?

Thanks!

In SQL 2005 it is no longer called DTS it is known as SSIS(SQL Server Integration Services). You can migrate DTS packages to SSIS or re-build them using the Business Intelligence Development Studio.|||

Thanks!

So does SSIS transform a mdb file the same as the DTS did in 2000?

|||

I have never transformed an mdb file; however, I am assuming it should work. Within the BI Development Studio tool, when you create an SSIS package there is a task that allows you to run SQL 2000 DTS packages. You might be able to get away with creating a one 'step/task' SSIS package that basically runs your SQL 2000 DTS package.

I used the migration wizard in the BI tool to 'convert' my DTS to SSIS and it worked fine; however, mine does not deal with mdb's. My package basically is a SQL statement as the source(in SQL 2005) and 'pushes' the results to a destination table in a SQL 2000 database.

P.S. My DTS was a simple task on the surface, but to migrate to SSIS and then test it as well as understand the new SSIS and BI tool and figure out how to run the package, etc. probably took me a week. Maybe I am not that good at it or I took the long road, but to move(and then understand) from DTS to SSIS is not a trivial half-hour job.

|||

Thanks for the imput. He is my deal. I am new to SQL. I had a evalution version of 2000, and I literally had the MDB file converted into an SQL DB in 5 minutes with DTS. So, once I was ready to purchase and install the full SQL product, I figured that I should go with 2005. Everyone I spoke with said it was way more user friendly!! I still think it probably is, but it certainly is not as easy as the 2000 was.

I am getting stuck alot with my new project, but I am still really enjoying the hands on erxperience dealing with this new technology.

-Mac

|||

You probably used the Import Wizard to do that in SQL Server Enterprise Manager 2000. Have you tried the same in SQL Server Managment Studio 2005? I just did and it took about 20 seconds.

-Jamie

|||

Any luck!

Sounds like what you are trying to do is convert an MDB database to a SQL 2005 database. If this is just a one-ime deal then you can probably get this done as fast as Jamie states.

I assumed you were wanting to move data from MDB to SQL 2005 on a regularly scheduled basis. This too can be simple; however, I feel to truly understand SSIS and its power it may take more than just a one-time 20 second migration. That was my point of my previous post. Each time I 'play' with SSIS I learn something new. It si an on-going educational experience for me.

Swit

|||

Thanks for the reply. I am not trying to import data into SQL on an ongoing basis. I am trying to upgrade our company from an MS Access DB and front-end to ande SQL DB with an ASP.NET website. So what I am trying to do is have SQL import all of the tables and columns and the data and create its own DB from it.

For that reason, the DTS in 2000 was great. I did it just to play with it and it instantly created to DB for me. So now instead of trying to figure out this SSIS in 205, I think I will use the DTS in 2000 and them import it into 2005.

Any feedback?

-Mac

|||

Well, its really up to whats best for you now. SSIS is a much more robust application/platform than DTS was. However as this is a one time operation, your familiar with DTS, and perhaps your short on time, do what you have to do...and enjoy reading about all the great SSIS features later :)

When you get a chance you might want to look at the many webcasts there are for SSIS as a way to ramp up. http://msdn.microsoft.com/SQL/bi/integration/default.aspx

Also, you might explore an option inside Microsoft Access. The upsizing Wizard, which is specifically desinged to move your Access data to SQL. In the Access 2003 menues you would get it from here Tools>>Database Utilities>>Upsizing Wizard

Hope that helps

|||

Thanks for the reply. I am in the process of using the "Upsizing" wizard to convert to the SQL. It is exactly what I was looking for.

-Mac