quote:

---

Originally posted by loudbliss

Visak, thanks but where do i put this in SSIS? Script Component, Ole DB Destination..?

---

quote:

---

Originally posted by visakh16

DELETE FROM YourTable WHERE MONTH(DateColumn)=@Month-1

Where @Month is the month parameter

---

quote:

---

Originally posted by loudbliss

tkizer, i could do the work with the Execute SQL Task, very simple, only had to write the statement:
"delete from mytable where month(datecolumn) <> month(getdate())"

Visakh im trying it now with the ? as a place holder but i really dont know how to make this work.

I've had trouble writing statements with parameters so i've been forced to use scripts to send custom parametres to a query.

Could you please explain how do I use the ? part and how do i send a non fixed value to a variable,that it, lets say i want to delete everything from last year but only the actual month, i will need so somehow calculate the value of last year and the value of this month and then send it to a variable.

I know it's kind of a noobie thing but for some of us it would be very helpful.

Thax

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by visakh16

DELETE FROM YourTable WHERE MONTH(DateColumn)=@Month-1

Where @Month is the month parameter

---

You should also use year value otherwise all years data for that month would be deleted

Madhivanan

Failing to plan is Planning to fail

---

DELETE FROM YourTable WHERE MONTH(DateColumn)=@Month-1 AND YEAR(DateColumn)=YEAR(GETDATE())

DELETE FROM YourTable 
WHERE DateColumn>=DATEADD(month,@month-2,dateadd(year,datediff(year,0,getdate()),0))and 
      DagteColumn<DATEADD(month,@month-1,dateadd(year,datediff(year,0,getdate()),0))

quote:

---

Originally posted by madhivanan

Another method though complicated but accurate

DELETE FROM YourTable 
WHERE DateColumn>=DATEADD(month,@month-2,dateadd(year,datediff(year,0,getdate()),0))and 
      DagteColumn<DATEADD(month,@month-1,dateadd(year,datediff(year,0,getdate()),0))

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by madhivanan

Another method though complicated but accurate

DELETE FROM YourTable 
WHERE DateColumn>=DATEADD(month,@month-2,dateadd(year,datediff(year,0,getdate()),0))and 
      DagteColumn<DATEADD(month,@month-1,dateadd(year,datediff(year,0,getdate()),0))

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by loudbliss

Guys,

Thanks for the help with the delete statements but what i'm more interested in knowing is the setting of parameters (how to set them and how to add them in a query) in SSIS.

Deleting a previus month can be done with those queries you guys sent w/o the need of parameters but for example i need to make an statement in ssis where i can delete all the data from the previous week (that is the last 7 days) For that i need to calculate what was is that day, determine to what month and year it belongs. Therefore i need to do that in the script component i think.

That's where i really need help. How to set a calculated value to a variable and how to put that in a statement.

Visakh mentioned this
"Create a variable in package to hold the value of month and use ? as place holder for parameter for query statement. Then map the parameter value to the created variable." but i really didnt how to do this specially the use of the ?

---

quote:

---

Originally posted by loudbliss

visakh i missed your links before.

http://technet.microsoft.com/en-us/library/ms141138.aspx
aswered my question.

Really appreciate your help

---