BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.