DTS global variable -> DTD Sql Query Problem

• Richard-227934

  Newbie

  Points: 1

  More actions

  October 21, 2005 at 7:13 am

  #69893

  I don't know if this is possible or what im doing wrong.

  Basically i want to setup a global variable to use a bit o sql to get a date.

  This date would then be passed as a parameter to the sql query in the Data Transform Properties.

  Now i know the global variable here must be set to ? but when i click the parameter button i get error message "syantax error or access violation"

  Here is what im trying to do.

  1 ) package properties set up variable name eg. gvSQL set value to

  declare @date smalldatetime

  if getdate() > dbo.f_CurDate()

  begin

  select @date = dbo.f_CurDate()

  end

  else

  begin

  select @date = getdate()

  end

  2) Setup my transformation task properties Sql query to

  declare @test-2 smalldatetime

  select @test-2 = ?

  select Databasename = 'ERS_LDA', TableName = 'ActivityCode', CommonTableName = 'Activity', Fieldname = 'Count', FieldValue = count (*), RunDate = @date

  from f_LDA_PH_REF_ActivityCode(@test)

  Now i'm probably missing out a step or it may not even be possible so if anyone could help as im quite new to all of this that'd be great

  Thanks!

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  October 22, 2005 at 7:29 pm

  #599383

  You are doing fine and there is a klugy work-around for mapping global variables that is at http://www.sqldts.com/default.aspx?234.

  Basically, the steps are:

  1. Have a dummy sql statement that is on one line that has the desired variables. In you case, just code

  select ?

  2. Map the ? place holders to the Global Variables.

  3. Replace the dummy SQL with the real SQL.

  SQL = Scarcely Qualifies as a Language