DTS and how do you edit in SQL 2005?
-
Is it me or am I losing it? All I want to do is edit a previous version of DTS which I imported into SQL 2005 using the import wizard.
I have also downloaded and installed Microsoft SQL Server 2000 DTS Designer Components. I can see the packages using integration services, and I can run/delete any DTS package but can I edit. NO.
Can anyone tell me how to do this as my table top cannot take anymore punishment from my head banging on it in utter frustration?
-
Since you have the Microsoft SQL Server 2000 DTS Designer Components loaded, you should be able to access the the TS packages using SSMS. You should access it through the following in the Object Explorer:
Management
Legacy
Data Transformation Services
I can't on my development system as I haven't loaded the Designer Components.
-
Now I did think that would be the obvious place but its empty.
I also created an example where I imported a text file and saved it as a SSIS and that is where all my DTS packages have been saved, under Integration Services
As I mentioned, I can run and delete but are unable to edit
-
Have you tried BIDS? (Business Intelligence Development Studio)
-
Yes, I did try looking in BIDS but where are the files stored? I did a wildcard search on the server looking for my data files and came back blank.
Now do you see my frustration.........
-
Yes, and I wish I had a better answer for you at this point.
-
I have not played with importing DTS to SSIS, but I have used DTS Designer in SQL 2005. I think the two ways of doing this are mutually exclusive - either you import your DTS package to SSIS and it is wrapped as read-only code, or you use DTS Designer in SQL 2005 and keep the ability to maintain your package.
After installing DTS Designer (also look out for KB 971406 fix), use SSMS and right-click on Data Transformation Services. Select Open, and locate your DTS structured storage file. When the package is loaded into DTS Designer, do a Save As and save it to SQL Server. It will then be kept in msdb in the same way as in SQL 2000.
If your packages use and custom components you will have to register them before you can open their structured storage files. This can be a bit tricky - you need to open a package that does not use the components, then register the custom components within DTS Designer, and finally abandon the Designer session. After this you can open the ssf files for the packages that use the components.
If you are running on 64-bit, any custom modules that would be put in \System32 on 32-bit need to go into \Syswow64.
Finally, MS have announced support of DTS Designer in SQL 2008, but I have not checked yet if the support is in the July CTP.
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
-
Thanks, now I understand
-
so... my question is this...
kevin, were you able to edit after all this??
i too am having extreme frustration with this process.
_________________________
-
After much swearing and banging my head in frustration I finally sussed it.
When you import a previous version of DTS using the migration wizard you are able to run the application using Integration Services but are unable to edit the package as it is hard coded.
If you use SQL Server Business Intelligence Development Studio (BIDS) you are able to import the DTS packages and save them as a dtsx file which you are then able to edit.
This is still an ongoing issue as I have no idea how to run a saved dtsx file in BIDS but I now understand what has happened.
-
nuts!
so basically if you migrate your DTS packages from a 2000 server to 2005 server,
you are essentially unable to edit the connection properties to point to your
NEW 2005 server.
what good are the DTS designer components then?? aside from the 2000 run-time environment which is installed. it's probably the ability to read it so if/when
you are ready to create another one in ssis you can. thats the only reasoning i
can see behind it.
i guess one solution is to goto the 2000 server make a copy of all the dts packages on and change the source/destination locations to my 2005 Server name, and then migrate them over to it. this way i can run them... and... it should work and i won't need to
edit them in BIDS/SSIS
thoughts?
_________________________
-
Welcome to my nighmare, as this is what I am doing to overcome this problem.
Be careful if you have DTS packages that import/export data with a command to delete tbl before the import I have found some packages will work and other refuse.
-
You can get virtually the same functionality for DTS in SQL 2005 as in SQL 2000. I have done this and it works.
First, you must have the DTS Designer components installed.
Second, you must load your DTS packages into SQL Server 2005 using DTS Designer.
Using SSMS, right-click on the DTS Designer and select Open Package. Point the dialogue to the structured storage file you created from SQL Server 2000. When the package is loaded into DTS Designer, select Save As and say you want to save to SQL. Enter the name of your SQL instance and the package is saved into msdb as a local package.
You can create a new DTS package in SQL Server 2005. Because there is not a New option, you edit an existing package and use Save As and give the saved object the name of your new package. Next, you edit your saved object, delete what was in the original package, and start adding stuff for your new package.
If you use the SSIS Import process your DTS package it becomes read-only, and you can get very frustrated...
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply