Passing dynamic variable in a DTS

Question

Post reply

Passing dynamic variable in a DTS

Pradip Sahu

SSC Enthusiast

Points: 114
More actions
April 7, 2005 at 3:45 pm

#164070
I have the following code to execute in a DTS package.
The code in red doesnot execute since the DTS package resides on SERVER1
and the query looks for data from SERVER2.
Is there away I can get that value as a dynamic variable and pass it on to this query.
Some sample code will help.
Thank you.
PKS.
```
SELECT  T1.EMPLID,T1.EMPLNAME,T1.EFFECTIVE_DATE
```
```
FROM  EMPLOYEE.DBO.TBL1 T1
```
```
WHERE  T1.ORGID = 'TT'
```
```
AND T1.ENTYCODE = 'T'
```
```
AND T1.FISCAL_YR = 
```
```
(Select Fiscal_Year  
```
```
from SERVER2.Fac.dbo.Fac_Ext_Parms P1  
```
```
Where P1.Parm_Key = 1)
```

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Christop Preindl SSC Rookie Points: 38 More actions · Answer 1

Hi,

here is a dynamic way for your tsql...

declare @query varchar(4000)

declare @server varchar(50)

declare @error int

set @server = @@servername

set @query = 'SELECT T1.EMPLID,T1.EMPLNAME,T1.EFFECTIVE_DATE

FROM EMPLOYEE.DBO.TBL1 T1

WHERE T1.ORGID = ''TT''

AND T1.ENTYCODE = ''T''

AND T1.FISCAL_YR =

(Select Fiscal_Year

from ' + @server + '.Fac.dbo.Fac_Ext_Parms P1

Where P1.Parm_Key = 1)'

exec @error = sp_executesql @query

Tejal Sumeet Deshpande SSC-Addicted Points: 428 More actions · Answer 2

According to me you can not pass parameter in inline queries for DTS SQL task.

Generally the parameters is passed in query using "?" then setting input parameter from global variable.

example

Create a SQL task in DTS package.Write in following way

select t.employeeid,t.lastname,t1.territoryid

from employees t join EmployeeTerritories t1

on

t.employeeid=t1.employeeid

where

t1.territoryid=?

HTH

Calvin Lawson SSChampion Points: 11030 More actions · Answer 3

You could pass the Server Name into a proc that executes the dynamic SQL, but it's kinda flaky working with dynamic SQL in DTS.

Instead, you can dynamically change the server/database connection properties using global variables and the "Dynamic Properties Task". You can pass global variables into the proc using VB or by calling DTSRun from the windows command line (or xp_cmdshell in SQL Server). You can use dtsrunui.exe (in 80/tools/bin) to build the DTSRun command with the global variables.

cl

Signature is NULL

Pradip Sahu SSC Enthusiast Points: 114 More actions · Answer 4

I have added a global variable 'FY' to the

DTS package and used dynamic properties task to set its value. Now, how do I reference a global variable from a query? Can I have some help please?

Thank you.

PKS.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

To Call DTS:

1) use dtsrunui.exe (in 80/tools/bin under SQL Program Files) to generate a DTSRun command string. Set variable FY="default value" to generate global variable portion of command line.

2) Copy command line and set a varchar variable in proc to that value.

3) replace "default value" in string with a "server" parameter from proc (using string concatenation)

4) exectute xp_cmdshell with pre-concatenated string.

Signature is NULL