October 10, 2011 at 6:05 am
Hi,
i have to perform following task by using SSIS package
please help me to automate following using SSIS
TABLE STRUCTURE
MonthEndCDSSpreadCalc:
Date CompanyName PurchaseLotID DatePurchased EntryPrice PreviousMark Spread Mark
NULL Federal Germany 929 2011-08-25 0.97 0.973 NULL NULL
NULL Oil Corp 767 2011-04-15 1.083 0.998 NULL NULL
NULL French Republic 566 2010-12-21 0.959 0.94 NULL NULL
Reporting_DailyNAV
Date CompanyName PurchaseLotId DatePurchased EntryPrice PreviousMark
2011-08-31 CDX IG16 899 2011-08-08 0.995624 0.995624
2011-08-31 CDX.NA.HY.16 912 2011-08-11 0.925000 0.940104
2011-08-31 CDX.NA.IG 15 498 2010-11-23 1.002759 0.997650
step 1:
Insert into MonthEndCDSSpreadCalc(Date,CompanyName, PurchaseLotId, DatePurchased, EntryPrice,
PreviousMark)
SELECT Date, CompanyName, PurchaseLotId, DatePurchased, EntryPrice, PreviousMark
FROM Reporting_DailyNAV
WHERE (Date = '8/31/2011') AND (PortfolioId = 5) AND (SecurityType in ('CDS'))
ORDER BY CompanyName
--MonthEndCDSSpreadCalc is temp table i'm taking values from Reporting_DailyNAV table because i want to do some operations and need to calculate Mark
--For step1 i have taken execute sql task. any other suggestions?
step 2:
--to edit Spread values
Select * MonthEndCDSSpreadCalc where Date = '8/31/2011' -- in edit mode in SQL server
--in this step2 i need to insert values of spread which is in excel file so i have taken one excel source and inserting
spread value into MonthEndCDSSpreadCalc but what about step 1 then?
i mean how can i do this in one dataflow task?
step 3:
--update mark
update MonthEndCDSSpreadCalc
set mark = CalculateCDSMark(purchaselotid, DATE, spread, 1) WHERE (Date = '8/31/2011')
--now here in step 3 i'm updating mark value which will call this CalculateCDSMark scalar function
basically this function takes spread values which i have inserted in step2 and calculate mark value
i wonder how can i do it in same package?
step 4:
--now i need to copy 'Mark' values into Reporting_DailyNAV from MonthEndCDSSpreadCalc
in this step i need to copy mark from 1st table MonthEndCDSSpreadCalc to Reporting_DailyNAV
step 5:
--make all null
update MonthEndCDSSpreadCalc
set spread4y = null, spread5y = null, mark = null
at last i need to make MonthEndCDSSpreadCalc this temtable all sets to null...
October 10, 2011 at 6:44 am
Okay, let's see if I understand this correctly:
1. Insert data into a worktable
2. Import data from a spreadsheet
3. Calculate data from spreadsheet
4. Update worktable
5. Insert/Update data in primary table from worktable
Is that correct? If so, everything except the Excel import should just be stored procedures or T-SQL scripts. In either case, Execute SQL Task objects should be used for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 10, 2011 at 7:15 am
how many Execute SQL Task are needed as i have mentioned steps can you plz let me know how i need to go..
i'm newbie please help
October 11, 2011 at 6:05 am
It looks like one before the import, and one after. The one after would have multiple steps in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply