March 15, 2006 at 6:55 am
I'm getting ready to migrate our SQL Server 2000 production environment to a new server. Not being a DBA, I'm not 100% confident that I've explored all the possible avenues to accomplish one of my main goals, which is to migrate the DTS packages from one server to another without having to manually modify the connection settings of the packages and the data mappings.
I'm a little concerned with the level of effort required to manually update the various DTS packages with the new server connection information. I've looked at various migration options, but I don't believe any achieve the results I was hoping for.
I've looked at saving the DTS packages as VBScript files, which would allow replacement of connection information via a text search and replace. But I don't see a way to import the script file back as a DTS package.
Simply opening the DTS package and doing a 'Save As' to the new server does not update the connection information.
Saving the DTS package as a dts script file results in a binary file that cannot be changed.
Is there a way to update the connection information during a DTS/Server migration?
March 15, 2006 at 10:31 am
There are few ways to do this, and if you search the forum, you'll find quite a few responses.
You need to make an executable out of the vb script after editing it to "save" in visual studio 6.0. I forget the exact reference it requires. You can find instruction how to do this on MSDN.
Personally, I prefer to use the .DTS file. I have multiple client servers running the same dts. I use DTSRUN.exe in a job to execute it. If I need to make changes, I open it on my test server, edit it, and then copy the file to the clients. Make sure to over-write the old .DTS file (delete then save), because it will archive the old file otherwise.
March 16, 2006 at 5:30 am
We have used global variables as a way of specifiying our connections in our DTS packages. So when we migrate from one server to another, it's just a matter of amending the global variable.
March 16, 2006 at 7:31 am
Check out http://www.sqldts.com/ for info on making packages more portable. Also, check out the Disconnected Edit feature. It makes it much easier to change some DTS settings like connection objects, etc.
March 16, 2006 at 12:01 pm
"I've looked at saving the DTS packages as VB files, which would allow replacement of connection information via a text search and replace. But I don't see a way to import the script file back as a DTS package. "
The file that DTS creates when you save as VB is not really the actual package. It is a program that will create the package object and then add all of the steps, connections, and other objects to the package.
By default, the VB program, when executed, will create the packagem then execute it using the following code found somewhere in the middle of the VB prog:
-------------------------8<------------------------------------------
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothing
set goPackageOld = Nothing
End Sub
----------------------8<--------------------------------------------------
SO, to save the package, comment the Execute method call, uncomment the SaveToSQLServer method call, then fill in the blanks. When you execute this VB, it will create the package, then save it on the SQL server.
The bad news is that all of the visual formatting in the package is lost. So, if you like all of the objects lined up so the package looks pretty, you will be disappointed with the results.
jg
March 16, 2006 at 12:05 pm
Rats. I forgot to mention: It is probably less work to add a dynamic properties task to each package and set the server connections from within an INI file. This you can accomplish before the migration, so you change one INI file on cutover date and be done.
jg
March 21, 2006 at 10:49 am
Sorry to be so VB stupid, but I have this same problem. Some of my packages that I've inherited contain more than 20 connections, none of them use an INI file, they are all embedded connection objects.
I was a developer in a former life, but that was back with VB 4.0. I have Visual Studio .Net, and have no idea how to get the .bas module to run (I keep getting 'Statement is not valid in Namespace' errors). A little more help would be appreciated.
VB Stupid and not afraid to admit it!
TIA,
-A.
March 21, 2006 at 12:19 pm
The .bas module is Visual Basic. You have Visual Basic.Net, which is a somewhat different language. You'll need Visual Basic for the module to run, unless the "upgrade wizard" will work.
jg
March 22, 2006 at 12:27 pm
I think if you open a new windows application and paste the .bas code into it, making the appropriate references to the DTS Com components, it should be close to runnable. It does seem to have a problem with oConnection.ConnectionProperties, though, which I don't know how to resolve.
The references you need are DTS, DTSCustTasks and DTSPump -- They are all listed like "Microsoft DTS Object Library" in the COM tab of Add References.
Does anyone else know how to handle the oConnection.ConnectionProperties problem?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply