November 8, 2019 at 3:00 pm
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
November 8, 2019 at 3:10 pm
Hopefully I've attached all the code you need.
As in 'none at all'?
Very difficult to help without more info!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 8, 2019 at 3:29 pm
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
November 11, 2019 at 9:19 am
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.
November 11, 2019 at 1:26 pm
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
Change is inevitable... Change for the better is not.
November 11, 2019 at 1:46 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply