INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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?

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

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

(OP)
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..

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

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

(OP)
Your knowledge and experience helped me a lot.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close