February 11, 2015 at 10:02 am
I have an AS400 table where the DATE (YYYY-MM-DD) is separate from the TIME (HH24:MM:SS). I pull into a work file with the two fields still separate.
I concatenate the two fields from the work file into one DATETIME field before I insert into the final destination SQL table. I use this statement to do this:
cast(cast(WF.BT_TRAN_DATE as datetime) + WF.BT_TRAN_TIME as datetime) as TransactionDatetime
When I go back to the AS400 table to get new records, I want to find the MAX TransactionDatetime on my final destination SQL table, assign it to a variable, and then use that variable in my query against the AS400 table (again, keep in mind that the fields are broken out on that table).
I was thinking the best approach might be to convert both sides of the comparison to the string format YYYYMMDDHHMMSS and then convert this to an BIGINT
So in my variable assignment SQL task, I have this code:
SELECT CAST(REPLACE(REPLACE(REPLACE(convert(varchar, max(TransactionDatetime),120), '-', ''), ' ', ''), ':', '') as bigint)
FROM table
Then, in my DB2 query in the data flow task, I have this:
WHERE BIGINT(VARCHAR_FORMAT(timestamp(char(BT_TRAN_DATE) || ' ' || char(BT_TRAN_TIME)), 'YYYYMMDDHH24MISS')) > ?
But I am having all sorts of issues with this and any other method I can think of.
Can anyone help?
February 11, 2015 at 10:34 am
This is a lot of messing about! I'm not very familiar with AS/400 databases, but I have an idea which might at least make things work for you – it allows you to stop worrying about filtering on times.
ExecuteSQL
1) Set MaxDate = max(date) from target table (ignore time)
Data Flow
2) (Data flow source data selection) Select [columns] from source where date >= MaxDate
– this will get all new rows and some existing rows
3) (Lookup transformation – full cache)
(Lookup source) select Id from target where date >=MaxDate
(Id is whatever your PK is)
– Match incoming data against the lookup based on Id. Ignore the 'Matched' output and instead send your Unmatched output to insert into your target table.
February 11, 2015 at 11:12 am
Ok, I am thinking this through and it might work. Does it make a difference if the datetime is part of my PK? The key on my table is a CustomerID, TransactionID (across the customer not the the table unfortunately), and TransactionDatetime. The transactions can be updated, which then changes the date and time fields. I am trying to capture the original records and the changes as well.
The problem is that the source and the destination tables are very large (13 million plus) and I was trying to avoid a LOOKUP if possible.
This is what I tried more recently:
Execute SQL Task with
SELECT convert(varchar, max(TransactionDatetime), 121)
FROM DestinationTable
Populating a STRING variable @MaxDate
Then, I use map the variable @MaxDate on the Data Flow Task Source against AS400 in the query:
WHERE timestamp(BT_TRAN_DATE, BT_TRAN_TIME) > ?
I figured out that the date field was a true DATE on the source, and the time was a true TIME.
It appears to be working now (at least it does not break). But I would like someone to tell me that this is the way to do it.
I can test your way and see how the performance is as well.
PK
February 12, 2015 at 1:07 am
paul.j.kemna (2/11/2015)
Ok, I am thinking this through and it might work. Does it make a difference if the datetime is part of my PK? The key on my table is a CustomerID, TransactionID (across the customer not the the table unfortunately), and TransactionDatetime. The transactions can be updated, which then changes the date and time fields. I am trying to capture the original records and the changes as well.The problem is that the source and the destination tables are very large (13 million plus) and I was trying to avoid a LOOKUP if possible.
This is what I tried more recently:
Execute SQL Task with
SELECT convert(varchar, max(TransactionDatetime), 121)
FROM DestinationTablePopulating a STRING variable @MaxDate
Then, I use map the variable @MaxDate on the Data Flow Task Source against AS400 in the query:
WHERE timestamp(BT_TRAN_DATE, BT_TRAN_TIME) > ?I figured out that the date field was a true DATE on the source, and the time was a true TIME.
It appears to be working now (at least it does not break). But I would like someone to tell me that this is the way to do it.
I can test your way and see how the performance is as well.
PK
Your concerns about the lookup should not be an issue, as I was suggesting only a filtered lookup (and not against the entire table).
However, the fact that the datetime is part of the PK is a factor. Obviously, you need to get the conversion from AS/400 to SQL Server exactly correct in all cases in order for this to work.
I would have expected there to be some other EventId column as the PK. Your current design has the (no doubt small) possibility of >1 customer/transaction occurring at exactly the same time --> error.
In most systems I've encountered, transactions cannot be modified. Instead they are reversed/added to/adjusted by further transactions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy