DTS PAckage - making portable across machines. 1

[MrPeds]

Hi,

Can anybody suggest the best methodologies for making DTS packages portable across different servers?

By that, I mean, I want to be able to provide a few input paramaters to a package and then let it execute from there, I dont want to have to worry about lots of config, so was wondering what other people do to make their packages easy to transfer.

Regards,

MrPeds.

 

[JayKusch]

The use of UDL files is the way to go. In setting the UDL as the file(s) where the package obtains the connection strings, you are able to effect all the connections using that UDL from on location.

Also coding the master and subpackages to use global variable can also permit you to change connection objects on the fly.

Remember to keep all you connections set to LOCAL so that they port correctly to the destined servers.

I am also sure you know to save your DTS packages as Structured Storage Files for ease of movement from one server to the next.

Thanks

J. Kusch

 

[gradley]

All great suggestions by JayKusch! Especially the use of UDL files and global variables.

Something else to consider is the use of the "Dynamic Properties Task" (DPT). Every package we run begins with that task. It gives you the ability to soft code global variables, external file names and paths, and security information, by using values from tables stored in databases of each SQL Server environment you move the package to. You can also retrieve information from INI files using this task.

Another trick I recently learned is to soft code the Package ID and Version ID values of child packages in this (DPT} so that you don't have to re-attach child packages everytime you move from one server to the next.

Ex:

-- Script to retrieve PackageID
select p.[id] as 'Package_ID'
from msdb..sysdtspackages p
where p.[name] = @strPackageName -- Child Package
and p.createdate = 
(
Select Max(c.createDate) 
from msdb..sysdtspackages c
where p.[name] = c.[name]
)

-- Script to retrieve VersionID
select p.[versionid] as 'Version_ID'
from msdb..sysdtspackages p
where p.[name] = @strPackageName -- Child Package
and p.createdate = 
(
Select Max(c.createDate) 
from msdb..sysdtspackages c
where p.[name] = c.[name]
)

These should be entered into the PackageID and VersionID respectively for each Execute Package Task called from the parent package. Add them on separate lines in the (DPT). In other words, Click ADD to add the PackageID script first. Click ADD again to add the VersionID script.

Hope this helps!!

 

[MrPeds]

Thanks guys.

Is there any performance hit when using a UDL file as opposed to the other connections?

MrPeds

 

[JayKusch]

None! ALL PLUSES by using UDL's

Thanks

J. Kusch