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

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

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

SSIS packages are edited using Visual Studio.

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

In Management Studio - Connect - Choose Integration Services - a dialog box will ask which server.

yes - i can do that,

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

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.

You will probably want SSIS Package Store and then your MSDB will be under the package path.

think were on the right track :)

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

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.

SSIS Package Store is usually Program Files\Microsoft SQL Server\90\DTS\Packages folder (if you install it with the defaults).

I really thought we had it then,

but that folder is empty

there not DTS - there ssis packages as its been migrated

"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

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

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

C:\Program Files\Microsoft SQL Server\90\DTS\Packages    is empty :(


this is driving me f*****g mad

i cant open that table :\   its all grayed out...

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!

On the SQL Server box - check
Program Files\Microsoft SQL Server\90\DTS\BINN\MsDtsSrvr.ini.xml

<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...

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.

yeah i can open them that way 2 :)

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

bugger.....


anyone else?

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.

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.....

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.

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....

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 ?

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