Incorrect date from control table

  • richardmgreen1

    SSCrazy Eights

    Points: 9887

    Hi all


    Hoping someone can spot something obvious on this one.....


    We use a control table as we do incremental loads.  The control table stores the date/time when a document was last loaded successfully (it takes the date of the latest record to be loaded into the relevant table).

    Now, we've got an SSIS package that grabs that date via a stored procedure and passes it to the next task in the sequence.

    The SSIS package pushed this date/time into a variable (I've confirmed this is of date datatype)

    The issue we currently have is that, for some documents, although the correct last updated date/time is stored in the control table, the stored procedure that should get the correct date/time is returning 01/01/1900 (we use that as a defult date to mean load everything).

    Hopefully I've attached all the code you need.


    When we run the code to get the last updated date/time on it's own, we get the correct date/time.

    When it's run as part of an SSIS package, it sometimes returns 01/01/1900 which means that our load time is increased when it doesn't need to be as data is being loaded that hasn't changed.


    Some of the table/document names that we are having issues with are here:-












    As you can see, they are of variable lengths (and I've checked that none of them go over the length of the variable we have in the stored procedure which is a varchar(100), the longest table name is 90 characters).


    Can anyone explain why the stored procedure works for some documents/tables but not others when used in the SSIS package?



    If you need any more info, please ask.





  • Phil Parkin

    SSC Guru

    Points: 244445

    Hopefully I've attached all the code you need.

    As in 'none at all'?

    Very difficult to help without more info!

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

  • richardmgreen1

    SSCrazy Eights

    Points: 9887

    thought I'd attached the SQL but apparently the forum doesn't like it (it's been blocked due to potential security risks).


    I've renamed them to *.txt files to see if these will upload.

    You must be logged in to view attached files.
  • MVDBA (Mike Vessey)


    Points: 21757

    sounds a bit like the way c# handles null dates  - it puts 1/1/1900 in if a date comes through as null - you have to use "dbnull" instead (or the other ways rounds - I forget) … my guess is that you are sending a null value


  • richardmgreen1

    SSCrazy Eights

    Points: 9887

    In the examples I've given, I'm not passing a NULL value (or 01/01/1900), it should be picking up the correct date/time.

    If you have a look at the code I've supplied (including INSERT statements) the dates are perfectly valid and should be used.

  • Jeff Moden

    SSC Guru

    Points: 996475

    I don't know much about SSIS but, if I had to guess, I'd say that the 1900 date has absolutely nothing to do with file names.  I'd say that SSIS is having problems with passing the original date you read forward.  The fact that the code works correctly separately is kinda of proof that's true.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • richardmgreen1

    SSCrazy Eights

    Points: 9887

    Thanks Jeff


    Looks like we'll have to dig into the SSIS packages then.

    I wonder if it's a date format setting that gone a bit strange......?



Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply