DTS from firebird to sql

Hi,
I am having a problem, primarily because I don't know what I am doing, so please be gentle with me!

I have an application running on firebird, a table on which has a date field, but defined as an integer. I have another application running SQL, on another machine. I need to move data from the firebird database to the SQL database based on date being yesterday. In the DTS wizard the following statement transfers the data I need:

Select * from transack
where tr_date = 20060926

If I copy the whole table across to SQL, (all dates) the following SQL script returns only yesterday:

select * from transack
where tr_date = CONVERT(CHAR(8), CURRENT_TIMESTAMP -1 , 112)

The same script, however, in the DTS wizard returns an 'unspecified error'

Anybody able to schedule some light?

Regards,
Greg

Doesn't sound like a good idea, why don't you try to fix it?This statement is run in Firebird, isn't it?
This statement is run in MsSQL, isn't it?
That's because sql syntax is different between Firebird and MsSQL, you'd better find out proper syntax for Firebird, build a view in it and extract from that view, generally I wouldn't embed sql statements in DTS packages.Light can be scheduled like a cron job?
Hope this helps, feel free to ask for clarifications.

pabloj,
Thanks for the reply.
Yeah, not a great idea, but not my app, all I can do is get the data off, and use it for my purposes(which is then to re-transform it into something else, but thta's another story!)

Makes sense what you say about the different syntax. Strictly speaking the staement is running in SQL, but against the firebird data, so it doesn't understand the syntax.
I really need to do all this from the SQL machine, as I have control of it, but not the other one, so unless I am not quite understanding you, your suggestion is not really an option.
I can take the whole table across, but that involves moving about 3000000 line items, and I only need about 200 of them at any given time.

Meant to say 'shed', been a long day.

Regards,
Greg

No, it's sent to Firebird and executed there, you'll have to use firebird's syntax.