November 21, 2007 at 3:00 pm
Hi all. First of all HAPPY TURKEY DAY weekend!!
OK, down to business...
I have one DTS package that runs on a production server (2000). The IT dept is about to upgrade to 2005. What is going to happen to the DTS package? Is there a way to script it out, so that if they hose it, we can recreate? Any help here is much appreciated! Thank you.....:hehe:
November 21, 2007 at 4:42 pm
Open it in DTS Designer and save it as a structured storage file, preferably somewhere other than the server that hosts the SQL Server instance.. I haven't done an in-place upgrade from SQL 2000 to SQL 2005, so I don't know for sure, but I don't think that DTS packages get converted to SSIS when the database engine is upgraded.
Greg
Greg
November 26, 2007 at 10:38 am
I don't believe they do either. There is a 2000 DTS runtime you can install on SS2K5 that will run your packages for you. There is also a conversion utility, but it only really works with simple packages.
November 27, 2007 at 2:05 am
Hi
I use:
Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Audience(s): Customer, Developer
X86 Package (SQLServer2005_DTS.msi) - 5088 KB
Open the .dts file created in SQL Server 2000 and then modify the
DTS-package to connect to Server 2005.
But you cannot create a new packed. Then consider using SSIS.
/Gosta
November 27, 2007 at 2:47 am
After you install the DTS Designer components into SQL 2005 you can maintain your packages in the same way as on SQL 2000. We needed to apply the fix in KB 917406 before we could edit all of our DTS packages.
If you have custom components and are using 64-bit SQL 2005, any files that needed to be put in \System32 on a 32-bit machine must instead be put in \SysWOW64 for 64-bit SQL.
There is no 'New' option in the SQL 2005 DTS Designer, but that does not stop you creating a new package. Open an existing package in SQL 2005 DTS Designer, then 'Save as' to your desired new package name. Delete all unwanted components from your new package, then add whatever functionality you need.
All of the above should apply in SQL 2008, but I have not tested everything in that environment.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 27, 2007 at 9:21 am
November 27, 2007 at 9:43 am
You can script all of your packages out of SQL 2000 and script them in to SQL 2005. This forum has examples, or look up Nigel Rivett's examples.
However, any form of scripting will loose the layout and annotation details of your packages. The only way with Microsoft software to export and import DTS packages and keep the layout and annotations is to do the job manually. There are some vendor products that can migrate and keep the layout and annotations but they are not free.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 27, 2007 at 9:52 am
November 28, 2007 at 2:28 am
...and where can I find Nigel Rivett's examples?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 28, 2007 at 10:59 am
I have a question about using the Microsoft SQL Server 2000 DTS Designer Components.
I have noticed that when I open a DTS package up using this that my SSMS locks up and stays that way until I close the DTS Designer. But, my coworkers PC does not have this issue. He can open up the Microsoft SQL Server 2000 DTS Designer Components and continue to work in SSMS as well.
Has anyone here experienced that and know what I might need to do to have my SSMS not lock up?
Thanks,
Chad
November 29, 2007 at 1:56 am
I experience this as well but I just put it down to one of the many many many many bugs in 2005. Now that I know you should be able to continue to work in SSMS while having a legacy DTS package open I'll start digging around as well.
If you or anyone else finds an answer before I do I would be greatfull for a reply as well.
November 29, 2007 at 2:19 am
I have never had this issue, but please post the solution when you find it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 29, 2007 at 2:26 am
just apply service pack2 for sql 2005 and you will feel good once u update the service pack
November 29, 2007 at 2:28 am
Service Pack 2 did nothing for us on this score.
November 29, 2007 at 8:32 am
SP2 did nothing to resolve this here as well. I currently have my client tools and servers at SP2 CU4 and still have the issue.
Chad
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply