DTS --> Global Variables of type DATE

• Torsten Radl

  Newbie

  Points: 8

  More actions

  August 25, 2004 at 4:11 am

  #60205

  Hi,

  I've some problems to schedule my jobs.

  I'm using global variables with option explicit.

  When I use the data type DATE the scheduled job doesn't work.

  As soon as I use datatype DATE in my global variables it doesn't work anymore.

  My variables are initialized in a valid format.

  Any suggestions ?

  (By the way it works with String instead of DATE)

  Is this a common bug ?

  Greats THOR

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  August 25, 2004 at 5:02 am

  #520476

  Can you post a snippet of the code that is failing with the global variables so we can attempt to help you.  It sounds like a data-type conversion issue but without seeing some of the code that is not working (assuming DTS package starts) it will be hard to diagnose.

   

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Torsten Radl

  Newbie

  Points: 8

  More actions

  August 25, 2004 at 6:36 am

  #520494

  Hi,

  thx for help.

  But I think the DTS package does not start bcause of the error

  message

  Job Outcome (Step 1 Failed)

  Is this because of the data-type conversion issue you spoke about ?

  Variables are tried to initialize and this fails ?

  Anyway:

  Here is a pic of the global variable declaration and the snippet of code:

  LastMonth

  MaxHistory

  Timeakt

  TimeHistory

  have been of data-type DATE.

  the Job schedule was created by right-clicking the package.

  package worked well when started manual (not scheduled).

  so whats the difference between starting manual and (try)

  to start by a job schedule ?

  

  

  some Code:

  '**********************************************************************

  '  Visual Basic Transformation Script

  '************************************************************************

  '  Copy each source column to the destination column

  Function Main()

  Dim NewValue

   if ( CInt(DTSSource("KennzahlID"))  = CInt(DTSGlobalVariables("KennzahlID").value) _

   and CInt(DTSSource("KostenID")) = CInt(DTSGlobalVariables("KostenID").value) _

   and CInt(DTSSource("OrgID")) = CInt(DTSGlobalVariables("OrgID").value) _

   and CInt(DTSSource("LandID")) = CInt(DTSGlobalVariables("LandID").value) _

   and CInt(DTSSource("SourceID")) = CInt(DTSGlobalVariables("SourceID").value) _

   and DTSSource("TimeHistory") = DTSGlobalVariables("TimeHistory").value _

   and DTSSource("Timeakt") = DTSGlobalVariables("Timeakt").value) then

       

    DTSDestination("Mark")=1

   else

    DTSDestination("Mark")=0

   end if

   

   if ( LandID <> CInt(DTSGlobalVariables("LandID").value)  _

   or CInt(DTSSource("KennzahlID"))<>CInt(DTSGlobalVariables("KennzahlID").value) _

   or CInt(DTSSource("KostenID"))<> CInt(DTSGlobalVariables("KostenID").value) _

   or CInt(DTSSource("SourceID")) <>CInt(DTSGlobalVariables("SourceID").value) _

   or DTSSource("TimeHistory") <> DTSGlobalVariables("TimeHistory").value _

   or Month(DTSSource("Timeakt"))= 12 _

   or CInt(DTSSource("OrgID")) <> CInt(DTSGlobalVariables("OrgID").value) ) then

    DTSGlobalVariables("NewWert").value = 0

   end if

   NewValue= DTSSource("Wert") - DTSGlobalVariables("NewWert").value

   

   DTSGlobalVariables("NewWert").value  =DTSSource("Wert")

   DTSGlobalVariables("KennzahlID").value  =DTSSource("KennzahlID")

   DTSGlobalVariables("KostenID").value =DTSSource("KostenID")

   DTSGlobalVariables("OrgID").value =DTSSource("OrgID")

   DTSGlobalVariables("LandID").value =DTSSource("LandID")

   DTSGlobalVariables("SourceID").value =DTSSource("SourceID")

   DTSGlobalVariables("TimeHistory").value =DTSSource("TimeHistory")

   DTSGlobalVariables("Timeakt").value =DTSSource("Timeakt")

   DTSDestination("kfmDataID")=DTSSource("kfmDataID") 

   DTSDestination("KennzahlID")=DTSSource("KennzahlID") 

   DTSDestination("SourceID")=DTSSource("SourceID")

   DTSDestination("KostenID")=DTSSource("KostenID")

   DTSDestination("OrgID")=DTSSource("OrgID")

   DTSDestination("LandID")=DTSSource("LandID")

   DTSDestination("SourceID")=DTSSource("SourceID")

   DTSDestination("TimeHistory")=DTSSource("TimeHistory")

   DTSDestination("Timeakt")=DTSSource("Timeakt")

   DTSDestination("Wert")=NewValue

   Main = DTSTransformStat_OK

  End Function

   

   

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  August 25, 2004 at 5:33 pm

  #520628

  Ok, this is what I would try,

  1. change the fields that you are thinking are bombing due to DATE to CHAR where you know the code works when you run it manually (BTW have you tried to run it manually with the vars as DATE??)
  2. Schedule the job to run as YOU and see if the schedule fails..
  3. Schedule the job to run as sa and see if the schedule fails...

  I am thinking that there is a permissions problem and not a code problem.  Make sure that the user that runs the job has rights to do what the 1st step does

   

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net