[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL SERVER 2005 SSIS migrating DTS from 2000 to 2005  - quick and easy

Posted on 2006-05-05
30
Medium Priority
?
271 Views
Last Modified: 2013-11-30
HI

I have run the migration and I can see my packages in the integration services section BUT I dont have an edit button, How do I edit them ? I need to make changes since importing them but cant find out how to do this:

how do you edit DTS packages after you have migrated them to a sql server 2005 ?

thanks
0
Comment
Question by:Ken-doh
  • 15
  • 11
  • 2
  • +1
29 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 16614694
You download this: http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en


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) - 5083 KB
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16614747
hi

no - I have migrated them to ssis there sitting in my msdb in sql 2005 i have that tool it  lets me edit dts packages in sql 2005.

I have my ssis packages migrated in the intergration server on 2005.....

i need to know how to edit them, that tool isnt what I am after,

thanks
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16614783
SSIS packages are edited using Visual Studio.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:Ken-doh
ID: 16615042
yes

I know that you use visual studio

how do I get them in visual studio  ? there in my integration services under stored packages > msdb

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16615433
In Management Studio - Connect - Choose Integration Services - a dialog box will ask which server.
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16615477
yes - i can do that,

the problem is getting my packages (that I just migrated) into the visual studio
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16615595
Ok...In Business Intelligence Projects - Create a new Integration Project. Then Under the Solution Explorer you will see SSIS Packages. Right click - Add Existing Package. Dialog will ask for the Package Location and package path.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16615618
You will probably want SSIS Package Store and then your MSDB will be under the package path.
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16615638
think were on the right track :)

how do I find out what the package path is ? and what is the ssis package store ??

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16615692
Laughing - cool - ok. SSIS packages can be saved several different ways - the destination store is the SQL Server 2005 MSDB db, which was how DTS packages were saved.

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16615757
SSIS Package Store is usually Program Files\Microsoft SQL Server\90\DTS\Packages folder (if you install it with the defaults).
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16615814
I really thought we had it then,

but that folder is empty

there not DTS - there ssis packages as its been migrated
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16615841

"destination store is the SQL Server 2005 MSDB db"  yes there in there ! but i cant get to them in visual studio to modify them :|

any ideas ? there not in the DTS folder as mentioned before
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16615922
Laughing - hey, not responsible for Microsoft's folder naming conventions - Program Files\Microsoft SQL Server\90\DTS\Packages

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16616039
ok...I created a new project in VS, then added an existing package. I selected SQL Server (added a server name) and when I clicked on the ellipsis for file path it had the packages listed.

syspackages90 in MSDB stores the content - sysdtspackagefolders90 tells you the folders
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16616055
C:\Program Files\Microsoft SQL Server\90\DTS\Packages    is empty :(


this is driving me f*****g mad
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16616082
i cant open that table :\   its all grayed out...

0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16616112
right
I can export them to a dtsx file -

then I can open that in vs - BUT thats so WNAK

i have about 30 packages to change, i have to export them, then import them again for each 1 ?

why couldnt ms just leave DTS alone - it was so good!
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16616255
On the SQL Server box - check
Program Files\Microsoft SQL Server\90\DTS\BINN\MsDtsSrvr.ini.xml

0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16616278
<Folder xsi:type="FileSystemFolder">
  <Name>File System</Name>
  <StorePath>..\Packages</StorePath>
  </Folder>


but that packages folder is empty - as I said its not DTS is this nobby ssis stuff...
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16616299
I know, I know - but Microsoft did not rename the folders (and obviously did not rename the tables in MSDB) to SSIS, they just left the DTS name. Ok. I don't know what else to tell you - I can open my MSDB SSIS packages like this: I created a new project in VS, then added an existing package. I selected SQL Server (added a server name) and when I clicked on the ellipsis for file path it had the packages listed. I do not know why you do not see them or access them.

0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16616322
yeah i can open them that way 2 :)

but its cause I have migrated them from sql 2000 :(

bugger.....


anyone else?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16629180
hi ken-doh,
i dont think you can edit a package migrated from sql 2000 you can see them in legacy folder in sql 2005 management studio. you can run them and schedule them.
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16629199
there not in the legacy folder, that is empty - if they were in there I could edit them, but they are appearing in the msdb as mentioned above,

I have exported them to the file system, I can then edit them and then import the file into ssis BUT its really time consuming and there must be a better way as I have lots of packages to migrate.....
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16629750
If you have upgraded your sql server 2000 to 2005 they should appear in the legacy folder.  


If you have created them in sql server 2005 management studio then they are saved internally in msdb file and there isn'
t any way you can see them in
In Management Studio - Connect - Choose Integration Services - put your server name.
then check them under stored packages.
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16629760
Hi

thanks - I havent upgraded - I have a new 2005 server and in my integration services the packages are listed - BUT I cant find out to edit them....
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16631854
I need to get this done,

lets flip it on its head

if i export the packages to a .dts

then import them, they will appear in legacy.... where I can edit them etc

how do I schedule these to run ?
0
 
LVL 1

Author Comment

by:Ken-doh
ID: 16656374
BINGO

2 ways of doing it

BULK

migrate your DTS pacakges to the file system as SSIS packages
then create a new job that calls ssis packages on the file system or you can import them to the ssis and run a package that way

OR

if its only 1 dts package that needs to be kept as dts (because it doesnt work as an ssis package

you export the dts to a dts file
import the file into sql as a dts legacy package
you create a new ssis package to run the old dts package
then you create a new job to run the new ssis package that runs the old dts package
then you create a schedule to run the new job that runs the new ssis package that runs the old dts package
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 16850070
PAQed with points refunded (270)

CetusMOD
Community Support Moderator
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question