I have a problem with my Expression on SSIS - i use parameter and i would like use function GETDATE 1

[manti1986]

I have a problem with my Expression sql on SSIS - i use parameter and i would like use function GETDATE but i have a error... all printscr on attached


If i used sql like below i have a result

"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE>'" +(DT_STR, 20, 1252)(DATEADD( "DD", -@[User:NI_WSTECZ],@[System::StartTime]))+"'"


But if i change @[System::StartTime] to GETDATE() I have a error

"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE>'" +(DT_STR, 20, 1252)(DATEADD( "DD", -@[User:NI_WSTECZ],GETDATE()))+"'"




can you tell me how can I change my sql query, to see reult?

 

[fredericofonseca]

you have the following options
1 - change the size of the resulting string to be the correct size for a getdate() function e.g. 29 - this should have been clear from the error message you got

2 - as you are using getdate just pass the variable to the sql and do the date add within the sql itself instead of outside
"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE> DATEADD( DD, -" + (DT_STR, 20, 1252)@[User:NI_WSTECZ] + ",GETDATE())"

3 - again do the dateadd bit within the sql and pass @[User:NI_WSTECZ] as a parameter to the sql


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[manti1986]

1-
If i changeg from 20 to 29

"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE>'" +(DT_STR, 29, 1252)(DATEADD( "DD", -@[User:NI_WSTECZ],GETDATE()))+"'"

view it's ok:

select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE>'2017-10-02 11:40:11.280000000'

but when i open olde db source editor i see error ..

ORA-01830: date format picture ends before converting entire input string

2-
if i used your sql

"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE> DATEADD( DD, -" + (DT_STR, 20, 1252)@[User:NI_WSTECZ] + ",GETDATE())"

view:

select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE> DATEADD( DD, -3,GETDATE())

an i have a error ..Error at Load records to ORDER fact table [Select order fact table 1 [70]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-00904: "DD": niepoprawny identyfikator".

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)


but if i used this sql i thought that should be ok.. but also error

"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE> DATEADD( \"DD\", -" + (DT_STR, 20, 1252)@[User:NI_WSTECZ] + ",GETDATE())"

view:
select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE> DATEADD( "DD", -3,GETDATE())

SO IT SHOULD BE OK, but still i have a error


i don't know how i can do now..

 

[fredericofonseca]

ahhh.. using Oracle is slightly different.

You will need to play around with this - depending on the values of kssales.salesorderhead.NEW_DATE and whether they have time part of it you may wish to truncate the date you are comparing to.


This gives you the results using a calculated date within SSIS
"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE > to_timestamp('" +(DT_STR, 29, 1252)(DATEADD( "DD", -@[User:NI_WSTECZ],GETDATE()))+"', 'YYYY-MM-DD HH24:MI:SS.FF9')"

This gives you a Oracle (SYSDATE) calculated date
"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE> sysdate -" + (DT_STR, 20, 1252)@[User:NI_WSTECZ]

This gives you a Oracle (SYSDATE) calculated date truncated to have only the date part
"select kssales.salesorderhead.NEW_DATE,ordernumber from kssales.salesorderhead inner join kssales.salesorderposition on kssales.salesorderhead.SALESORDERHEAD_ID = kssales.salesorderposition.SALESORDERHEAD_ID inner join KSSALES.VKCUSTOMER on kssales.salesorderhead.VKCUSTOMER_ID = KSSALES.VKCUSTOMER.VKCUSTOMER_ID AND kssales.salesorderhead.NEW_DATE> trunc(sysdate -" + (DT_STR, 20, 1252)@[User:NI_WSTECZ] + ")"


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[manti1986]

Your knowledge and experience helped me a lot.

It's working, thank you so much Frederico Fonseca!