January 24, 2011 at 9:50 am
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
January 25, 2011 at 12:11 pm
well you can include the columns as they are from the source and use a data conversion transformation down the road ......
regards
greg
January 25, 2011 at 12:20 pm
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.
January 25, 2011 at 12:23 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 25, 2011 at 12:28 pm
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;
January 26, 2011 at 2:21 am
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
January 26, 2011 at 4:30 am
What is your OLEDB data source?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 26, 2011 at 5:08 am
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
January 26, 2011 at 5:28 am
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