SSIS with Where Clause - SQL Server Forums

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 2005 Forums

SSIS and Import/Export (2005)

SSIS with Where Clause

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-03-26 : 13:40:46

I am an SSIS newbie and was hoping someone could help me with the following issue.

I created an SSIS package which simply reads an IBM DB2 table as its source and writes to a SQL Server table. I currently write all rows found in the DB2 table. I would like to isolate certain records based on todays date. Basically, when the SSIS package runs, it says get me only the DB2 records with a TRANSACTION_CREATE_TS >= getDate().

Within SSIS, I modified the Data Access Mode so that it now uses a SQL Command. I then put the following query for the SQL commant text:

SELECT *
FROM DBA002.TRANSACTION_BILLING
WHERE (TRANSACTION_CREATE_TS >= getdate())

When I attempt to preview, I get the following:
"Unable to retrieve column information from the data source......"

If I remove the where clause, I get all the date from DB2 returned.

Any ideas?

TRANSACTION_CREATE_TS is stored in "3/10/2010 9:37:59 AM" format in case it helps.

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-26 : 13:54:05

getdate() is not valid DB2 syntax

see http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-03-26 : 14:25:36

I read the linked page and still am unable to get thie query to work using. Can someone help me with the query?

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-03-26 : 14:50:59

OK, so this worked:

SELECT *
FROM DBA002.TRANSACTION_BILLING
WHERE DATE(TRANSACTION_CREATE_TS) = CURRENT DATE

Now, how can I get current date minus one day in the where clause?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 04:53:47

try


SELECT *
FROM DBA002.TRANSACTION_BILLING
WHERE DATE(TRANSACTION_CREATE_TS) = CURRENT DATE-1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources