In the DTS package I am currently working on, I have to create 3 connections, but their
properties (including server, login, password) have to be decided during run time. What
options do I have to implement such a feature? Do I have to do it programmatically?

Another questions is that I have to import a list of tables (part of the data in each table)
from one sql server to another sql sever. Can I use a loop inside the package to run a task
with different table name as parameter?

I am new to DTS, thanks for any suggestions.

In article <15F53730-9CED-4AFF-8834-4C77FBEBB238 (AT) microsoft (DOT) com>, Jeffrey
<anonymous (AT) discussions (DOT) microsoft.com> writes
You can do this in an ActiveX Script Task e.g
Function Main()

Set oConn = DTSGlobalVariables.Parent.Connections("MyConnName" )
oConn.DataSource = DTSGlobalVariables("Server").Value
oConn.Catalog = DTSGlobalVariables("Database").Value

Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function

You can use a Dynamic Properties Task to read from a variety of
locations and assign the values to the connection properties.

You can also use a Data Link (Udl) connection where all the connection
information is contained with the Udl file itself.

Depending on how you execute the package you can also manipulate the
connection properties directly from the calling program. A simpler
examples to use DTSRUN and the /A option to pass in global variable
values. These can then be assigned either via an ActiveX Script Task as
above or the Dynamic Properties Task also mentioned above.

You can drive a look around one or more tasks, from pretty much anything
such as an array or a list or a recordset. The problem comes when the
tables are of different structures as this means the DataPump task needs
to be changed as encapsulated within it is the structural information
about the source and destination columns and the transformation between
them. In this case it would be better to have multiple DataPump tasks,
one for each set of structures.

Some examples of looping-

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org