Using global variable in Data Transform Task (SQL Server 2000)

[TheEvilDonut]

Hello.

I have a Data Transform Task with the following in the SOURCE tab :

-----
declare @sql varchar(4000), @servername sysname
SET @servername = 'ORJ0X01'
SET @sql = 'select ora.* from OPENrowset(''MSDAORA'',' + '''' + @servername
+ '''' + ';'''';'''',''SELECT SUBSTR(NO_DISTRICT, 1, 4) AS NO_DISTRICT,
SUBSTR(NO_DETAILLANT,1, 6) AS NO_DETAILLANT, SUBSTR(NOM_DETAILLANT, 1, 30)
AS NOM_DETAILLANT, NO_SUCCURSALE, ADR_NO_MUNICIPAL_LIVR AS ADR_NO_CIVIQUE,
ADR_TYPE_RUE_LIVR AS ADR_TYPE_RUE, ADR_NOM_RUE_LIVR AS ADR_NOM_RUE,
ADR_POINT_CARDINAL_LIVR AS ADR_POINT_CARDINAL, ADR_TYPE_UNITE_LIVR AS
ADR_TYPE_UNITE, ADR_NO_UNITE_LIVR AS ADR_NO_UNITE, ADR_POSTALE_LIVR AS
ADR_POSTALE, SUBSTR(NOM_VILLE_ADR, 1, 32) AS ADR_VILLE,
SUBSTR(TEL_CODE_REGIONAL, 1, 3) AS TEL_CODE_REGIONAL, SUBSTR(TEL_NO, 1, 7)
AS TEL_NO, SUBSTR(NOM_CONTACT_MOP, 1, 32) AS NOM_CONTACT_MOP,
SUBSTR(COALESCE(FREQUENCE_VISITE,''''0''''), 1, 1) AS FREQUENCE_VISITE,
SUBSTR(COALESCE(JOUR_VISTE,''''00''''), 1, 2) AS JOUR_VISTE,
COALESCE(SUBSTR(TO_CHAR(SEQUENCE_VISTE, ''''09''''), 2, 2),''''00'''') AS
SEQUENCE_VISTE, COALESCE(SUBSTR(MODE_PAIEMENT, 1, 1),''''0'''') AS
MODE_PAIEMENT, ''''O'''' AS IND_TERM_FACT FROM DDMO.VEXE_MOP_RD_DETAILLANT
ORDER BY NO_DISTRICT, NO_DETAILLANT'') ora inner join BRANCHOFFICE sql on
ora.NO_DISTRICT = sql.DISTRICT'

execute(@sql)
-----

I would like to assign @servername with the contents of a global variable
that I initialized previously with a Dynamic Properties Task.

Can anyone tell me how I would go around doing that?

Thank you!

[matteog]

Post by TheEvilDonut
Hello.
-----
+ '''' + ';'''';'''',''SELECT SUBSTR(NO_DISTRICT, 1, 4) AS NO_DISTRICT,
SUBSTR(NO_DETAILLANT,1, 6) AS NO_DETAILLANT,  SUBSTR(NOM_DETAILLANT, 1, 30)
AS NOM_DETAILLANT,  NO_SUCCURSALE,  ADR_NO_MUNICIPAL_LIVR AS ADR_NO_CIVIQUE,  
ADR_TYPE_RUE_LIVR AS ADR_TYPE_RUE,  ADR_NOM_RUE_LIVR AS ADR_NOM_RUE,  
ADR_POINT_CARDINAL_LIVR AS ADR_POINT_CARDINAL,  ADR_TYPE_UNITE_LIVR AS
ADR_TYPE_UNITE, ADR_NO_UNITE_LIVR AS ADR_NO_UNITE,  ADR_POSTALE_LIVR AS
ADR_POSTALE,  SUBSTR(NOM_VILLE_ADR, 1, 32) AS ADR_VILLE,  
SUBSTR(TEL_CODE_REGIONAL, 1, 3) AS TEL_CODE_REGIONAL,  SUBSTR(TEL_NO, 1, 7)
AS TEL_NO,  SUBSTR(NOM_CONTACT_MOP, 1, 32) AS NOM_CONTACT_MOP,  
SUBSTR(COALESCE(FREQUENCE_VISITE,''''0''''), 1, 1) AS FREQUENCE_VISITE,  
SUBSTR(COALESCE(JOUR_VISTE,''''00''''), 1, 2) AS JOUR_VISTE,  
COALESCE(SUBSTR(TO_CHAR(SEQUENCE_VISTE, ''''09''''), 2, 2),''''00'''') AS
SEQUENCE_VISTE,  COALESCE(SUBSTR(MODE_PAIEMENT, 1, 1),''''0'''') AS
MODE_PAIEMENT,  ''''O'''' AS IND_TERM_FACT FROM DDMO.VEXE_MOP_RD_DETAILLANT
ORDER BY NO_DISTRICT, NO_DETAILLANT'') ora inner join BRANCHOFFICE sql on
ora.NO_DISTRICT = sql.DISTRICT'
-----
that I initialized previously with a Dynamic Properties Task.
Can anyone tell me how I would go around doing that?
Thank you!

Hi, i think you're misunderstanding DTS variables with T-SQL
variables.
With Dynamic Properties task you can change (and assign) the value of
a "property" of a task not the value of a T-SQL variable inside a
script.
Therefore you can change the value of script (ie, all the content of
the SQL Query pane in Source Tab of the Transformation Data task) but
not the value of the @server variable inside that script.

To accomplish what you want to do i would rather use an ActiveX to
build a dynamic sql batch. It wuold read the Servername from a Global
Variable and then combine/concatenate it in the rest of your t-sql
batch script (ie, from "declare @sql varchar [...]" to "[...]
BRANCHOFFICE sql on ora.NO_DISTRICT = sql.DISTRICT' "), and then
assign it to the property SourceSQLStatement of your Transform Data
Task either using the same ActiveX or with a separate Dynamic
Properties Task.

HTH,
M.