ssis expression - 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 expression

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-17 : 23:28:48

I wrote a query in the Expression window but I get an error msg when I try to preview it with the OLEDB Source.

ORA-12801: error signaled in parallel query server P001
ORA-01861: literal does not match format string (OraOLEDB)

-- Bad query, fails to preview.
"SELECt a.petky from dwods.pet a where
a.dw_load_date <= " + "'"

+

(DT_STR, 4, 1252) DATEPART("yyyy", getdate())

+
(DT_STR, 4, 1252) DATEPART("mm", getdate())

+
(DT_STR, 4, 1252) DATEPART("dd", getdate())

+"'" + " and
rownum < 5"

-- Good query.
"SELECt a.petky from dwods.pet a where rownum < 5"

This bad query works with different table called dwoods.cus
"SELECt a.cusky from dwods.cus a where
a.dw_load_date <= " + "'"

+

(DT_STR, 4, 1252) DATEPART("yyyy", getdate())

+
(DT_STR, 4, 1252) DATEPART("mm", getdate())

+
(DT_STR, 4, 1252) DATEPART("dd", getdate())

+"'" + " and
rownum < 5"

Can anyone provide some input how to fix it?
Looks like a.dw_load_date is the problem. But I'm not sure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 23:49:00

whats the datatype of a.dw_load_date?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 00:04:21

i think it should be something like this

"SELECt a.petky from dwods.pet a where
a.dw_load_date <= " +(DB_WSTR,20)(DT_DBDATE)GETDATE() + " and
rownum < 5"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 00:04:41

also see this

http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-18 : 01:32:04

a.dw_load_date should be DATE data type. But I will comfirm tomorrow.
I used the same expression and I pressed the button "evaluate Expression" and it works for the 2 tables. Otherwise it won't let me click the OK button if the expression can't be evaluated. It throws the error msg when I want to preview the records on the OLEDB Source for the table "pet" only.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-18 : 01:36:39

quote:
Originally posted by visakh16

i think it should be something like this

"SELECt a.petky from dwods.pet a where
a.dw_load_date <= " +(DB_WSTR,20)(DT_DBDATE)GETDATE() + " and
rownum < 5"

(DB_WSTR,20)(DT_DBDATE)GETDATE() throws an error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 02:19:51

whats the error?

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-18 : 13:15:42

(DB_WSTR,20)(DT_DBDATE)GETDATE() throws an error.
expressiong cannot be evaluated. You can copy and paste to your expression window and see the error in more details

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2008-12-18 : 19:00:47

I solved the problem. Oracle DBA helped me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources