Compare DATETIMES from AS400 to SQL via SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Compare DATETIMES from AS400 to SQL via SSIS

Author

Topic

paulkem
Starting Member

28 Posts

Posted - 2015-02-11 : 12:04:42

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?

paulkem
Starting Member

28 Posts

Posted - 2015-02-11 : 13:12:13

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.

PK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources