Passing dynamic variable in a DTS

[prakus]

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 = [COLOR=red](Select Fiscal_Year
		from SERVER2.Fac.dbo.Fac_Ext_Parms P1
		Where P1.Parm_Key = 1)[/color]

 

[JamesLean]

Have you set up server2 as a linked server? This should make your query work.

--James

 

[prakus]

James,
I apolozise for an error. Here is the corrected scenario. The DTS package resides on SERVER1. The code in red doesnot execute since the datasource is set to SERVER2 where the EMPLOYEE database resides and the query looks for data from SERVER1. Is there a way I can get the fiscal_year value as a dynamic variable and pass it on to this query. Some sample code will help.

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 = [COLOR=red](Select Fiscal_Year
        from SERVER1.Fac.dbo.Fac_Ext_Parms P1
        Where P1.Parm_Key = 1)[/color]

 

[JamesLean]

OK, can you set up server1 as a linked server on server2 then?

If not, add a global variable to your DTS package, use a dynamic properties task to set its value and then reference that from your query.

--James

 

[prakus]

James,
SERVER1 is already set as a linked Server on SERVER2. Still the query doesnot get parsed. The exact error is something like "Deferred prepare could not be completed." OLE DB provider 'SERVER1' doesnot contain table ""Fac"."dbo"."Fac_Ext_Parms"". The table either does not exist or the current user doesnot have permission on that table...

For the other option you mentioned(global variable using a query), could you please give me some sample code or the steps for the same.

Thanks again.
PKS

 

[prakus]

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.