Post reply

Upgrading SQL Server 2000 DTS Packages to SSIS

  • December 19, 2005 at 2:26 pm

    #111577

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/upgradingsqlserver2000dtspackagestossis.asp

    Brian Knight
    Free SQL Server Training Webinars

  • December 27, 2005 at 7:38 am

    #611519

    I didn't know about the Package Designer for 2000, thanks.

    Many article talk about upgrading but not a lot discuss a migration to a

    completly new server. What about DTS in that case, would it be possible

    to import it to 2005 from an actual 2000 system ?

    If yes would it be possible to use it in the Package Designer?

    Thanks for this precision,

    Dominique.

  • December 27, 2005 at 9:29 am

    #611547

    You bet, you can use the Upgrade Wizard to migrate the package to the SQL Server from a complely different server.

    Brian Knight
    Free SQL Server Training Webinars

  • May 12, 2006 at 6:30 am

    #637517

    I'm afraid there's an element of naivety here. OK, a company may have "dozens or hundreds of SQL Server 2000 DTS packages", and - if they have enough budget - will eventually find time to re-write them. What about companies who sell DTS packages as part of their product suite? Not everyone is going to jump to SQL 2005 as soon as it's available and so those companies are obligated to continue support for both SQL 2000 and SQL 2005 within their customer base. This means they need increased resources to maintain parallel code streams, including the associated testing and documentation.
     
    Maybe I'm missing something here: why couldn't Microsoft have provided a "DTS container" that would operate as a fully-fledged IS task but which could host an existing SQL 2000 DTS task. In other words, providing the necessary software interfaces and abstractions to allow a DTS task to run unchanged within IS.

  • May 18, 2006 at 8:31 am

    #638749

    Is it possible to connect to a SQL 2005 server/instance from within a SQL 2000 DTS package?

  • May 18, 2006 at 8:22 pm

    #638902

    I'm afraid it's not. It uses a different object model.

    Brian Knight
    Free SQL Server Training Webinars

  • September 4, 2006 at 7:22 am

    #658297

    I need that book NOW!!

    So as I can't wait until January I'll just have to buy something else. Pity as I've already bought the reporting services and analysis services from that Professional SQL 2005 series.

  • September 4, 2006 at 10:46 am

    #658395

    It's out now. This was a reprint from late last year.

    You can get the book here: http://www.sqlservercentral.com/books/books.asp?categoryid=12&categorynm=SQL+Server+2005

  • October 13, 2006 at 10:27 am

    #665787

    I am missing something here: I have several DTS 2000 packages nailing Sql 2005 server/instances databases.

    It is worth adding that the feature pack may break Sql Server Enterprise Manager. You may have to re-install it to get it back to its original glory. Once you do this, though, you can work in both environments.

    Also, when working with the DTS 2000 Package Designer (from Management Studio); you may not be able to alter any objects (edit anything) in Management Studio until the Package Designer is closed.

    In my opinion, small pain with big gain. They were very nice to give us a DTS Designer for management Studio.


    {Francisco}

  • February 1, 2008 at 5:09 am

    #774133

    I 've used the Migration Wizard for my DTS packages and it was worked well. But, my new server and database server have different names from the old one. So I have to change the connections from inside every package. How to do that? How to edit the packages since they are already imported in SQL 2005 SSIS? I've tried with BI but there is no deploy option to deploy the package to SSIS repository where my packages are stand on.

    Also, I have installed the DTS Designer 2000 but how to work with it in MSS Management Studio?

    In Theory, theory and practice are the same...In practice, they are not.

  • May 23, 2008 at 7:21 am

    #819641

    I found the abandonment of the Dynamic Properties task; which I used extensively as it was used to set the name of files to be downloaded, to set the date of datasets to upload, and many other purposes; so problematic that I decided to abandon the ETL platform an implement via SQL using xp_cmdexec to call ftp or a dedicated http download program (which was trivial to implement via MFC) and BULK INSERT to upload data into the server. I am much happier with this path, and I'm wondering if anybody else followed it. Now, instead of some "Fisher Price's My First ETL Package" paradigm I have complete control of the ETL operations and have don't have to worry about maintaining a parallel code base for ETL.

    This on a SQL 2005 Enterprise platform with 8 cpu's and 8GB of RAM that supports a 185 GB database that grows by about 2 GB per day.

  • May 23, 2008 at 1:57 pm

    #819912

    I have a out of the box application that will be moving/upgrading from sql 2000 to sql 2005. I have dts packages that read the oob database and pull data into my datawarehouse.

    Are you saying that if I convert/move my database to sql 2005, my sql 2000 dts packages cannot be pointed to that new database/server?

  • May 27, 2008 at 7:04 am

    #820537

    i don't do much ETL except for moving some data between servers, but i've started to use SSIS less and less for it and back to the old way of stored procedures.

    one big advantage that I like using sp's over SSIS is that i can make a job step be one sp and while troubleshooting performance it's easy to see how long each job step took. where if you have a multi-step SSIS package you may have to run it manually to see where the problem is.

    and i had weird authentical issues with SSIS like when you create a package on your PC and then save it on a server to run.

  • February 25, 2009 at 9:32 am

    #950098

    Just something stupid I ran across.

    If you migrate the packages to a name instance of SQL Server 2005, the migration program will allow it, but you'll never be able to do anything with the package because SSIS management studio, says it only supports a signle instance on a server, so you must connect to the default instance of SQL Server. Gues what, that's not the MSDB the packages are now stored on. :crazy:

    Now I gotta go migrate them again.

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • June 23, 2009 at 1:02 pm

    #1014189

    I'm debating whether I should just pick up your book, Brian. Probably be a good idea. Are you doing an update for SS2008?

    My problem is that I'm doing upgrades between servers, so I have a running 2005 server that I'm migrating 2000 databases from, soon I'll be setting up a 2008 server and migrating a different group of databases to it. I don't think the Package Designer for 2000 is going to do it in this case and I'll be looking at creating new packages in BIDS/VS and doing a migration from 2000.

    Can't say that I'm looking forward to it.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply