Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

manti1986

IS-IT--Management
Jun 25, 2014
3
PL
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::DNI_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::DNI_WSTECZ],GETDATE()))+"'"




can you tell me how can I change my sql query, to see reult?
 
 http://files.engineering.com/getfile.aspx?folder=bfd1bd5f-af5b-44f0-bb35-b51f348574e0&file=I_have_a_problem_with_my_Expression_sql_on_SSIS.docx
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::DNI_WSTECZ] + ",GETDATE())"

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


Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
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::DNI_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::DNI_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::DNI_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..
 
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::DNI_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::DNI_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::DNI_WSTECZ] + ")"


Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Your knowledge and experience helped me a lot.

It's working, thank you so much Frederico Fonseca! :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top