September 25, 2006 at 7:48 am
We're migrating a database and it's related DTS packages to SQL 2005 on another server. The DB and DTS do not need much maintenance because they are pretty straight forward; Simple data manipulation then dump to a text file before sending to an outside FTP site. I think we have 3 options.
1) Run the Wizard to convert the DTS to SSIS (I tried several and got no errors)
2) Run the existing 2000 DTS packages via SSIS
3) Rewrite the DTS as native SSIS.
I'd like to get them off the old server pretty soon because the disk storage they're on is old and coming off warantee & maintenance. So, since time & learning curve are an issue, I'm looking for suggestions.
Any thoughts ?
September 26, 2006 at 7:29 am
My 2 cents would be to try using the wizard first and test the packages it creates for you. I have used it to upgrade several packages that were a simple data pump task from 1 server to another and the upgrade worked well.
September 26, 2006 at 7:38 am
I tried a few with the wizard and they succeeded. But I can't figure out how to edit them. How do you open the damn things ? I connect to SQL2005 with "Integration Services" (as opposed to Database Engine or the others). I can see the packages, but not how to edit or run them. If I open SQL BI Dev Studio I can't see the packages anywhere to edit them. I've been on 2000 for a while, but I've just started with 2005 so I don't "get it" yet.
September 26, 2006 at 8:24 am
Seems like when you use the migration wizard you have to specify which packages you want to upgrade, and also how you want to store the newly created packages. You can choose either as files(.dtsx) or store them in msdb.
I would guess yours are stored in msdb database. If you run through the migration wizard again look for the option to export them to a file. Then you can open BI create a project and edit the files in there.
September 26, 2006 at 9:11 am
The "migrated" DTS packages are simply placed into a SSIS wrapper that can run DTS packages - kind of a translator. We have moved our DTS packages over using the migration wizard and are rebuilding them in Business Intelligence Developement Studio. You'll need to open the migrated package using this tool to edit it. The actual old DTS code is buried deep inside the package so you'll have to dig deep to find it if you want to edit that code.
To open the migrated packages, you need to choose to open from a SQL server because they are stored in the msdb database on the server where the package was migrated. One other thing, if you rewrite for native SSIS, when it comes time to run the package under a job, you will need to add the user running the package to the proper dts role in the msdb database before it will run successfully.
Joshua Perry
http://www.greenarrow.net
September 26, 2006 at 9:30 am
To actually open/edit the packages you can open BI Studio and create a new integration services project. From here you right click on SSIS Packages and choose add existing package. In the next dialog you tell it what location to look for the package ie sql server or file list(Default migrations will result in the upgraded package being placed in msdb) then choose the package path at the bottom(the button with the .) and select your package.
I have upgraded/migrated several packages and the only time I have had it 'wrap' DTS in an SSIS wrapper is when it didn't know which SSIS task the DTS Package Task corresponds to. Our dts packages were simple data push from source to destination and they always migrated without a wrapper and we could go in and look at the Control Task and the Data Flow task.
Hope this helps
July 1, 2008 at 2:19 pm
Have you tried DTS xChange ???
Its the only 3rd party tool in the market which gives the best DTS to SSIS Migration Capability
It will help you to Discover, Migrate and Monitor your packages and also apply best practices to migrated packages.
Homepage
Comparison with MS Wizard
http://dtsxchange.com/DTSxChange-vs-MSWizard.asp
Screenshots
March 23, 2009 at 10:19 am
Regarding OPTION 2 below:
1) Run the Wizard to convert the DTS to SSIS (I tried several and got no errors)
2) Run the existing 2000 DTS packages via SSIS
3) Rewrite the DTS as native SSIS
When Running existing 2000 DTS pkg's via SSIS, do you need to retain the "SQL 2000 SQL Server" housing these DTS packages and reference the Server Name \ Package Name at execution time -OR- can you port the 2000 DTS packages onto your SQL 2005 server, as is, with no changes and execute the SQL 2000 DTS packages right from the new SQL 2005 server (therefore allowing for the sunsetting of the old 2000 server)?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply