Re: Accessing DTS Global Variables (please help!)???

[TechDork]

"TechDork" <Techus@Dorkus.com> wrote:
>
>How can I access global variables from a Transform Data Task - in the sql
>from the Source query?
>
>Thanks very much!!


It appears that these global variables can only be accessed inside ActiveX
Tasks :-(

Is this true?? If so, is there anyway I can pass a parameter to my Data Transformation
Task from my ActiveX Script Task? I want to pass a table name so I can dynamically
build the sql in my Source Query in the Data Transformation Task.

Like...

Select * From <table value> where field > 1

Please help!!!

[Q*Bert]

>Like...
>
>Select * From <table value> where field > 1

Don't know if this applies but you could use the execute method and concat
the string together to issue the SQL.

Exec "SELECT * FROM @Variable Where Field > 1"

[Some Dude]

If you are using an "Execute SQL Task" you can do the following:

1. Double Click the task to open the task properties.
2. Click the Parameters button at the bottom of the property page.
3. On the "Input Parameters" tab, Select the global variable you
want to use from the dropdown in the "Input Global Variables"

column. Click OK. Note: The order you add the parameters in
this grid is the order your task will read them.
4. In your SQL statement you will access the parameters with question

marks as place holders. I use Stored Procedures and pass the
variables as parameters.

ex: EXECUTE st_SomeProc ?, ? --st_SomeProc has two parameters

I hope this will help, I believe that you do it in a similar way in the "Transform
Data Task".


Some Dude
-------------------------------------------------------------------------





"TechDork" <Techus@Dorkus.com> wrote:
>
>"TechDork" <Techus@Dorkus.com> wrote:
>>
>>How can I access global variables from a Transform Data Task - in the sql
>>from the Source query?
>>
>>Thanks very much!!
>
>
>It appears that these global variables can only be accessed inside ActiveX
>Tasks :-(
>
>Is this true?? If so, is there anyway I can pass a parameter to my Data
Transformation
>Task from my ActiveX Script Task? I want to pass a table name so I can dynamically
>build the sql in my Source Query in the Data Transformation Task.
>
>Like...
>
>Select * From <table value> where field > 1
>
>Please help!!!
>

[TechDork]

Thanks, some dude, but I get the following error when clicking the "parameters..."
button: "Provider cannot derive parameter information". This connection uses
a odbc dsn for fox pro.


Maybe I should try another approach?? Basically, I have about 800 Visual
Fox Pro tables that need to be accessed and transfered to one table in sql
server. Any ideas how I should go about doing this? Btw, these fox pro tables
reside in directories on a server on our network. Each folder represents
a branch for which data needs to be transffered, so I thought I would create
an ActiveX Script task to loop through all the directories and then, somehow,
transfer each branch's data, one by one.

Can someone pllllllllllleaaaaaaaaaaaaaaase helP??

Thanks

"Some Dude" <some@dude.com> wrote:
>
>If you are using an "Execute SQL Task" you can do the following:
>
> 1. Double Click the task to open the task properties.
> 2. Click the Parameters button at the bottom of the property page.
> 3. On the "Input Parameters" tab, Select the global variable you
> want to use from the dropdown in the "Input Global Variables"

>
> column. Click OK. Note: The order you add the parameters in
> this grid is the order your task will read them.
> 4. In your SQL statement you will access the parameters with question
>
> marks as place holders. I use Stored Procedures and pass the
> variables as parameters.
>
> ex: EXECUTE st_SomeProc ?, ? --st_SomeProc has two parameters
>
>I hope this will help, I believe that you do it in a similar way in the
"Transform
>Data Task".
>
>
>Some Dude
>-------------------------------------------------------------------------
>
>
>
>
>
>"TechDork" <Techus@Dorkus.com> wrote:
>>
>>"TechDork" <Techus@Dorkus.com> wrote:
>>>
>>>How can I access global variables from a Transform Data Task - in the
sql
>>>from the Source query?
>>>
>>>Thanks very much!!
>>
>>
>>It appears that these global variables can only be accessed inside ActiveX
>>Tasks :-(
>>
>>Is this true?? If so, is there anyway I can pass a parameter to my Data
>Transformation
>>Task from my ActiveX Script Task? I want to pass a table name so I can
dynamically
>>build the sql in my Source Query in the Data Transformation Task.
>>
>>Like...
>>
>>Select * From <table value> where field > 1
>>
>>Please help!!!
>>
>