DTS problems after changing server

• Danie Coetzee

  SSC Journeyman

  Points: 86

  More actions

  October 16, 2007 at 7:33 am

  #178539

  We have just move from one server to another. Now I have the following problem:

  My DTS's will not run through SQL Query Analyzer, or my Visual Studio front ends, but they run fine in the designer and DTSRUNUI.

  When I run one DTS in SQL Query Analyzer using a SP, I get errors regarding date calculations.

  Please help - I am almost totally bald now...:w00t:

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  October 16, 2007 at 7:42 am

  #742358

  I wondering what errors you're getting in date calculations...:cool:

  May be you can have a look at your server's default date/time settings...

  --Ramesh
  

  ---

• Danie Coetzee

  SSC Journeyman

  Points: 86

  More actions

  October 16, 2007 at 11:33 pm

  #742591

  I wish it was as simple as the date/time settings.

  Here is my code:

  if DTSSource("ProcDate") > " " then

  i_Day = (Mid(DTSSource("ProcDate").Value ,7 , 2 ))

  i_Month = (Mid(DTSSource("ProcDate").Value ,5 , 2 ))

  i_Year = (Left(DTSSource("ProcDate").Value,4))

  end if

  DTSDestination("ProcessingDateDate").Value=DateSerial( i_Year , i_Month , i_Day)

  i_Day = (Mid(DTSDestination("ProcessingDateDate").Value ,9 , 2 ))

  i_Month = (Mid(DTSDestination("ProcessingDateDate").Value ,6 , 2 ))

  i_Year = (Left(DTSDestination("ProcessingDateDate").Value,4))

  if i_Day > "15" then

  i_Day = "15"

  DTSDestination("MonthEndDate").value=DateSerial( i_Year , i_Month , i_Day)

  DTSDestination("MonthEndDate").value = DateAdd( "M", 1, DTSDestination("MonthEndDate") )

  else

  i_Day = "15"

  DTSDestination("MonthEndDate").value=DateSerial( i_Year , i_Month , i_Day)

  Procdate = yyyymmdd (20071017)

  ProcessingDateDate and MonthEndDate are defined as smalldatetime.

  The error I get is that i_Month is invalid in the last line of code

• mrpolecat

  SSCertifiable

  Points: 7014

  More actions

  October 17, 2007 at 2:13 pm

  #742881

  if you msgbox(DTSDestination("ProcessingDateDate").Value) after you create it what do you get?

  ---