Have you ever faced the situation where you have needed to move a DTS package from one server to another, say from development to production? The typical approach might be to save it as a file from your development server, then connect to production, open the file, modify the database connection, and then save it on production. This works fairly well assuming you are only moving one or two packages and you have access to the production database server. But if you need to move multiple packages across multiple environments, this will get tedious very quickly. It can also be error prone. For example, you can miss changing a connection, or the transformations can be inadvertently reset as the package is edited.
For this reason I have made a batch file that changes the parameters of a DTS packages throught command line. The batch file looks like as follows:
IF not exist %1.dts goto badfile
CALL dtsrun /F%1 /N%1 /WTRUE /A”DB:8=%mydb%” /A”Server:8=%myserver%” /A”Outfile:8=%extractdir%\%~1.txt” > “%outdir%\%~1.txt”
IF /i %2==Y start notepad %outdir%\%~1.txt
IF /i %3==Y pause
ECHO Please provide a filename without DTS extension, followed by Y to show output, and another Y to pause before returning
ECHO %1 Completed
Now the problem is when I run this batch file on the DTS packages which are I already moved from server X to production server Y. The connections in the packages still point to the server X. Even after running the batch file. The connections are assigned global variables using Dynamic Properties tasks.