Hi, I'm new to DTS. I need to create a package that transforms from a text
file to a SQL Database. The problem is that I have to change the connection
to a different database in the same server depending on the value of a
global variable.
The package has a ActiveX Script Task, a Text File Connection, a Transform
data Task and a SQL Server Connection.

The code for the ActiveX Script is:

Option Explicit
Function Main()
' Declare Variables
Dim oPKG
Dim oConnection
DTSGlobalVariables("gvCatalog").Value = "data2"
' Get Package Object
Set oPKG = DTSGlobalVariables.Parent
' Examine Connections
For Each oConnection in oPKG.Connections
'Test for SQL Server Connection
If oConnection.ProviderID = "SQLOLEDB.1" Then
'Set new Server (DataSource) value
oConnection.DataSource = "smart1"
'Set new Database (Catalog) value
oConnection.Catalog = DTSGlobalVariables("gvCatalog").Value
End If
Next
' Clear Up
Set oConnection = Nothing
Set oPKG = Nothing

Main = DTSTaskExecResult_Success
End Function
This is'nt working.... any ideas?
Thanks in advance for your help... its kind of "urgent"
JC

When you say "Isn't working" I am going to presume that the destination
object still stays qualified by the name of the original DB ? In
Disconnected Edit blank it out. so say yours says

Pubs.dbo.Table1

change it to Table1


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Juan C. Santaella" <santaell (AT) telcel (DOT) net.ve> wrote

Allan, I went to Disconnected Edit and in the DTSTask_DTSDatapump_Task1
node, made the changes to the "DestinationObjectName property to [Table1].
Then I executed the package and received a "Succefully completed execution
of package" message, but when I check the database table there is no data
on it. What´s missing?
Thank you for your help.
JC


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Mak sure you have set the correct server and DB for the destination
connection as well.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Juan C. Santaella" <santaell (AT) telcel (DOT) net.ve> wrote