Error in query in SSIS

  • Hello,

    I am trying to add the below query in the dataflow task(oledb source):

    select BL_ID, BL_NUM, CP_GROUP_CD, POL_COUNTRY_CD, POL_LOCATION_CD, POD_LOCATION_CD, POD_COUNTRY_CD, SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD,

    SAISAN_LEG_CD,SAISAN_MONTH, TEU from mini_nctv..NCV_BL where SAISAN_MONTH >='201001'

    AND SAISAN_MONTH < right('00'+ Convert(varchar(4),YEAR(getdate())),4) + right('00'+ Convert(varchar(4),MONTH(getdate())),2)

    And CP_GROUP_CD ='DHL_DANZAS'

    However, I am getting this error- An OLE DB record is available. Description: "ORA-00936; missing expression".

    I think this is due to this statement - right('00'+ Convert(varchar(4),YEAR(getdate())),4) + right('00'+ Convert(varchar(4),MONTH(getdate())),2)

    is there any other way to write this query so that it is accepted in SSIS. by the way this query works perfectly in sql server management studio..

    Many thanks,

    Paul

  • well you can include the columns as they are from the source and use a data conversion transformation down the road ......

    regards

    greg

  • I always try to keep my queries as SPs on the database itself, and then reference the SP from the package. Makes maintenance/troubleshooting much easier.

    That would also fix this issue since the date comparison would be running on the SQL server, assuming the syntax is correct.

  • Derrick Smith (1/25/2011)


    I always try to keep my queries as SPs on the database itself, and then reference the SP from the package. Makes maintenance/troubleshooting much easier.

    That would also fix this issue since the date comparison would be running on the SQL server, assuming the syntax is correct.

    ..except that looks like an Oracle error message ...

    and, regardless of that, the date comparison would be running on the server whether via SP or source SQL.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin (1/25/2011)


    Derrick Smith (1/25/2011)


    I always try to keep my queries as SPs on the database itself, and then reference the SP from the package. Makes maintenance/troubleshooting much easier.

    That would also fix this issue since the date comparison would be running on the SQL server, assuming the syntax is correct.

    ..except that looks like an Oracle error message ...

    .....aaaand I missed that part completely.

    If that's the case, this is an oracle question, not an SSIS question. Syntax is entirely different for this kind of thing in Oracle.

    In that case, you need something like this to return YYYYMM:

    TO_CHAR(SYSDATE, 'YYYYMM') FROM DUAL;

  • Derrick Smith (1/25/2011)


    Phil Parkin (1/25/2011)


    Derrick Smith (1/25/2011)


    I always try to keep my queries as SPs on the database itself, and then reference the SP from the package. Makes maintenance/troubleshooting much easier.

    That would also fix this issue since the date comparison would be running on the SQL server, assuming the syntax is correct.

    ..except that looks like an Oracle error message ...

    .....aaaand I missed that part completely.

    If that's the case, this is an oracle question, not an SSIS question. Syntax is entirely different for this kind of thing in Oracle.

    In that case, you need something like this to return YYYYMM:

    TO_CHAR(SYSDATE, 'YYYYMM') FROM DUAL;

    Hi,

    I am working on SSIS and this question is related to that only. I also tried using this statement but it still doesnt work:

    RIGHT("0" + (DT_WSTR, 2)YEAR(getdate()), 4) + RIGHT("0" + (DT_WSTR, 2)MONTH(getdate()), 2)

    This is the query in which I need to fit in the above statement:

    select BL_ID, BL_NUM, CP_GROUP_CD, POL_COUNTRY_CD, POL_LOCATION_CD, POD_LOCATION_CD, POD_COUNTRY_CD, SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD, SAISAN_LEG_CD,SAISAN_MONTH, TEU from NCTVDB.NCV_BL where SAISAN_MONTH >='201001' AND SAISAN_MONTH < RIGHT("0" + (DT_WSTR, 2)YEAR(getdate()), 4) + RIGHT("0" + (DT_WSTR, 2)MONTH(getdate()), 2) And CP_GROUP_CD ='DHL_DANZAS'

    Could anyone please tell me what needs to be changed in order to make this work ? This is not currently working and returns an error:

    "ORA-00907: missing right parenthesis"

    Kind Regards,

    Paul

  • What is your OLEDB data source?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • pwalter83 (1/26/2011)


    I am working on SSIS and this question is related to that only.

    It is not because you are working with SSIS that you only have SSIS question. SSIS is an enterprise ETL tool. It works with various data sources. So problems may rise at those source systems, which could be Excel, flat file, DB2, SQL Server, Oracle...

    pwalter83 (1/26/2011)


    I also tried using this statement but it still doesnt work:

    RIGHT("0" + (DT_WSTR, 2)YEAR(getdate()), 4) + RIGHT("0" + (DT_WSTR, 2)MONTH(getdate()), 2)

    This is an expression written in the Derived Column expression language. You cannot mix this with regular SQL.

    pwalter83 (1/26/2011)


    Could anyone please tell me what needs to be changed in order to make this work ? This is not currently working and returns an error:

    "ORA-00907: missing right parenthesis"

    It seems like an Oracle issue. So, to ask it again (as Phil and others did): what is your source? Is it Oracle?

    The error seems quite clear: you are missing a right parenthesis.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • resolved it...its working now....Thanks for your help...

    just had to do this...

    to_char(sysdate, 'YYYYMM')

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply