question

innap9999 avatar image
innap9999 asked

Oracle to SQL

Hello. I am new in Oracle. I need data copy from Oracle DB to SQL server. When I am using WHERE statement DRAW_DATE BETWEEN TO_DATE('08/05/2013','mm/dd/yyyy') AND TO_DATE('09/29/2013','mm/dd/yyyy') I have no problem, but I need to run SSIS package each week and date will change each time. What should I use instead? Please help. Thanks.
sqloracledate
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlaj 1 avatar image
sqlaj 1 answered
How about creating a varible at the top of the t-sql code for the SSIS package the changes the date accoringly? Something like. > DECLARE @startdate DateTime DECLARE > @enddate DateTime > > SET @startdate = getdate() SET > @enddate = getdate() Then determine the intervals. Every day, every week...and change the variables in the WHERE clause > DRAW_DATE BETWEEN TO_DATE(@startdate) AND TO_DATE(@enddate)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you, I will do it. You Helped me a lot.
0 Likes 0 ·
innap9999 avatar image
innap9999 answered
Sorry, but I cannot DECLARE and SET variables in that t-sql - it give me errors. I tried to create variables in SSIS, but I don't know how to do so..
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlaj 1 avatar image
sqlaj 1 answered
I don't work with SSIS that much so I am not sure about how to use variables. I do know there are script tasks and I think that you can execute T-SQL code but again, not really sure how to set it up. I am not sure if this will help but you may want to look through it for guidance. http://technet.microsoft.com/en-us/library/ms135941.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvroman avatar image
dvroman answered

In SSIS there are system and user variables. These are different than script variables. See [http://technet.microsoft.com/en-us/library/ms141085.aspx][1] to get you started. Yes user variables can do exactly what you want. You can find a good beginners tutorial on SSIS and variables here: https://www.sqlservercentral.com/articles/batch-etl-of-multiple-data-files-using-an-ssis-foreach-loop-container-1 It also goes through other things, but what you're looking for is there.

[1]: http://technet.microsoft.com/en-us/library/ms141085.aspx

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.