Incorrect date from control table

  • richardmgreen1

    SSCrazy Eights

    Points: 9760

    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:-

    wtbl_MT_EMR_Document_Amu_Discharge_Summary_GENAMUDSUM0

    wtbl_MT_EMR_Document_Amu_Discharge_Summary_GENAMUDSUM10

    wtbl_MT_EMR_Document_Apgar_NEONBPC1

    wtbl_MT_EMR_Document_Drug_Cupboard__Amu_Tto_GENAMUDSUM2

    wtbl_MT_EMR_Document_Medications_And_Medical_Device_GENMT1_1

    wtbl_MT_EMR_Document_Medications_And_Medical_Device_GENMT1_2

    wtbl_MT_EMR_Document_Medications_And_Medical_Device_PAEDMMD_1

    wtbl_MT_EMR_Document_Medications_And_Medical_Device_PAEDMMD_2

    wtbl_MT_EMR_Document_Mortality_Review_MORREVIEW

    wtbl_MT_EMR_Document_Neo_Adm_Summ_Delivery_Details_Background_NADMDEL

     

    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.

     

    TIA

     

    Richard

  • Phil Parkin

    SSC Guru

    Points: 243772

    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.

  • richardmgreen1

    SSCrazy Eights

    Points: 9760

    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.

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

    SSC-Insane

    Points: 21020

    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

    MVDBA

  • richardmgreen1

    SSCrazy Eights

    Points: 9760

    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: 994867

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • richardmgreen1

    SSCrazy Eights

    Points: 9760

    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......?

     

    Richard

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

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