January 27, 2005 at 7:53 am
I recently had to transfer about 25 DTS packages to a new server when I was setting up a development environment.
Since the servers are identical I just installed SQL Server, checked to see that it was working, shut down the services and copied the data/log files to the new server. I restarted the services and everything seemed fine. However, when I went to check the packages I got an error saying they couldn't be altered because they were created on another server. I got the same message about the jobs and was forced to delete the records directly out of the msdb..sysjobs table.
Anyway, not a big deal. I deleted all the packages and used DTSBackup to transfer the packages. Then came the annoying part....
Since server names and paths are essentially hard-coded into packages I ended up having to edit each package and manually replace each instance that named the servers. This wasn't difficult, just tedious. In fact, even when the server was specified as (local) the datasources didn't work. (Wasn't me! A consultant did that!)
What I'm wondering is whether anyone knows of a better way to do this.
Is there a way to edit/decode a structured storage file? Is there a way to get a dts package saved as a visual basic file back into SQL Server? I suppose I could have done some updating on the various jobs tables in the msdb database but that would have involved a lot of research on where various elements are stored.
It just sort of annoyed me because the whole operation was the equivalent of doing a 'replace all' in a text file.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
January 27, 2005 at 8:12 am
I often move packages across servers using visual basic.
Save the package as a visual basic file, then open it up in notepad. You can then change the connection settings within the file, using search and replace or whatever.
To import it back in, go to Enterprise Manager and highlight any package in the right hand window. Right click mouse and select @All tasks@, followed by @import@. This runs a wizard, where you can bring everything in.
In the long term, if it's going to happen regularly, you are better off setting up a .ini file and using a readconfig task in the package to set the data sources dynamically. See this article. I implemented this scenario in a previous job using it - http://www.databasejournal.com/features/mssql/article.php/3073161
January 27, 2005 at 8:29 am
I don't know what I'm missing but when I highlight a package, right-click and select 'all tasks' I just get the regular import/export wizard. Is there a way to import and export packages via this wizard?
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
January 27, 2005 at 9:13 am
Apologies. Gave you a bum steer there. I have been migrating jobs over for the last few weeks and got mixed up with them.
The way to get them back in is through a visual basic program. You need to delete the existing package and then recreate it through the code. Everything is in there that you need. if you look at the format of the .bas module, you will see it is split up into headed sections with a create package statement at the end just before the error handling.
From memory, I'm sure I was able to run it in a vba modlue in word. Just make sure you reference the 3 dts libraries:
Microsft DTS Custom tasks object library
Microsft DTSdatapump scripting onject library
Microsft DTS package object library
Also remember to set the following line:
goPackage.SaveToSQLServer "(local)", "sa", ""
replacing with your user name / password
January 28, 2005 at 10:19 am
Here's a utility that will allow you to select your storage location (server, structured storage file, etc), which packages you want to work with, select the destination, and what export method you want to use (structured storage, meta data, etc).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply