CODEWITHSUNDEEP
sqlssis
Biswa
Biswa

Reputation: 331

SSIS Package to archive data on monthly basis

Can any one let me know how can i create a ssis package which will serve the below mentioned criteria.

1) Extract new member joiner data on monthly basis .

2) Store the data on a separate table call 'Joiner' which has column member_name,join_date,member_class.

3) the job will be scheduled to run 1st day of every month.

For example the package will run on 1st April with a join_date 1st March to 31st Match and dump it to the 'joiner' table. Next month it will run on 1st May with a join_date of 1st April to 30th April.

I know i have to create a store procedure with a join date parameter to pass but my concern is how should i achieve this automation of passing date every month and archive joiners data on month and month basis.

Any help will be much appreciated.

Upvotes: 0

Views: 723

Answers (1)

dfundako
dfundako

Reputation: 8324

It sounds like your question is about how to get the previous month of data on an automated basis. DATEADD() and EOMONTH() can accomplish your need of grabbing a rolling timeframe each month automatically if you are using SQL Server.

SELECT 
getdate(), --current date
EOMONTH(GETDATE()), --last day of the current month
EOMONTH(GETDATE(),-1), --last day of the previous month
DATEADD(DAY, 1, EOMONTH(GETDATE(),-2)) --go back two months and add 1 day

Your query would need to include something like this in the WHERE clause.

WHERE join_date >= DATEADD(DAY, 1, EOMONTH(GETDATE(),-2)) 
AND join_date < EOMONTH(GETDATE(),-1)

Upvotes: 2

Related Questions