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.
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)
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..
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
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
17 People are following this question.
