Creating ssis package to copy data to a table from query that uses temporary tables

Hi all, I have a really long bit of query that uses temporary tables and others to generate a list of rows.
My problem is I want to store the final values returned by the query in a physical table for reporting purposes to be quicker. This process would be repeated on a daily basis.
I am quite new at SSIS so any help would be appreciated.
Thank you .
select mainnumber,revenue AllRevenue
into #PlusOtherRevenue
from nrmevent with (nolock, index(IX_EventStartDate))
where EventStartDate = 20100413



select tigomainnumber,sum(AllRevenue) AllRev
into #REV
from #PlusOtherRevenue
group by mainnumber



SELECT Adate, right(PARTYNUMBER,9) isdn,
DURATION EventMinutes, NORMALFEE Revenue
into #tmpSubscenterDetail
FROM RECj1 with (nolock, index(IX_adate))
inner join dimCodes j2 on j1.otherPARTYNUMBER = j2.ISDN
where ADATE =  convert(varchar,getdate()-1,112)/*Yesterda*/


SELECT Adate, isdn, COUNT(*) CallCounts, 
SUM(EventMinutes/60.0) EventMinutes, SUM(Revenue/10000.0)Revenue
into #tmpSubscenter
FROM #tmpSubscenterDetail
group by adate, isdn



select Adate, ISDN, #tmpSubsCAllcenter.Revenue,Callcount,Eventminutes,Classification 
into #tmp 
from #tmpSubsCAllcenter 
inner join CubeData..Subscribers
on isdn = MainNumber where EventStartMonth = 201002 /*convert(varchar(6),GETDATE(),112)-1*/
group by Adate, ISDN,Classification,#tmpSubscenter.Revenue,Eventminutes,callcount
order by adate


select Adate, ISDN, Revenue,Callcount,Eventminutes,Classification,AllRev,allcalls
into #tmpAll
from #tmp 
inner join #rev on #rev.mainnumber = isdn

/* want to store this in a physical table*/
select Adate, ISDN, Revenue,Callcount,Eventminutes,Classification,AllRev
from #tmpAll order by allrev desc

Open in new window

MenoforiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
Is this in a stored Procedure?
If so you can add something like this to the bottom....
GO
if exists (select * from sysobjects where id = object_id('dbo.tmpAll") and sysstat & 0xf = 4) drop procedure dbo.tmpAll
GO
CREATE PROCEDURE dbo.tmpAll
0
Reza RadConsultant, TrainerCommented:
put the whole script above in a stored procedure, let's name it as SP_FetchData
now create a ssis package
add a data flow task
double click on this
in data flow tab, add oledb data source,
select source server, and write exec SP_FetchData as sql command.
then add OLEDB destination
select server, and the table you want to put data in .
that's all, after deploy the package you can schedule it.

Note that all of these steps can be done by Import/Export Wizard simpler.
at last steps you should save the package , and then schedule it in a sql server job.

let me know if you have problem on these methods
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.