SQL Task To Excel ssis - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SQL Task To Excel ssis

Author

Topic

wbach3
Starting Member

3 Posts

Posted - 2012-03-04 : 15:48:31

All I would like to run this code and return the results in excel using ssis. The only problem is I can only use temp tables. I am a wiz at dts however my new company only has ssis. I am a complete newb on it. Thank you.

select Playerid,firstname+' '+lastname as Host
into #a
from AbsUser a
join playerhost b on a.userid = b.hostid
where UserID in (31231,
31238,31821,31806,31855,31828,31216,31893,31938,31802,31217,31218,31211,31818,31829,31826,31661,31945,31892,31862,31215,
31820,31232,31237,31939,31213,31227,31228,31823,31942,31112,31659,31955,31655,30858,31663,31219,31822,31827,31150,
31859,31776,31236,31235,31861,31860,31664,31662,31656,31863,31214,31658)

--Max Play

select playerid, Max(AccountingDate)LastTrip
into #six
from PlayerDay
where ((slot_coinin >0) or (table_theowin <> 0) or (slot_jackpot <> 0))
and PlayerID in (select PlayerID from #a)
group by playerid

select PlayerId,min(playernameid)playernameid
into #name1
from PlayerName
where PlayerID in (select PlayerID from #a)
group by playerid

select a.PlayerId, Firstname,Lastname
into #name2
from #name1 a
join PlayerName b on a.playerid = b.PlayerID and a.playernameid = b.PlayerNameID

select a.playerid,Firstname,Lastname,Host,LastTrip as LastTripDate, MonthInactive = datediff(mm,getdate(),LastTrip)*-1
into ##stage
from #six a
join #name2 b on a.playerid = B.playerid
join #a c on a.playerid = c.playerid

select * from ##stage
where lasttripdate <= GETDATE()-120
order by host,monthinactive desc

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2012-03-05 : 14:22:46

Use Common Table Expressions. I imagine there could be more cleanup involved..but something like this would work as SQL Select for an SSIS dataflow.



;WITH  ctePlayerIDHost as
  ( SELECT Playerid,firstname+' '+lastname as Host
	FROM AbsUser a
		join playerhost b on a.userid = b.hostid
	where UserID in (31231,
	31238,31821,31806,31855,31828,31216,31893,31938,31802,31217,31218,31211,31818,31829,31826,31661,31945,31892,31862,31215,
	31820,31232,31237,31939,31213,31227,31228,31823,31942,31112,31659,31955,31655,30858,31663,31219,31822,31827,31150,
	31859,31776,31236,31235,31861,31860,31664,31662,31656,31863,31214,31658)
)


,  cteLastTrip AS
	( SELECT playerid, Max(AccountingDate)LastTrip
	  FROM PlayerDay join ctePlayerID on PlayerDay.PlayerID = ctePlayerID
	  WHERE  ((slot_coinin >0) or (table_theowin <> 0) or (slot_jackpot <> 0)) 
	  group by playerid
	)

,  ctePlayerName1 AS
	(
		SELECT PlayerId,min(playernameid)playernameid
		FROM PlayerName join ctePlayerID on PlayerName.PlayerID = ctePlayerID.PlayerID 
	 	GROUP BY playerid
	)

, ctePlayerName2 AS 
	(
		SELECT a.PlayerId, Firstname,Lastname
	 	FROM ctePlayerName1 a join PlayerName b 
			ON  a.playerid = b.PlayerID and a.playernameid = b.PlayerNameID
	)

, cteStage AS
	(
		SELECT a.playerid,Firstname,Lastname,Host,LastTrip as LastTripDate, MonthInactive = datediff(mm,getdate(),LastTrip)*-1 
	FROM cteLastTrip a
	join ctePlayerName2 b on a.playerid = B.playerid
	join ctePlayerIDHost c on a.playerid = c.playerid
	)
SELECT a.playerid,Firstname,Lastname,Host,LastTripDate, MonthInactive 
FROM cteStage
WHERE LastTripDate <= GETDATE()-120
ORDER BY Host,MonthInactive

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources