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?
 
Reza RadConnect With a Mentor Consultant, 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.