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.