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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Compare DATETIMES from AS400 to SQL via SSIS
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paulkem
Starting Member

28 Posts

Posted - 02/11/2015 :  12:04:42  Show Profile  Reply with Quote
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 - 02/11/2015 :  13:12:13  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000