| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-09-21 : 08:39:05
|
| Bob writes "I ran the DTSRUN.EXE utility to copy all DTS packges (en-masse) into separate structured files. Is there a way to import all of these files (en-masse again) into a SQL Server database on another server? Other than opening each file on the other server and saving locally one by one." |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-21 : 08:42:34
|
| [url]www.sqldts.com[/url]is a very good dts resourcerockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-09-21 : 23:42:06
|
I had to do this when I upgraded a server recently.Because I am extremely lazy I wanted a quick way to do it  I restored the old MSDB database with a new name to my new server, then I just copied the rows from the sysdtspackages table into my new MSDB database.Worked great Damian |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-22 : 07:21:17
|
| You can use a linked server or the import wizard or a dts package to just copy msdb..sysdtspackages.But why bother? Just run the dts packages from structured storage files instead of msdb - it's much easier and you won't getthis probelm next time - just copy the directory.If you want to load them seehttp://www.nigelrivett.net/s_ProcessAllFilesInDir.htmlwhich processes all file in a directoryandhttp://www.nigelrivett.net/s_LoadPackageToServer.htmlWhich loads the structured storage file to sql server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
achyuth_1
Starting Member
3 Posts |
Posted - 2004-09-22 : 13:47:05
|
| Hi MerkinI have done a very similar job. I have followed the steps mentioned at http://www.sqldts.com/?204 and used DTS to transfer all the packages (all rows of sysdtspackages).Now I want to know how can I change the IP address of the server in connections in all the packages. Do I have to open each one of them and do it or is there a better way?Thanks |
|
|
|
achyuth_1
Starting Member
3 Posts |
Posted - 2004-09-22 : 14:04:41
|
| Forgot to mention...I wanted to know how to change the IPAddress, Username & Password of the connections when I transfer all the packages from one server to the other.[I have transferred all the rows of sysdtspackages, now whats the next step?]Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-22 : 17:34:46
|
| The best way is not to hard code server names, ip addresses ... in the packages.Get them from global variables which you set in the load of the package.You can get round it by creating a loader which loads the package, loops through the tasks and sets the relevant things then runs it but you are better off usig the global variables while you have to do this.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
achyuth_1
Starting Member
3 Posts |
Posted - 2004-09-27 : 14:02:14
|
| Can you please explain me how can I make the IPAddress, Login/password as global variables and use them in the connections of my packages.Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-27 : 19:51:26
|
| This sort of thing is one wayhttp://www.nigelrivett.net/SetDTSRunTimeValues.htmlBut look at the dynamic properties task - it probably allows you to set it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Importing DTS Packages en-masse into a different Server