Changing a DTS connection data source does not propagate to SQL Exec Tasks

Changing a DTS connection data source does not propagate to SQL Exec Tasks

Post by Joe Ambros » Fri, 23 Mar 2001 03:09:14



I have defined a SQL Server 7.0 DTS package with one SQL Server Connection.
Lets assume the database associated with that connection is called
JOE_TEMPLATE. In this same DTS package, I have several SQL Tasks that
perform miscellaneous operations using the connection I just mentioned.

Now, in my visual basic application, I create a DTS package object and load
it from the existing DTS package above. Based on login parameters, I
dynamically change my connection's database to JOE_TESTDB. The
transformation into this connection works fine. However, all my SQL Tasks
associated with that connection still think they are pointing to database
JOE_TEMPLATE.

Its as if when I change the database of my connection, the other SQL tasks
associated with that connection fail to use that new DB name and use the
original hard-coded one.

Does anyone know if this is a bug or if there is something else I need to do
besides changing my connection's data source?

 
 
 

Changing a DTS connection data source does not propagate to SQL Exec Tasks

Post by Mike » Fri, 23 Mar 2001 06:18:32


Joe - I have had success with the following approach - this script updates
the connection properties for a data connection - can be altered to change
the server name as well.  Using this approach, all SQL Tasks utilizing this
connection are appropriately updated to use the new connection properties.

Is it possible you have a second or third database connection defined,
pointing at the same source, which the SQL Tasks are using?

' Get Package Object
Set oPKG = DTSGlobalVariables.Parent

 'LOOP THROUGH EACH CONNECTION IN THE PACKAGE
        For Each oConnection in oPKG.Connections

 'TEST FOR SQL SERVER CONNECTION
         If oConnection.Name = "Connection 2" Then

  'SET PASSWORD CONNECTION PROPERTY
  set oConnectionProperty = oConnection.ConnectionProperties("Password")
  oConnectionProperty.Value = sLoginPWD

  'SET UID CONNECTION PROPERTY
  set oConnectionProperty = oConnection.ConnectionProperties("User ID")
  oConnectionProperty.Value = sLoginUID

  'SET OTHER DATABASE PROPERTIES...

End If

 Next


Quote:> I have defined a SQL Server 7.0 DTS package with one SQL Server
Connection.
> Lets assume the database associated with that connection is called
> JOE_TEMPLATE. In this same DTS package, I have several SQL Tasks that
> perform miscellaneous operations using the connection I just mentioned.

> Now, in my visual basic application, I create a DTS package object and
load
> it from the existing DTS package above. Based on login parameters, I
> dynamically change my connection's database to JOE_TESTDB. The
> transformation into this connection works fine. However, all my SQL Tasks
> associated with that connection still think they are pointing to database
> JOE_TEMPLATE.

> Its as if when I change the database of my connection, the other SQL tasks
> associated with that connection fail to use that new DB name and use the
> original hard-coded one.

> Does anyone know if this is a bug or if there is something else I need to
do
> besides changing my connection's data source?


 
 
 

Changing a DTS connection data source does not propagate to SQL Exec Tasks

Post by Joe Ambros » Fri, 23 Mar 2001 08:09:21


I have similar logic to adjust the data source, user id, etc on my database
connection. However, something definitely gets hosed with the SQL Tasks
pointing to the same connection but not 'taking' the change in data
source...

My hack work-around right now is that I removed all my SQL tasks and
imbedded them into a ActiveX script task. Everything works fine that way.


> Joe - I have had success with the following approach - this script updates
> the connection properties for a data connection - can be altered to change
> the server name as well.  Using this approach, all SQL Tasks utilizing
this
> connection are appropriately updated to use the new connection properties.

> Is it possible you have a second or third database connection defined,
> pointing at the same source, which the SQL Tasks are using?

> ' Get Package Object
> Set oPKG = DTSGlobalVariables.Parent

>  'LOOP THROUGH EACH CONNECTION IN THE PACKAGE
>         For Each oConnection in oPKG.Connections

>  'TEST FOR SQL SERVER CONNECTION
>          If oConnection.Name = "Connection 2" Then

>   'SET PASSWORD CONNECTION PROPERTY
>   set oConnectionProperty = oConnection.ConnectionProperties("Password")
>   oConnectionProperty.Value = sLoginPWD

>   'SET UID CONNECTION PROPERTY
>   set oConnectionProperty = oConnection.ConnectionProperties("User ID")
>   oConnectionProperty.Value = sLoginUID

>   'SET OTHER DATABASE PROPERTIES...

> End If

>  Next



> > I have defined a SQL Server 7.0 DTS package with one SQL Server
> Connection.
> > Lets assume the database associated with that connection is called
> > JOE_TEMPLATE. In this same DTS package, I have several SQL Tasks that
> > perform miscellaneous operations using the connection I just mentioned.

> > Now, in my visual basic application, I create a DTS package object and
> load
> > it from the existing DTS package above. Based on login parameters, I
> > dynamically change my connection's database to JOE_TESTDB. The
> > transformation into this connection works fine. However, all my SQL
Tasks
> > associated with that connection still think they are pointing to
database
> > JOE_TEMPLATE.

> > Its as if when I change the database of my connection, the other SQL
tasks
> > associated with that connection fail to use that new DB name and use the
> > original hard-coded one.

> > Does anyone know if this is a bug or if there is something else I need
to
> do
> > besides changing my connection's data source?

 
 
 

Changing a DTS connection data source does not propagate to SQL Exec Tasks

Post by PKeega » Sat, 24 Mar 2001 03:38:44


What do your table name look like. Are the [JOE_TEMPLATE].[dbo].[Table_Name]
or are they [Table_Name]. They need to the [Table_Name]  , no reference to
the database.

Also make sure that you only have one connection defined per database.
Sometimes the wizard will create multiple connection (different names) for
the same database, in an attempt to improve operational performance.

Paul


Quote:> I have defined a SQL Server 7.0 DTS package with one SQL Server
Connection.
> Lets assume the database associated with that connection is called
> JOE_TEMPLATE. In this same DTS package, I have several SQL Tasks that
> perform miscellaneous operations using the connection I just mentioned.

> Now, in my visual basic application, I create a DTS package object and
load
> it from the existing DTS package above. Based on login parameters, I
> dynamically change my connection's database to JOE_TESTDB. The
> transformation into this connection works fine. However, all my SQL Tasks
> associated with that connection still think they are pointing to database
> JOE_TEMPLATE.

> Its as if when I change the database of my connection, the other SQL tasks
> associated with that connection fail to use that new DB name and use the
> original hard-coded one.

> Does anyone know if this is a bug or if there is something else I need to
do
> besides changing my connection's data source?

 
 
 

1. DTS Transform Data Task fails in job where DTS Execute SQL Task does not

I have experienced the following issue in several DTS packages.  I use
Transform Data Task to transfer data from a table on one SQL Server to a
table on another SQL Server.  The package executes successfully when run
manually and fails with the error shown below when run from a job.

However, if I use an insert statement in a Execute SQL Task to accomplish
the same task,  the package executes successfully when run manually and when
run from a job.

Can anyone explain why jobs running with Transform Data Task sometimes fail
as shown below?

 DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnStart:
DTSStep_DTSDataPumpTask_1   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1;
1000 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 2000
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount =
2000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000   DTSRun
OnProgress:  DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 5000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 6000   ...  Process Exit Code 1.  The
step failed.

2. How to use ADO from Windows system to connect to a Oracle database on Unix?

3. Can SQL Server CE propagate data changes to Mircosoft Access 2000

4. HELP: Images on SQL-Server 6.5 / 7.0

5. Interface Design Challenge

6. Execute SQL task (exec my_sp) pb, DTS stops without error

7. Maximum Database Size in SQL Server 7.0

8. documenting TSQL code from DTS exec SQL tasks?

9. Changing a DTS Source Connection

10. Dynamically changing DTS Conection properties does not reset Source and Destination references

11. DTS - How do I change properties of a data pump Task

12. Change source data type in DTS