sql server dts:
I am experiencing a problem when using a .UDL file to change the SQL
server / database settings.
Scenario:
-------------
I am importing data through the use of a Text File Connection. The text
file name may vary.
The destination is a SQL 2000 server. I have a process table defined
which has the same columns (etc) but exists in multiple sep. SQL
servers.
I have a work flow defined between the Text File Connection and the SQL
server connection (which is set always use a .UDL). When setting up the
destination properites (under "Transform Data Task Properties" of the
workflow), the Table Name drop down reads: Server1.dbo.tablename. I
have an ActiveX Script which maps the database columns to the DTSSource
columns.
I created 2 global variables which specify the import text file name
and the .UDL file path\name. The global variable are located in a
Dynamic Properties Task which are executed before the Text File
Connection.
Testing:
------------
When running the task with the Server1 (set in the .UDL), everything is
fine. I can change the text file name in the global variable and
successfully import data into SQL as expected.
When changing the UDL properties to point at another server (I have
multiple servers running on the same box for testing), the importing
fails.
Specifically, when I look at the Transform Data Task Properties, the
table name drop down is still set to the Server1 name. At this point I
would expect the server name to be server1.Importtablename. This is
causing none of the destination columns to default as expected.
When looking at Dynamic Properties Task and then examing the SQL server
connections properties, the Initital Catalog and Data Source are set
correctly.
How can I map the destination table name in the Transform Data task
when the table name may change dynamically (because of the
ServerName.dbo.TableName standard)?
If anyone has experienced this, could you please give some pointers?
JB
