Hi there,
I am new DTS developer so I hope my question will be clear.
I need to transform a table but the exact DB should be a dynamic variable.
That's why I thought to use a global variable.
The problem is that when I try to change the SQL to -

Select station_code from [?].Station

I get am error.

Probably this is not the right way to do it.

So what is the right way?

Many thanks,
--
Elizabeth

Elizabeth

I will presume you are using a DataPump task (Transform Data task).
This task has two connections associated with it.

1. The Source
2. The Destination


The syntax you are using would, if it worked, be asking for a
parameterized object owner not database. The simplest way to achieve
what I think you want is to have a very plain statement against the
Source connection of

SELECT <column list> FROM <owner>.<table>

The connection which you will be using will take care of the database
side of things. Now what you need to be able to do is to change the
DataSource property of the connection. For that you can use an Active
Script task before the DataPump task and do something like this

DTSGlobalVariables.Parent.Connections("Name of Connection").DataSource =
DTSGlobalVariables("Name of variable").Value


How do you intend to pass in the value for the global variable?


HTH

Allan




"Elizabeth" <Elizabeth (AT) discussions (DOT) microsoft.com> wrote

Hi Allan,
Thank you for your quick responce.

Currently, I execute the package using the following code -

Dim MyPack As New dts.Package

MyPack.LoadFromStorageFile Fname, passwrd
MyPack.Execute

Is there any way using those functions to send the global variable?

Many thanks
--
Elizabeth


"Allan Mitchell" wrote:

Sure

If you are doing it like this then you do not need to use a Global
variable. Between the MyPack.LoadFromstorageFile and the MyPack.Execute
lines you can simly set the correct property on the connection in the
connections collection on the MyPack object.



Allan



"Elizabeth" <Elizabeth (AT) discussions (DOT) microsoft.com> wrote

Thank you - I will
--
Elizabeth


"Allan Mitchell" wrote: