How to find out why a column from Source won't be inserted in Target
-
Hi there,
so I have the following problem: I have a Dataflow which picks up FILE_DATE (bigint) from SourceTableA and should be inserted into TargetTableB. Within the Dataflow this Column isn't touched at any point, if I enable the DataReader just before the OLEDB Destination, he correctly shows me the FILE_DATE Column having Data as expected.
Once the package completes - without any errors, redirected rows, truncation or something else - the FILE_DATE Column on TargetTableB is empty. From this point, how would I investigate further why FILE_DATE isn't added to TargetTableB? in the OLEDB Source I'm reading in that Column as DT_I8 datatype and I'm passing it as the same datatype to the Destination.
- DinoRS - Tuesday, October 16, 2018 6:59 AM
Are you absolutely certain that the datatype is BIGINT in both source and target? Why are dates being stored in non-date format? Are source and target both SQL Server?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
The decision to store a date in bigint format was not mine, I basically just took this to complete whoever before me started this, so the why question is the same I have for myself however I want to be done with this, not start discussions with management why something has been decided that way ages ago. Not are both source and target on the same server only, they're even within the same database.
You cannot imagine how many times I've looked at it to confirm for myself that both are bigint, null ... and that from source to destination it travels through the whole data path which it does including showing up on the Data Reader just ahead of the destination.
but looking at the table once it completed, there is none of it, any number of rows has for FILE_DATE a NULL in there, at least it's consistent I guess.
- DinoRS - Tuesday, October 16, 2018 7:35 AMThe decision to store a date in bigint format was not mine, I basically just took this to complete whoever before me started this, so the why question is the same I have for myself however I want to be done with this, not start discussions with management why something has been decided that way ages ago. Not are both source and target on the same server only, they're even within the same database.
You cannot imagine how many times I've looked at it to confirm for myself that both are bigint, null ... and that from source to destination it travels through the whole data path which it does including showing up on the Data Reader just ahead of the destination.
but looking at the table once it completed, there is none of it, any number of rows has for FILE_DATE a NULL in there, at least it's consistent I guess.
If they're in the same database, why are you using SSIS? Just call a proc to do it.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Phil,
pretty simple: I'm not going to rewrite the whole package someone started 2+ years ago. The Table is working within the solution and was completed as such. All I did and want to do is pass a FILE_DATE from Source to Target table as this will be the only thing I can use to delete unneeded data from this table before the next load again. The logic for the DELETE is in place, too. It just refuses to work when you go for a YEAR(FILE_DATE) when all values are null. - DinoRS - Tuesday, October 16, 2018 9:54 AM
The YEAR() function expects a date, time or datetime variable as its argument, not a BIGINT, so that is never going to work. To get the year from such a column, do something like this:
DECLARE @x BIGINT = 20181016;SELECT
@x
, XYear = @x / 10000;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
DELETE FROM dbo.Table
WHERE (YEAR(CONVERT(datetime, CONVERT(char(8), FILE_DATE))) = YEAR(GETDATE()))works pretty well. I know this is far from a nice solution but I'm not designing something brand new here nor do I have the time to look through everything right now as it's supposed to be in production tomorrow. From my perspective if the date should be stored as a numeric value, a int Column should've been sufficient.
I did solve my issue, to solve this I simply added a Data Conversion Task that Converts the incoming bigint to (DT_I8)([FILE_DATE]) and I see a FILE_DATE in the target table appearing. I did run a Profiler session beforehand and didn't notice anything statement wise that would've caught my attention.
- DinoRS - Wednesday, October 17, 2018 4:18 AM
Your query is non-SARGable and will not be able to use any available indexes. Here is a better way, avoiding unnecessary data conversions:
DECLARE @Start BIGINT = YEAR(GETDATE()) * 10000 + 101;
DECLARE @End BIGINT = YEAR(GETDATE()) * 10000 + 1231;DELETE FROM dbo.table
WHERE
FILE_DATE BETWEEN @Start AND @End;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Phil,
there is no index on this column as the only thing ever querying FILE_DATE will be SSIS to check wether to delete records or not.Regardless of that, your solution is faster and working so thank you!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply